Quick Queries #101
By Richard Rost
13 hours ago
What Do You Mean By "Pull Data" in Microsoft Access?
In this lesson, we discuss the many meanings of "pull data" in Microsoft Access, including displaying related records with queries, intentionally copying historical or default values, importing or linking external data, retrieving single values with DLookup, exporting data, and synchronizing sources. We also cover foreign keys, table relationships, placeholder dates, SQL Server views, and using a Windows machine or SQL Server when Access data must be accessed from a web application.
Jerome from Philadelphia, Pennsylvania (a Gold Member) asks: I'm working on a Microsoft Access database, and I need to pull data from somewhere else into the part of the database I'm working on. I've searched online, but I keep finding completely different answers. Some people say to use a query, some say to copy the data, and others say I need code. What is the best way to pull data in Access?
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp QQ Quick Queries, pull data from another table, display related table data, join tables in query, Access relational query, copy values between tables, historical snapshot data, import Excel to Access, linked tables, staging table, DLookup, select query criteria, export Access data to Excel
Intro In this lesson, we discuss the many meanings of "pull data" in Microsoft Access, including displaying related records with queries, intentionally copying historical or default values, importing or linking external data, retrieving single values with DLookup, exporting data, and synchronizing sources. We also cover foreign keys, table relationships, placeholder dates, SQL Server views, and using a Windows machine or SQL Server when Access data must be accessed from a web application.Transcript When someone asks, "How do I pull data in Microsoft Access?" that sounds like a simple question. And I get this all the time. The problem is, pull data could mean about a dozen completely different things.
Welcome to another TechHelp Quick Queries video brought to you by Access Learning Zone. I'm your instructor, Richard Rost.
Today, we're going to look at what people might actually mean when they say they want to pull data. Are they displaying related information, copying values, importing from Excel, reading a website, or doing something else entirely?
We'll answer this question, plus many more from YouTube comments, my website forums, emails, and lots more.
All right, let's jump in.
Today's question comes from Jerome in Philadelphia, Pennsylvania, one of my gold members. Jerome says, "I'm working on a Microsoft Access database, and I need to pull data from somewhere else into the part of the database I'm working on. I'm searching online, but I keep finding completely different answers. Some people say to use a query, some say to copy the data, and others say I need code. What's the best way to pull data in Access?"
Well, the problem, Jerome, is that pull data is a very ambiguous phrase. What exactly do you mean by pull data? And I see this all the time. I get lots of emails about it, posts in the forums, all kinds of comments. So let's discuss what you mean by pull data.
The first thing to understand is that pull data isn't really a precise database term. It's like telling your mechanic, "My car's making a noise." Okay, what kind of noise? Where's it coming from? When does it happen? Give me more information.
In Access, you might mean display information from another table, copy values, import an Excel sheet, look up one value, export something else. They're all different jobs, and each one has a different tool.
Pull data is like walking into an office and asking someone to get the paperwork. "Get the papers, get the papers." What does that mean? Do you want the customer's file? Do you want matching order slips? Do you want me to photocopy this? Do you want me to bring in a stack of papers from an office down the hall?
Just like that, Access has separate tools for separate jobs.
Now, most beginners who ask how to pull data from another table simply want to display related information. So let's say you've got a customer table and an order table. The order table stores the customer ID, but you want to see the customer's name, address, phone number, and other information alongside each order.
Now, you don't normally copy all of the customer's information into every order record, but you can create a query, join the tables on customer ID, and display fields from both tables. The customer information stays in the customer table, and the order information stays in the order table. That's a properly normalized database. The query brings them together when you need them.
This is the basic relational database approach, and this is what I would teach first. Make sure you understand relationships, outer joins so you're not missing any records, and normalizing data. That's where you keep information from customers in your customer table, orders in your order table, and so on.
Sometimes, however, you really do want to copy data from one table to another. The classic example is the shipping address. A customer places an order, and you ship it to their current address. Six months later, they move, so you change the address in the customer table.
Now, if you only display the address from the customer table, that old order will now appear to have shipped to the new address, which is historically wrong. So when the order is created, you copy the current shipping address into the order record.
The same idea applies to the product information, the tax rate, the commission, all that stuff must be frozen in time, so you copy it into the order table, or the order details table.
Now, that's not bad duplication. That's an intentional historical snapshot. So you can use an append query, an update query, a macro, or some VBA to copy those values. I explain how to do that in this storing duplicate data video, when it's actually a good idea to store duplicate data.
Now, another common meaning for pulling data is importing data from somewhere outside the database. "I want to pull data in from Excel." Maybe you've got an Excel spreadsheet, a CSV file from your bank, a text file from another vendor, or tables in another Access database.
Now, Access gives you two broad choices. You can import the data, which makes a copy inside your database, or you can link to the data, which leaves it at the original location but lets Access work with it from there.
For real-world imports, I usually recommend bringing the data into a staging table first. That gives you a safe place to clean it up, validate it, find duplicates, translate text values and numbers, all that kind of stuff, and generally remove whatever nonsense people manage to put into their data.
Here's a video about importing from another Access database, and this one will show you how to import from Excel. And I've got lots more videos on my website about importing. So just go search for that on my website.
Now, sometimes people mean pulling data from a website. That can involve sending an HTTP request, connecting to an API, downloading a file, reading HTML from a webpage, or even using browser automation.
APIs are usually the cleanest choice, because the website is intentionally providing structured data for other programs to use. Web scraping means reading the visible webpage and extracting the information yourself. That works, but it can break whenever the website changes its layout.
Now, this is definitely possible with Access, but now we're getting beyond simple queries and into VBA, web services, authentication, XML, JSON, all that other developer stuff.
I do have some videos on this. Here's one about talking to an actual web API. That's where the website provides you with a link you can use to pull the data that you want. And I've got a video series on using the Edge browser control, which is a web browser you can stick in your database that lets you automate browsing the web and retrieving information that way, so check this out.
All right, now another meaning of pull data is, "I need to pull one value. I want to pull a customer's phone number." Okay, you want to select the customer and show their phone number. You want to pick a product and display its current price. That's more specific than just, "I want to pull their phone number."
Choose an employee and show their email address, that kind of stuff. For a single value, people often use DLookup.
You could also get the value from a hidden column in a combo box, use a small query, or reference a field already displayed on another open form. Which approach is best depends on where the value is needed and how often you're retrieving it.
DLookup is convenient, but you don't want 500 DLookups running repeatedly on a large form, like a continuous form, if a simple query join would work and do the job better. But for a single form where you want to pick a customer and then just display his phone number, if it's in a different table, that's fine. Use a DLookup.
What's DLookup? Well, go watch this video.
And if the value is already available on another open form, even like a parent form to a subform or a completely different open form, you can just reference it by its name.
People also use pull data to mean retrieving a set of records. Someone might say, "Pull all orders from last month," or, "Pull every customer in Florida with an unpaid balance." They're usually talking about a select query.
A query reads the stored data, applies criteria, sorts it, performs any calculations that you need, and returns a result set. Nothing is necessarily copied or moved. You're simply asking the database to show you the records that match your rules.
Then the same query can become a record source for a form or a report, or something you open as a recordset in VBA. If you want to learn more about select queries, go watch this. And you can apply query criteria to get just the records that you want.
And if you're curious what a recordset is, go watch this video.
Now, this is where pull data gets confusing, because it can mean the opposite of import data. Sometimes people say, "I want to pull data from Excel." Well, sometimes they say, "I want Excel to pull data from Access." So it's the opposite meaning.
So it's like how they made literally and figuratively the same thing. Don't get me started on that. I hate that.
Anyways, from Excel's point of view, it makes sense. But from the Access side, we call it exporting the data. If you're asking a question in the Access forums, you ask it as an Access developer. "I want to export the data to Excel."
You can send query results to Excel, create a CSV or a text file, export a report to a PDF, attach it to an email, or pass the data to another system.
So when someone says pull, always ask, "Who's doing the pulling?" Is it Excel pulling, or is it Access pulling? That's why I made this little tug-of-war thing.
Oh, by the way, I'm doing a split test between two different slides. Tell me if you like this one better or if you prefer this one. I'm split testing it. This is where YouTube shows them both. You might alternately get one or the other. And then I can decide.
That's how I decided on the cover photos. I was doing before with, "Do you guys like the slides better with people on them, or do you like screenshots better?" And there was 50/50, so I'm doing both. There's the other one if you can't decide.
Post your comment down below. Let me know which one you like better. Do you like the one with a small tug-of-war, or do you like the big tug-of-war better? I'm kind of split. I don't know.
I'm starting to see more YouTube title frames that are more picture than descriptive text, so I don't know what to do.
Anyways, back to the lesson. So when someone says pull, always ask, from whose point of view are we pulling? Is Access pulling the data in, or is another program pulling data out of Access? Same movement, just different button.
I did a search for export on my website, and there's 24 results. So I just give you the big ones. Here's exporting to Excel, exporting to a PDF, exporting to Word, exporting to text. There's a whole bunch more.
You can write a text file. I've got Excel automation videos to teach you how to actually create a custom spreadsheet using VBA. There's all kinds of ways you can export data from Access.
Oh, by the way, these are all free videos I'm recommending. They're on my website. They're on my YouTube channel. Go watch them if you've got questions.
Sometimes pull data means to synchronize two different sources. Maybe an Access database checks an Excel file every night. Maybe your office database downloads new records from SQL Server. Maybe two locations of their own data need to bring the changes together.
That sounds simple until both sides change the same record. Then you have to decide which version wins.
Good synchronization needs unique identifiers, a way to recognize new and changed records, timestamps, version information, rules for conflicts, and deletion. This can be done manually with queries or automatically with VBA scheduled tasks. That's a bigger job than simply displaying related fields.
I've got this video that talks about synchronizing with a remote database. Sorry, no thumbnail for it yet, but I'll put a link to it down below.
Another pull data variation is where you're not necessarily preserving history. You're simply copying a default starting value.
A customer might normally receive a 10% discount, but this order might get a 15%. A product has a standard price, but the salesperson can override it. A customer has a preferred shipping method, but this particular package needs to go overnight.
So in those cases, Access pulls the default from the related record and copies it into the new transaction. After that, the copied value belongs to the transaction, but it can be changed independently. This is very common for order entry systems.
The regular default value that you might set in the form gives you a starting point, but you can override it later.
A lot of times, people ask me, "I've got a default, let's say state. If you're a retail business based in Florida, you might default the state to Florida, but you want to be able to change it when you pick the customer." So it'll pull in the customer's state. Pull that data.
This video talks about default values, and that one I mentioned earlier about storing duplicate data will talk about overriding it. So when you pick the customer, it'll override whatever values you have to.
So instead of asking, "How do I pull data?" give us a little more information. Where's the data right now? Is it in another table, Excel, a website, SQL Server, another country, on the moon?
Where do you want it to go? A form, a report, another table, a file, the Enterprise, set the Alpha Five.
Do you only want to display it, or should it be copied permanently? Are you retrieving one value or hundreds of records? Should it happen when you click a button, when a field changes, when the database opens automatically every night?
Answer those questions, and the vague phrase pull data becomes a real database task that we can solve.
So coming back to the original student's actual question, after talking back and forth for a bit and getting a little more information, what they really want is to display information from another related table.
They've got customers in one table and records belonging to those customers in another table. They want to see the customer's name and other details alongside the related record.
That means we don't need to import anything, scrape a website, synchronize databases, or fire up the warp core. We just need a relational query with a join.
The two tables share a customer ID. We join them on that field, bring in the fields we want to display, and let the query show everything together without unnecessarily copying the customer data.
That's the version of pull data that most beginners are actually asking about, and so was this customer. No Klingon painsticks are needed to solve the problem.
So the big takeaway is, pull data can mean many different things. There isn't one universal answer.
If you need to display related information, use a query. If the value must remain historically accurate or be independently editable, copy it intentionally. If the data comes from outside Access, import it or link to it, preferably through a staging table.
Before choosing a tool, clearly identify where the data is coming from, where it's going, and whether you want a live display or a permanent copy. Once you know those things, Access gives you a pretty straightforward solution.
All right, let's see what else we've got in the grab bag today.
But before we do, make sure you hit that like and subscribe, and everybody who hits the like button will be entered into a contest to win a free trip to the planet Ferenginar, just as soon as they invent warp drive. All expenses paid.
Okay, okay.
First up, we got Ray White, one of our gold members and longtime students. He asked about one of those drag-and-drop website builders that claims it can connect directly to a Microsoft Access database.
And that led to a follow-up question: Can you actually store an Access database on a Mac and use a web front end to access it?
Well, that's a good question. There's a lot of confusion around this.
You can store an ACCDB file on a Mac drive, but that doesn't make the Mac a database server. Microsoft Access and the ACE database engine only run on Windows, so a Mac can't actually serve that database the way a Windows machine can.
If you're building a real web application, I recommend moving the data into SQL Server. Your website talks to the SQL Server, and then users can access it from Windows, Mac, Linux, phones, tablets, the TARDIS, whatever they want.
If you still want to use Access itself, keep the front end and back end on a Windows machine and just remote desktop into it from your Mac.
But I would avoid putting an Access back-end file on a Mac and having multiple users open it over the network. That's just asking for database corruption sooner rather than later.
Next up, we got Paul Jack, one of my beginner students. He's designing an aviation database. He's got countries, states, cities, airports, runways, and companies, and all the related tables.
His main questions were, "Do I have too many tables?" and, "How do I know where the foreign keys belong?"
And this was a pretty long thread. I'll include a link to it down below if you guys want to read the whole thing. There's a lot of it here. But, in a nutshell, first, Paul, I love seeing people plan their databases before they start typing in thousands of records. That's exactly what you should be doing.
Now, the biggest takeaway from this thread wasn't that you had too many tables. In fact, I thought the number of tables looked pretty reasonable. The important lesson was that the direction of the relationships was backwards.
And that's something that confuses almost every beginner. The foreign key belongs in the child table, not the parent table.
So, if you've got country, state, and city, country has a primary key. Now, country is not going to have a foreign key because it's not under anything else, unless you've got, like, planet over here.
So country's primary key becomes the foreign key in the state field. So you get the StateID. Each state has its own ID, and then it's got a CountryID so you know which country it belongs to. One-to-many. One country can have many states.
Same thing from state to city. The StateID becomes the foreign key in the city table. So each city knows what state it belongs to.
So now, you know which country each city belongs to with a double lookup. You just go backwards. So Buffalo belongs to New York, which belongs to the United States.
So that's how those foreign and primary keys work. But a lot of beginners get this confused.
I like Matt's explanation. We call the country the top table in the hierarchy. All tables are equal as database objects, but they're not all equal in relationships, in the relationship tree.
Like I said, unless you've got a planet above that, and then a galaxy above that, and then universe above that.
Another question came down to runways. You have runway one, runway two, runway three. My rule, I have a rule of threes. Any time you need more than three of something, that should be in its own table.
Like phone numbers. I mentioned this in a lot of videos. If you've got a customer with a couple of different phone numbers, they've got a home phone, a phone, a phone, and a cell phone. Okay, that's good. Three is enough usually for most people.
If someone needs six, then now you're starting to think about putting those phone numbers in a separate phone number table. Same thing with addresses. Same thing with runways.
Small airports with only three runways, okay, you're fine. Now you start talking about Atlanta. I don't know how many runways they have, but it's more than three. So that might need to be in its own table with a RunwayID.
And this whole thread is the perfect example of why I tell people, finish the beginner series, and at least my Expert 1 and 2 classes before you start to build your dream database. You'll save yourself a lot of redesign later.
Learn all the basic material. Expert 1 and 2 cover relationships in detail, things like relational combo boxes and that.
Don't start your company database now. Wait until you've got some stuff under your belt first.
But excellent first question, and thank you very much for posting it.
All right, heading over to the YouTubes, we got XXZXYQ1 talking about my date problems video. That was a video that talked about if you're having problems working with your dates, check to make sure your date's not actually stored in a text field.
Because as soon as you use Format on a date, like in a query, for example, now congratulations, you've just converted that date to a string. So you can't do things like format it in a different date format, add one to it, or find out what day of the week it is, or whatever, until you convert it back to a date.
Now, ZYXQLMOP says that instead of using null dates, he often stores a very old placeholder date so he doesn't have to deal with null values causing problems in calculations.
That's a very good, valid design decision, and I use it all the time myself.
Now, I tend to leave dates as null if the date is truly unknown because null has a specific meaning. It means we don't know.
And depending on the context, a placeholder date, like January 1st, 2000, for example, means we know the date and it's this, even if it's just being used as a marker.
Now, that said, if you know your application will never have legitimate dates before, say, the year 2000, then using a value like 1/1/2000 can simplify your code and a lot of your calculations. Just be consistent.
For example, when I'm importing orders, if there's an order date missing, I'll use 1/1/2000 as an early placeholder date because I started what I do now, my current company, my current database, in 2002. So it couldn't have an order date from 1/1/2000.
Just like if I'm calculating a future date, I'll use 1/1/2100 because I don't plan on being around to see that date. I mean, it'd be nice, but I'll be 100 in 2072. So if I live that long, I'll be happy.
You see what I'm saying? So you can use any old date you want or any new date you want. Just be careful that it matches the context of what you're doing.
For example, I wouldn't use a placeholder date for something like a date of birth, unless it's incredibly old. Like 1899 is probably valid enough now.
But you could just leave it null and just use the Nz function. Just wrap all your date values in Nz and say, okay, if you don't have a value, give it a zero, which will turn out to be that 1899 date or whatever value you want.
Next up, we got Brian. Brian's asking if you can create SQL views in Microsoft Access, and this is a great question. And the answer is yes, but the answer also depends on what you mean by it.
If you're talking about taking an SQL Server view and using it in Access, then yes, absolutely. I use them all the time. You create the view on the server and then link to it in Access, just like a linked table. And from Access's point of view, it behaves just like another linked table.
One little gotcha: if you change the view on the server by adding a field, let's say, you have to relink it in Access or refresh the field list.
So if you add a new field on the server, make sure you relink it. Otherwise, Access won't see that new field.
Now, if you're asking whether Access itself can create SQL Server views on the server, yes, it can. One way is with a pass-through query that sends a CREATE VIEW statement directly to SQL Server and executes it there.
It's a little more advanced, but once you do that, then the view is created, and then you can link to it from Access and use it that way.
So both are definitely possible. I'll be covering techniques that do this in my SQL Server for Access Users course. Level 1 is free. It's on my website. It's on YouTube. It's a couple hours long.
And I've got Level 2. I'm working on it right now. It should be ready any day now.
All right, before we wrap up today, I just wanted to say thank you to everyone who's been participating in the new video quizzes. The response has honestly been better than I expected.
Here's just a small sample of the responses up on the screen here. I love seeing people posting their scores, comparing notes, even getting into a little friendly competition. That's exactly what I was hoping would happen.
Now, I wrote about this in my Captain's Log a couple days ago. And because I did get a couple of comments from people saying, "Hey, when do we get back to the teaching classes? What are you doing with these quizzes a couple times a week?"
Well, the quizzes aren't replacing teaching. They are teaching. Educational research has shown that testing yourself, quizzing yourself, is one of the best ways to reinforce what you've already learned.
It gets your brain thinking about that material instead of just listening to me talk.
Plus, more importantly, quizzes only take me a few minutes to create, which frees me up to spend the rest of the day working on bigger projects that all of you have been asking for, like my SQL Server course, more developer lessons, new handbooks, that kind of stuff.
So I'll post a link to this article that I wrote in my Captain's Log that explains in a little more detail why I'm doing the video quizzes.
But like I said toward the end here, if I did a full TechHelp video every day, that is basically my whole day. And so I'm not having any time to get any other projects done or have any kind of a life.
So if somebody knows how to get to that cloning colony from Next Gen, I could use a trip to Mariposa right now.
All right, three more Richards would definitely solve my problems. But it would also create a few new ones too, like Michael Keaton said in Multiplicity, "No one sleeps with my wife but me."
But imagine if I did have some clones of myself and I could do the whole bridge work myself. That'd be pretty cool.
Although I don't think I would look that good in Counsellor Troi's dress. So, yeah.
All right, before we're done for today, make sure you stop by my website. I'm tired, folks. It's been a long week.
Stop by my website, check out what's new. Check out my Captain's Log. Yes, there's an article about penguins. Make sure you get your penguin facts straight. Talked about Strange New Worlds a little bit.
Here's that video quizzes article, and I did update the AI customer service bot on my website recently. So now it's a little smarter when you ask questions like, "What are my membership perks?" and stuff like that.
So check it out.
And while you're there, visit my merch store. Pick up a mouse pad or a T-shirt or a hat or whatever. It's pretty cool stuff.
Pick up a copy of my book on Amazon. Post your questions in the forums if you've got any questions for next week's Quick Queries, or post them in the YouTube comments, or wherever you want to post.
If you want a better chance to get an answer, though, post it in the forums on my website. I've got a fantastic group of moderators, and they answer questions a million times faster than I do.
And usually with YouTube comments, I only have time to do a couple here and there, but I try to read them all because I have to approve them, because I was getting a lot of people posting spam on my channel. So I turned on approvals.
But I do read them all. It's just I might not have time to answer them all. But on my forums, you're more likely to get an answer to your question. So check that out.
And of course, if you need help, if you're looking to hire a consultant, a developer needed to do it. No, it's not a tumor. No, that's tumor, not tutor.
If you need a tutor, check out my Access Developer Network. And make sure you get on my mailing list because I love YouTube, but they're not the best at notifying people when creators release new videos. They used to. They don't anymore.
So get on my mailing list if you want to get a notification, and you could set the frequency to, like, tell me every day or don't bother me but once a month, that kind of stuff. So it's all in there. It's like Rekall. "It's in there," or that's that Pregro, one of those two. I don't remember.
All right, so there you go. That's going to do it for your Quick Queries for today.
Again, make sure you let me know. Do you like that one or this one? Do you like the big picture or the little picture? Big picture, little picture. I don't know. The big picture is kind of growing on me, but I don't want to skew the results.
I kind of see this is the way I've been doing them forever. This one's a little more YouTubey, maybe. I don't know. Tell me what you think. Post a comment down below.
That's going to be your Quick Queries number 101. I hope you learned something. Live long and prosper, my friends. I know it's Sunday, but enjoy the rest of your weekend, and I'll see you next week. Or next time, or whatever. Whenever I make the next video, probably a video quiz next from Monday.
Bye.Quiz Q1. When someone says they want to "pull data" in Access, what should you determine first?
A. Whether they use a 32-bit computer
B. What data they need, where it is now, where it should go, and whether it is displayed or copied
C. Whether their database has more than ten tables
D. Which Access theme they are using
Q2. What is the normal relational approach for showing a customer's name and phone number with each order?
A. Copy all customer fields into every order record
B. Create a query that joins the customer and order tables on CustomerID
C. Store the customer name in the order table only
D. Use a separate database for each customer
Q3. Why is it usually better not to store a customer's address in every order record?
A. Access does not allow duplicate text values
B. Customer information belongs in the customer table and can be displayed with a query
C. Addresses can only be stored in reports
D. Order tables cannot contain text fields
Q4. When should data intentionally be copied from a related table into an order record?
A. When the value must remain historically accurate, such as a shipping address at the time of the order
B. Whenever a query has more than two tables
C. Only when the customer has no phone number
D. Whenever a form is opened
Q5. Which is an example of an intentional historical snapshot?
A. Looking up a customer's current email address in a query
B. Storing the tax rate used on a specific order
C. Sorting customers by last name
D. Linking an Excel worksheet
Q6. What is the difference between importing and linking external data?
A. Importing creates a copy in Access, while linking leaves the data in its original location
B. Importing only works with text files, while linking only works with Excel
C. Linking creates a permanent copy, while importing does not
D. There is no difference between importing and linking
Q7. Why is a staging table useful when importing external data?
A. It automatically deletes all duplicate records
B. It provides a safe place to clean, validate, and transform imported data before using it
C. It prevents users from opening forms
D. It converts all fields to text
Q8. What is generally the cleanest way to retrieve structured data from a website?
A. Manually copy and paste every webpage into Access
B. Use an API provided by the website
C. Store the website in an Access report
D. Use a table relationship to the website URL
Q9. Why can web scraping be less reliable than using an API?
A. Web scraping cannot retrieve any data
B. A website layout can change and break the scraping process
C. APIs only work with Access tables
D. Web scraping always requires SQL Server
Q10. Which Access function is often convenient for retrieving one value, such as a selected customer's phone number?
A. DLookup
B. DSum
C. DCount
D. DDelete
Q11. Why should you avoid using many DLookup functions repeatedly in a large continuous form?
A. DLookup permanently deletes data after retrieving it
B. Many repeated lookups can be less efficient than using a query join
C. DLookup only works with numeric fields
D. DLookup cannot retrieve text values
Q12. What is the normal Access tool for retrieving a group of records, such as all unpaid Florida customers?
A. A select query
B. A table design view
C. A primary key
D. An append query
Q13. What does a select query normally do?
A. Copies records permanently into another table
B. Reads data, applies criteria and sorting, and returns matching records
C. Deletes records that do not match criteria
D. Changes all linked tables into local tables
Q14. If Excel is retrieving data from Access, what is that action usually called from the Access perspective?
A. Importing
B. Exporting
C. Normalizing
D. Synchronizing
Q15. Which item is important when synchronizing data between two sources?
A. Unique identifiers and rules for handling conflicts
B. A separate table for every field
C. A report with no record source
D. A different primary key for every query
Q16. What problem can occur if two synchronized data sources both change the same record?
A. Access automatically combines the values perfectly
B. A conflict occurs, and rules are needed to decide which version wins
C. Both records are always deleted
D. The database becomes read-only forever
Q17. In an order entry system, why might a default price or discount be copied into a new order record?
A. So it can become a starting value that can later be overridden for that transaction
B. So the product table can be deleted
C. So Access can stop using queries
D. So every customer receives the same price forever
Q18. In a one-to-many relationship, where does the foreign key belong?
A. In the parent table
B. In the child table
C. In both tables as the same primary key
D. In a report footer
Q19. In a country, state, and city design, where should CountryID normally be stored?
A. In the Country table as a foreign key to State
B. In the State table as a foreign key to Country
C. In the City table only, with no State table
D. In a separate report table
Q20. When might a repeating item deserve its own table?
A. When there can be many occurrences, such as many runways for an airport
B. Only when the item has a date field
C. Only when the database is stored on a network
D. Never, because all data should be in one table
Q21. Can an Access database file be stored on a Mac drive?
A. No, Access files cannot exist on a Mac drive
B. Yes, but that does not make the Mac a database server capable of running Access
C. Yes, and the Mac can run the Access database engine natively
D. Only if the database contains no tables
Q22. What is generally recommended for a real web application that needs database access from many types of devices?
A. Put the Access back end on a Mac network share
B. Move the data to a server database such as SQL Server
C. Email a copy of the Access file to every user
D. Store all data in a Word document
Q23. How can an SQL Server view be used in Access?
A. It can be linked and used similarly to a linked table
B. It must be copied into a report before it can be used
C. It can only be viewed in Excel
D. It cannot be used by Access
Q24. What may be necessary after adding a new field to an SQL Server view that is linked in Access?
A. Reboot the database server
B. Refresh the linked field list or relink the view
C. Recreate every Access form
D. Delete the primary key
Q25. What does a Null date generally mean?
A. The date is known and is January 1, 1900
B. The date is unknown or has no value
C. The date field has been deleted
D. The date is automatically today's date
Q26. When can a placeholder date be a reasonable design choice?
A. When the application can safely reserve a date that can never be a legitimate value
B. When every date must be stored as text
C. When calculations should always fail on missing dates
D. When dates are not important to the database
Answers: 1-B; 2-B; 3-B; 4-A; 5-B; 6-A; 7-B; 8-B; 9-B; 10-A; 11-B; 12-A; 13-B; 14-B; 15-A; 16-B; 17-A; 18-B; 19-B; 20-A; 21-B; 22-B; 23-A; 24-B; 25-B; 26-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary In today's Quick Queries video, I answer a variety of Microsoft Access questions from students, forum posts, emails, and YouTube comments. The main topic is one of the most common and ambiguous questions I receive: "How do I pull data in Access?"
The problem is that "pull data" is not a specific database task. It can mean many different things, and each situation requires a different tool. Before deciding whether to use a query, VBA, an import operation, or something else, you need to identify exactly where the data is coming from, where it needs to go, and whether you want to display it temporarily or save a permanent copy.
For example, many beginners say they want to pull data from another table when they really want to display related information. Suppose you have a Customer table and an Order table. The Order table stores the CustomerID, but you want to see the customer's name, address, phone number, and other information with each order.
In a properly normalized database, you do not store the customer's name and address repeatedly in every order record. You store customer information in the Customer table and order information in the Order table. Then you create a query that joins the two tables using CustomerID. The query can display fields from both tables together whenever you need them.
This is the basic relational database approach. You should understand table relationships, primary keys, foreign keys, joins, outer joins, and normalization before building larger databases. A query lets you bring related information together without unnecessarily duplicating it.
However, there are times when copying data is appropriate. A common example is a shipping address. When a customer places an order, you may copy their current address into the order record. If the customer moves six months later and you update their address in the Customer table, the old order should still show the address where the package was actually sent.
That is not bad duplication. It is an intentional historical snapshot. The same idea applies to product prices, tax rates, commissions, discounts, and other values that must remain accurate for a past transaction. You may use an append query, update query, macro, or VBA to copy those values when an order is created.
Another meaning of "pull data" is importing information from outside Access. You may have data in an Excel worksheet, CSV file, text file, another Access database, or a file supplied by a vendor or financial institution.
Access gives you two broad choices in those situations. You can import the data, which creates a copy inside your Access database, or you can link to the data, which leaves it in the original file while allowing Access to work with it.
For real-world imports, I generally recommend importing data into a staging table first. A staging table gives you a safe area where you can clean up bad values, validate records, identify duplicates, translate text values into proper IDs, and remove inconsistent or unnecessary data before moving it into your permanent tables.
Sometimes "pull data" means retrieving information from a website. That could involve connecting to an API, making an HTTP request, downloading a file, processing XML or JSON, reading HTML from a web page, or automating a browser.
If a website provides an API, that is usually the best method because the site is intentionally providing structured data for programs to use. Web scraping can also work, but it is more fragile because a small change to the website's layout can break your process. These techniques generally require VBA and a better understanding of web services, authentication, APIs, JSON, XML, and browser automation.
Another common request is to pull a single value. For example, you may select a customer and want to display that customer's phone number. You may select a product and show its current price, or select an employee and display their email address.
For a single value, DLookup is often useful. You can also retrieve a value from a hidden column in a combo box, use a small query, or reference a control on another open form. The best method depends on where the value is needed and how frequently it must be retrieved.
DLookup is convenient for occasional lookups on a single form. However, you do not want hundreds of DLookup expressions repeatedly running in a continuous form when a query join would be faster and more efficient. If you need to display related information for many records, a query is usually the better choice.
Sometimes "pull data" means retrieving a group of records. For example, you may want all orders from last month or all customers in Florida with an unpaid balance. In most cases, that means you need a select query.
A select query reads stored data, applies criteria, performs calculations, sorts the results, and returns the records that match your conditions. Nothing has to be copied or moved. You are simply asking Access to show the records that meet your rules. That query can then be used as the record source for a form or report, or opened as a recordset in VBA.
The phrase can become even more confusing when people say they want Excel to pull data from Access. From Excel's perspective, it is pulling data. From Access's perspective, Access is exporting data.
It is important to be clear about whose point of view you are using. If Access is sending query results to Excel, creating a CSV file, exporting a report to PDF, creating a Word document, generating a text file, or sending information to another system, then Access is exporting data.
You can also use VBA to automate Excel and build custom spreadsheets from Access data. There are many ways to export information, so the key question is whether Access is receiving the data or sending it elsewhere.
Another possible meaning of pulling data is synchronization. For example, an Access database may check an Excel file each night, download new information from SQL Server, or synchronize changes between two locations.
Synchronization is more complicated than simply displaying data or importing a file. You need unique identifiers so you know which records represent the same item. You also need a way to recognize new records, changed records, deleted records, timestamps, version numbers, and conflict rules.
The hardest synchronization problem occurs when both systems modify the same record. You must decide which version wins or whether the user needs to resolve the conflict manually. Synchronization can be handled with queries, VBA, scheduled tasks, or a more advanced database platform, depending on the situation.
A related concept is pulling default values into a new transaction. A customer may normally receive a 10 percent discount, but a particular order may receive 15 percent. A product may have a standard price, but the salesperson may override it. A customer may prefer a certain shipping method, but one package may need overnight delivery.
In those cases, Access can retrieve the default value from a related table and copy it into the new transaction record. After that, the copied value belongs to that specific transaction and can be edited independently. This is common in order entry databases.
For example, a business located in Florida may default a customer's state to Florida. Once the user selects a customer, the customer's stored state can replace that initial default. The default gives you a starting value, but the final transaction value can be changed as needed.
The important thing is to ask better questions than simply, "How do I pull data?" You should identify where the data currently exists. Is it in another Access table, an Excel worksheet, a website, SQL Server, a text file, or another database?
You should also identify where the data needs to go. Does it need to appear on a form, report, query, another table, spreadsheet, text file, or external system?
You also need to decide whether the data should be displayed live from its original source or copied permanently. Are you retrieving one value or hundreds of records? Should the process run when a button is clicked, when a field changes, when a form opens, or automatically every night?
Once you answer those questions, the vague phrase "pull data" becomes a specific database task with a clear solution.
In the original situation that started this discussion, the student really wanted to display information from a related table. They had customers in one table and records belonging to those customers in another table. They wanted to see the customer's name and other details along with the related records.
The correct solution was a relational query with a join. Both tables shared a CustomerID field. By joining the tables on CustomerID, the query could display fields from both tables without copying customer information into every related record.
I also answered a question about web applications and Microsoft Access databases. Someone asked whether an Access database can be stored on a Mac and accessed through a web front end.
You can store an ACCDB file on a Mac drive, but that does not make the Mac a database server. Microsoft Access and the ACE database engine run on Windows, not macOS. A Mac cannot properly serve an Access database in the way that SQL Server can serve a database.
If you are building a real web application, I recommend moving the data into SQL Server. Your website can connect to SQL Server, and users can access the site from Windows, macOS, Linux, phones, tablets, and other devices.
If you still want to use Access itself, keep the Access front end and back end on a Windows computer and use Remote Desktop from your Mac if necessary. I would avoid placing an Access back-end file on a Mac and allowing multiple users to open it over a network. That can lead to database corruption.
Another question involved database design for an aviation database with countries, states, cities, airports, runways, companies, and related information. The question was whether there were too many tables and how to determine where foreign keys belong.
The number of tables was reasonable. The bigger issue was the direction of the relationships.
The foreign key belongs in the child table, not the parent table. For example, a Country table has a CountryID primary key. The State table should have its own StateID primary key and a CountryID foreign key. This tells Access which country each state belongs to.
Likewise, the City table should have a StateID foreign key. Each city belongs to a state, and each state belongs to a country. You can determine a city's country by following the relationships back through the State table.
A good way to think about this is as a hierarchy. Countries are above states, states are above cities, and cities may be above airports. Each child record stores the ID of its parent record.
The discussion also covered the question of whether runways should have their own table. My general rule is that when an entity can have more than a few repeating items, it should probably have a separate related table.
For example, storing three phone numbers in a customer record may be acceptable for a simple database, but if customers can have many phone numbers, a separate Phone table is more flexible. The same logic applies to addresses, email addresses, runways, and other repeating data.
A small airport may only have a few runways, but a large airport may have many. In that case, a separate Runway table with a RunwayID and AirportID is usually the better design.
This is why I encourage students to complete the basic Access lessons and at least the first two Expert-level classes before attempting to build a major business database. Learning relationships, relational combo boxes, normalization, and proper table design early can save you from major redesign work later.
I also discussed date values in Access. One student mentioned using a very old placeholder date instead of Null dates to avoid problems with calculations.
That can be a valid design decision. Null means that a value is unknown or missing. A placeholder date means that a date has been assigned, even if that date is only being used as a marker.
If your application will never have a legitimate date before a certain year, you can use an early placeholder date. For example, if a business did not begin until after the year 2000, then 1/1/2000 could be used to represent a missing imported order date.
Similarly, you might use a future placeholder date for calculations involving an unknown future date. The important thing is to choose values that make sense in the context of your database and use them consistently.
For something like a date of birth, however, I would generally prefer leaving the value as Null unless you have a meaningful reason to use a placeholder date. You can use the Nz function in calculations to handle Null values by supplying a replacement value when needed.
Another question concerned SQL Server views and whether they can be used with Access. The answer is yes.
If you create a view in SQL Server, you can link to it in Access just as you would link to a table. From Access's point of view, the linked SQL Server view behaves much like a linked table.
One important detail is that if you modify the view in SQL Server by adding or changing fields, you may need to relink it in Access or refresh the linked table's field list before Access recognizes those changes.
You can also use Access to create views on SQL Server. One method is to use a pass-through query that sends a CREATE VIEW statement directly to SQL Server. That is a more advanced technique, but it allows you to manage SQL Server objects from Access when appropriate.
Finally, I discussed the new video quizzes. These quizzes are not replacing teaching videos. They are part of the learning process.
Educational research shows that testing yourself is one of the best ways to reinforce material. A quiz requires you to actively think about what you have learned instead of passively listening to an explanation.
The quizzes also take much less time for me to create than a full TechHelp lesson. That gives me more time to work on larger projects that students have requested, including SQL Server training, developer lessons, and new handbooks.
If you have questions for future Quick Queries videos, you can post them in the forums on my website or leave them in YouTube comments. The forums are usually the best place for detailed questions because the moderators and other students can often respond more quickly.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Meaning of "pull data" in Access
Displaying related data with join queries
Normalized tables and related records
Copying historical snapshot values
Importing and linking external data
Using staging tables for data imports
Retrieving data from websites and APIs
Using DLookup to retrieve one value
Using select queries to retrieve record sets
Access exports versus Excel pulling data
Synchronizing data between sources
Copying default values into transactions
Using Access databases with Macs and web apps
Foreign keys in parent-child relationships
When to create a separate related table
Using placeholder dates versus Null dates
Using SQL Server views with AccessArticle In Microsoft Access, the phrase "pull data" can mean many different things. Before choosing a query, import tool, lookup function, or automation process, first define exactly where the data is now, where it needs to go, whether it should remain connected to its source, and whether you want to display it or permanently copy it.
The most common meaning is displaying related information from another table. For example, an Orders table may store a CustomerID, while the customer's name, address, and phone number are stored in a Customers table. You generally should not copy the customer's full contact information into every order record just so it can be displayed.
Instead, create a query that joins the two tables using CustomerID. The query can display order fields alongside customer fields, while the actual data remains stored in its proper table. This is the normal relational database approach. Customer information belongs in the Customers table, order information belongs in the Orders table, and a query brings them together whenever needed.
Relationships are important here. A primary key uniquely identifies each record in a table. A foreign key stores that identifier in a related child table. For example, the Customers table has a CustomerID primary key, and the Orders table contains CustomerID as a foreign key. One customer can have many orders, but each order normally belongs to one customer.
The foreign key belongs in the child table. If you have countries, states, and cities, the State table should include CountryID, because each state belongs to a country. The City table should include StateID, because each city belongs to a state. You can determine a city's country by following the relationships from city to state and then from state to country.
Sometimes copying data is appropriate. A shipping address is a common example. If a customer places an order today and moves six months later, changing the address in the customer record should not change the address that appears on the old order. The original order needs to preserve the address used at the time of sale.
In that situation, copying the shipping address into the order record is intentional and correct. The copied address is a historical snapshot. The same principle applies to product descriptions, unit prices, tax rates, commissions, discounts, and shipping methods. A product may have a current price in the Products table, but an old order must retain the price that was charged when that order was created.
Access can copy such values using an append query, update query, macro, or VBA. At a high level, the process should identify the source record, retrieve the relevant current values, and save copies of those values in the transaction record when the transaction is created. Once copied, the transaction values should remain independent from later changes to the source records.
A related situation involves default values. Suppose a customer normally receives a standard discount, has a preferred shipping method, or is located in a particular state. When creating a new order, Access can retrieve that value and use it as the starting value on the order. The user can then change it for that one order if necessary.
This is different from displaying a live value. A live value always reflects the current related record. A copied default becomes part of the new transaction and can be changed independently. This approach is common in order-entry systems because it balances convenience with flexibility.
Another meaning of pulling data is importing information from outside Access. The source might be a Microsoft Excel worksheet, CSV file, text file, another Access database, SQL Server database, bank download, vendor file, or other external system.
When bringing outside data into Access, you generally have two choices. You can import it, which creates a copy inside your database, or link to it, which lets Access use the original data where it already exists. Importing is useful when you need to clean, validate, transform, or preserve a local copy of the data. Linking is useful when the external data should remain at its original location and needs to stay current.
For real-world imports, it is often best to import data into a staging table first. A staging table is a temporary or intermediate table used to inspect and clean incoming data before moving it into your main tables. This helps you find duplicate records, missing values, invalid dates, unexpected text, inconsistent names, and other common data problems.
For example, an imported spreadsheet might contain state names in several formats, such as "FL," "Fla," and "Florida." A staging table gives you a safe place to standardize those values before they affect your main customer records. It also lets you review the imported data before appending new records or updating existing ones.
Pulling data from a website is a more advanced task. It might involve connecting to a web API, downloading a file, reading structured data such as JSON or XML, or extracting information from a web page. An API is usually the preferred option because it is designed to provide structured data to other programs. Web scraping, which means reading visible web page content and trying to extract information from it, can work but is more fragile because a website layout can change at any time.
In Access, web data retrieval often requires VBA or another external process. At a high level, the code would send a request to the website or service, handle authentication if required, receive the response, interpret the returned data, validate it, and save the useful information into appropriate tables. This should be designed carefully, especially if the process runs automatically or retrieves large amounts of data.
Sometimes "pull data" means retrieving one value. You may select a customer and want to show that customer's phone number. You may select a product and want to display its current price. You may choose an employee and want to show an email address.
For a single value, Access offers several options. You can use DLookup, retrieve the value from a query, use a hidden column in a combo box, or refer to a control on another open form. The best choice depends on where the value is needed and how often it must be retrieved.
DLookup is convenient when you need one value occasionally, such as displaying a selected customer's phone number on a single form. It is not always the best choice for a form that displays many records at once. Using many repeated DLookup expressions in a continuous form can be slow, especially with a large number of records. In those cases, a query join is usually more efficient because Access can retrieve the related fields in one operation.
Another common meaning is retrieving a group of records. A request such as "pull all orders from last month" or "pull all customers in Florida with an unpaid balance" usually means creating a select query. A select query reads stored data, applies criteria, performs calculations if needed, sorts the results, and returns the matching records.
A select query does not necessarily copy, move, or change any data. It simply asks Access to show the records that meet your rules. That query can be used as the record source for a form or report, exported to another format, or opened through VBA for further processing.
The word "pull" can also cause confusion when dealing with Excel or another external program. A user might say, "I want Excel to pull data from Access." From the Excel user's point of view, Excel is pulling the data. From the Access user's point of view, Access is exporting the data.
The direction matters. If Access sends query results to Excel, creates a CSV file, produces a PDF, writes a text file, or passes data to another system, that is an export from Access. Before deciding on a solution, identify which application is initiating the process and where the data needs to end up.
Data synchronization is another, more complicated interpretation of pulling data. For example, an Access database might regularly check for updates in an external file or database. A remote office might send new records to a central database. Two separate systems may need to exchange changes.
Synchronization is more than a simple import. You need a way to identify each record uniquely, determine whether a record is new or changed, decide how to handle records modified in both locations, and account for deletions. Timestamps, version fields, unique identifiers, and clear conflict rules are important. A synchronization process may be handled manually through queries or automated through VBA and scheduled tasks, but it should be designed carefully before relying on it.
Date handling is another area where data design choices matter. If a date is truly unknown, leaving the field Null is often appropriate because Null means there is no known value. However, some systems use a placeholder date when they need to avoid Null values in calculations or comparisons.
For example, if your application cannot possibly contain legitimate records before a certain year, an early date may be used as a marker for missing imported dates. Similarly, a far-future date may be used to represent an unspecified future end date. This can simplify some calculations, but it must be documented and used consistently. A placeholder date should never be confused with a real date.
Be especially careful with dates stored as text. A date field should normally use a Date/Time data type. Formatting a date for display is different from converting it to text. If a date is turned into text, date calculations, date comparisons, and date functions may no longer behave correctly until the value is converted back to a real date.
When designing tables, it is normal to wonder whether you have too many of them. Usually, the better question is whether each table represents one type of thing and whether the relationships make sense. A separate table is often appropriate when an item can occur many times.
For example, an airport can have multiple runways. A customer can have multiple addresses, phone numbers, orders, or contacts. If the number of items can grow beyond a small fixed limit, a related table is usually better than adding columns such as Runway1, Runway2, Runway3, Runway4, and so on. A Runways table with an AirportID foreign key is more flexible and easier to manage.
The key to solving any "pull data" question is being specific. Ask where the data currently exists. Is it in another Access table, a spreadsheet, a web service, a SQL Server database, or a file? Ask where the data needs to go. Does it need to appear on a form, print on a report, save in another table, export to a file, or update another system?
Then ask whether the result should be a live display or a permanent copy. Decide whether you need one value, a list of records, or an entire import. Finally, determine when it should happen: when a user selects a value, clicks a button, creates a new record, opens the database, or according to an automated schedule.
Once those details are clear, the correct tool usually becomes obvious. Use a query to display related data. Copy values intentionally when preserving history or allowing transaction-specific changes. Import or link external data when it comes from outside Access. Use a lookup or form reference for an occasional single value. Use select queries to retrieve groups of records. Use export tools when data needs to leave Access. Use a carefully designed synchronization process when two systems must exchange updates.Primary Topics displaying related table data with joins, copying historical snapshot values, importing and linking external data, retrieving single values with DLookup, select queries for record sets, exporting Access data, synchronization between data sources, default Secondary Topics Access database use on Mac, SQL Server web application architecture, foreign keys and parent-child relationships, table normalization, placeholder dates versus Null, SQL Server views linked in Access, quizzes as learning reinforcement |
