How Do I Pull Data in Microsoft Access?

"How do I pull data in Microsoft Access?" sounds like a simple question, but it can mean several completely different things. You might want to display a customer's name from another table, copy an address into an order, import an Excel spreadsheet, retrieve one phone number, or export records to another program. Access has tools for all of those jobs, but they are not the same tool.

The key is to stop thinking in terms of "pulling data" for a moment and describe exactly what needs to happen. Where is the data now? Where does it need to go? Are you only displaying it, or do you need to save a permanent copy? Once you answer those questions, the right Access technique is usually pretty obvious.

The most common situation is displaying information from a related table. For example, suppose you have a Customer table and an Order table. Each order stores a CustomerID, but when you look at an order, you also want to see the customer's name, address, phone number, and email address.

The beginner instinct is often to copy all of the customer information into every order record. Usually, do not do that. Store the customer information once in the Customer table, store the order information in the Order table, and connect them using the CustomerID.

Then create a query that joins the two tables on CustomerID. The query can display fields from both tables together, even though the data remains stored separately. This is one of the main reasons relational databases exist in the first place.

For example, an order query might show the OrderDate, OrderTotal, CustomerName, Phone, and Email. The order information comes from the Order table, while the customer details come from the Customer table. Nothing has to be copied just so you can see it on a form or report.

This is also why relationships matter. The Customer table is the parent table, and the Order table is the child table. One customer can have many orders, so the Order table stores the CustomerID as its foreign key. When you build the query, Access uses that matching ID to bring the records together.

Be careful with join types, too. A normal inner join only shows records where Access finds a match in both tables. That is usually fine for valid orders with valid customers. But in some situations, you may need an outer join so that Access still shows all records from one table even if a matching record is missing in the other. This can be useful for finding orphaned records, incomplete imports, or customers who have not placed any orders yet.

Sometimes, however, you really do need to copy data from one table into another. This is not automatically bad database design. The question is whether the copied value needs to remain historically accurate or be independently editable.

A shipping address is the classic example. A customer places an order today, and you ship it to their current address. Six months later, the customer moves and you update their address in the Customer table. If your old order simply displays the customer's current address, it will now look like you shipped that package to the new address. That is historically wrong.

In that case, copying the shipping address into the order record is exactly the right thing to do. The order needs its own snapshot of the shipping address as it existed when the order was placed. The same idea applies to product prices, tax rates, commissions, discounts, shipping methods, and other transaction-specific values.

This kind of duplication is intentional. It is not the same as blindly storing the same customer phone number in fifty different places because someone did not want to learn how joins work. One is a historical snapshot. The other is a future maintenance nightmare.

You can copy values with an append query, update query, macro, or VBA code. Which method is best depends on when the copy needs to happen. If the values should be copied automatically when a new order is created, a form event or VBA routine is often appropriate. If you are fixing or updating many existing records at once, an action query may be the better choice.

Another common meaning of "pull data" is bringing information in from outside Access. Maybe you have an Excel spreadsheet, a CSV file from a bank, a text file from a vendor, or data stored in another Access database. In that case, you are usually talking about importing or linking data.

Importing creates a copy of the outside data inside your Access database. Linking leaves the data in its original location but lets Access work with it as though it were a table. Both approaches have their place.

If you receive a spreadsheet every month from someone who believes column headings are merely suggestions, do not import it directly into your production tables. Import it into a staging table first. A staging table gives you a safe place to clean up bad values, remove duplicates, validate dates, translate text values, and make sure the imported data matches your database structure before it reaches your real tables.

For example, one spreadsheet might say "Florida," another might say "FL," and a third might say "Fla." You do not want all of that mess going directly into a properly designed State table. Clean and standardize it in staging first. Future You will appreciate this. Future You may even send Present You a nice card.

Sometimes you only need one value, such as displaying a customer's phone number after selecting that customer in a combo box. In that situation, a domain aggregate function such as DLookup can be convenient. You select the customer, and Access looks up the corresponding phone number from the Customer table.

DLookup is fine for a simple lookup on a single form. It is not something you want to repeat hundreds of times on a large continuous form when a query join would do the job more efficiently. If you are displaying a whole set of records, use a query. If you need one quick value in one place, DLookup can be perfectly reasonable.

You may not even need DLookup if the value is already available. A combo box can contain hidden columns, so you can select a customer by name while also retrieving their phone number, email address, or other fields from the combo's existing row data. Likewise, a subform can reference a value on its parent form, and one open form can reference a control on another open form.

Another meaning of "pull data" is simply retrieving a group of matching records. "Pull all unpaid invoices," "pull orders from last month," or "pull every customer in Florida" are usually requests for a select query.

A select query does not copy or move anything. It reads the stored records, applies criteria, sorts the results, performs calculations if needed, and returns the matching records. That query can then become the record source for a form or report, or it can be opened in VBA as a recordset for further processing.

For instance, if you want all unpaid invoices from the previous month, build a query using the invoice table, add criteria for the unpaid status and date range, and let Access return the matching records. That is pulling data in the sense that most people mean it, but nothing is actually being duplicated.

The phrase gets even more confusing when Excel is involved. Someone may say, "I want to pull data from Access into Excel." From Excel's point of view, it is pulling data. From Access's point of view, you are exporting data.

That distinction matters when asking for help. If you are working in Access and want query results sent to Excel, say that you want to export to Excel. Access can export query results to Excel, create CSV or text files, generate PDFs from reports, export to Word, or send information to other systems.

Data can also be pulled from websites, APIs, SQL Server, and other remote sources. That is possible, but it is a different level of project. A web API may require HTTP requests, authentication, JSON or XML processing, and VBA. Web scraping may require browser automation and can break the next time the website changes its layout because somebody moved a button three pixels to the left.

If a website provides an API, that is usually the cleaner and more reliable choice. APIs are designed to provide structured data to other programs. Scraping visible web pages should generally be the fallback option, not the first choice.

There is also synchronization, which is more than simply importing or exporting. Synchronization means two sources need to exchange changes over time. Maybe Access checks an Excel file nightly, downloads records from SQL Server, or merges records from two offices.

Synchronization gets complicated fast when both sides can change the same record. You need unique identifiers, a way to identify new and changed records, timestamps or version numbers, conflict rules, and a plan for deletions. That is a real database project, not just a query with a fancy hat.

Finally, there is a useful middle ground between displaying live data and preserving historical data: copying a default value into a new transaction. A customer may normally receive a 10 percent discount, but a particular order may need 15 percent. A product may have a standard price, but a salesperson may override it for one sale.

In those cases, Access can retrieve the customer's usual discount or the product's current price and copy it into the new order. That copied value becomes part of the transaction and can be changed without affecting the original customer or product record. This is very common in order-entry databases.

So before asking, "How do I pull data in Access?" try to define the job more clearly. Is the data in another table, an Excel file, a website, or another database? Do you want it on a form, in a report, in another table, or exported to a file? Do you need one value or thousands of records? Should the result be live, or should it be saved permanently?

If you just need to display related information, use a query with a join. If you need a historical snapshot or an independently editable transaction value, copy it intentionally. If the data comes from outside Access, import or link it, preferably through a staging table. Once you know what "pull" actually means for your situation, the solution becomes much less mysterious.

Watch the embedded video for a fuller discussion and demonstrations of the different ways Access can retrieve, display, copy, import, and export data.

Live long and prosper,
RR