Microsoft Access 2010 Tutorial Part 09 of 12 - Customer Queries

[@mom2673]

I am a computer technician that can repair a pc blindfolded. I cannot however, create an Access Database from scratch (or from template). That is, until NOW! These are the most awesome videos I have found while scouring the internet to learn Access. Thank you so much for your time and effort involved in this. I can assure you I will be buying the advanced tutorials very soon!

[@tanysha1450]

Back in highschool I was taught the basics in Access, but there is no comparison with these tutorials. I have been watching them all day long and by now have many an idea on how to implement this software in home and job activities.Congratulations for the awesome work, effort and humbleness in teaching what you know to others.

[@rubensteinspeaker2644]

I am SO grateful for these tutorials. I am using Access to organize an academic literature review for a doctoral program and it's really helping me keep track of hundreds of articles. These tutorials are so clear and it's great to refer back to them when I forget a step.

[@599CD]

+hrrs95 Glad you like them. :)

[@599CD]

Yes, that was one of my frustrations with teaching in a classroom setting (which I did for about 10 years before starting my web site and putting videos online). In a class of 20 people, ten would get everything just fine, five would be totally lost, and five would be bored because they were fast learners. The class would always end up moving at the pace of the slowest student. Online videos let you learn at your own pace, but still ask questions of the instructor if you get lost.

[@Lizardguy3]

You have just saved me! I have a big test Friday and I was never taught to use Access, which was half of the test. Thank You!

[@599CD]

Thank you for the compliments. Remember a QUERY only shows data that's in the TABLE under it. It doesn't store any data of its own. When you enter or change data in the QUERY that data goes right into the TABLE.

[@599CD]

Remember, AND across, OR down. So if you want to see both people from NY and OH, you need to say: NY OH in the OR down rows. Alternatively, you could put this in one criteria field: "NY" OR "OH"

[@472magdy]

these series of videos are very clear and helpful, thanks

[@jumanakhasawneh3806]

hi I'm a Ball State university Online student, & living in Jordan, & I'm doing access now for my last 3 assignments,,i really like your tutorial about creating a query, which help alot.

[@599CD]

Thank you very much for your kind words! It's posts like this that make me want to keep making new tutorials.

[@599CD]

Yep. Just close and rerun the query and it will refresh with data from the table. Remember, data is never stored in a query. It just shows you what's in the underlying table at that time.

[@moranih]

Thanks a lot. I'm more confident now to create and present neat data now. I'd forgotten the basics of this beautiful office tool.

[@599CD]

Yep. Next time to re-run the query it will have your updated information in it. Remember, the query doesn't store any data itself. It's just a representation of the data in the table. Every time you re-run the query, it gets updated information for you.

[@599CD]

Well, you could try a FIND DUPLICATES query to catch duplicate entries. If you want to see them as you're typing values in you could use a SEARCH COMBO BOX.

[@cyphi1]

Glad you uploaded more videos!!! I subbed a while ago, but there weren't many videos. Thanks for all the help!!!

[@599CD]

You can use dates in criteria. I show this in my Access Beginner Level 3 class. For example, just type in: BETWEEN 3/1/12 AND 3/31/12.

[@rachelreid8695]

Hi just want to say thanks so much! I was having quite a lot of difficulty doing this and your video made it quite a piece of cake!! Thanks again.

[@599CD]

Welcome

[@599CD]

You can display the data however you want if you make an actual REPORT to do this. However, if you want to just show your data this way in your final query results, add the fields like this: LastName (sorted), FirstName (sorted), LastName. Now check off the SHOW box for the first LastName. It will be used for your sort order, but won't display.

[@TheGlitched64]

I am struggling to get a query to work correctly when more than one table is involved. I am unsure if it's a problem with how I create the table relationship or what, but do you cover that in your advanced tutorials or is there a simpler answer?

[@JPMongeS]

Hi, thanks for your tutorials, I'm learning very much! - Quick question: whenever I need to add further information on the table, will the query that is based on that table recognize the new information and add/use it to run its previously determined function? Thanks again!, Regards from Costa Rica!

[@rui386]

I'm from portugal, and you have no idea how much you've helped me :D

[@SuperPetitmalin]

Thank you very much for your great tutorials, I am in the process of making an Access table that would keep track of how many times an item has been purchased thus generating a new table that would get its data from a purchase order table, each particular item would show many times on the table on one row for every single purchase, I would greatly appreciate if you could shed some light.

[@599CD]

Relationships are beyond my Beginner Access lessons. I cover them in Access Expert 1. Without seeing your query, I can't tell you what's wrong. If you're missing data, you probably need to set up an OUTER JOIN. Again, see my Access Expert 1 lesson for how that's done.

[@599CD]

Yep. Queries only SHOW the data stored in your tables. That's a LIVE version of your data.

[@sakke1379]

you explained this in 2 hours , most school teachers would take half a year to teach this :p

[@bboro30]

Took me about 3 weeks to get half way through a book on this and couldn't do jack when i actually got on access. Not even 30 min into these videos and I was WAY farther along than the 3 weeks of reading. Thanks Sir

[@599CD]

bboro30 Thanks. :)

[@jab71man]

This is amasing!! I plan to purchase your product, I have learned alot so far. Thank you!!

[@adya1]

WOW!!! Seriously, ive being so lost doing access at college. Our teacher doesn't help us at all and doesn't really care. Most tutorials are advanced and not explained well. Yours on the other hand is so helpful and easy to understand. For the first time, I feel like im starting to understand what im doing. Thank you very much !! Next block im doing java. Do you have any java tutorials? lol that would be great also.. thanks

[@booyakasha1988]

my pass in business admin is because of you, cheers

[@rocren1192]

Very well designed and edited for beginners. Thanks!

[@599CD]

Welcome

[@katty0572]

i actually sorted it out. Was also trying to create a multi criteria form for a report using combo as one of the unbound inputs. Sorted as well. your video as so educating. Quick question. is it possible to disable or hide the quick access toolbar.

[@599CD]

This is BETTER done with a form. Create a form and set it's AllowEdits property to NO. However, if you want to do it directly in the query, open they query's property sheet and set the Recordset Type property to SNAPSHOT.

[@nealhoward7899]

Is there a way to have a lookup with a parameter query? For instance when I run the query I know how to make it ask which state I want, but how do I get Access to give me a list of choices of all the available states to pick from?

[@norina195]

I have a question: If I just updated a record in the CustomerT that includes "NY", would the NYQuerie automatically update that record as well?

[@akshaysud]

You are awesome. Brilliant work.. :). I have a question..are the Advanced level videos available on KZbin as well ?

[@sirrahphotography5930]

I'm having an issue with creating the Queries. When going to Create/Query Design....choosing the First and Last Names, the only thing that changes is the first and last names are first in my table. The remaining fields are still there. In your tutorial, only the first and last names were in the query after hitting "run". So, what am I missing? Update: Interesting enough, if I use the wizard to create/update a query, it works as advertised. I'm using Access 2013.

[@599CD]

Awesome

[@rreggdragon]

hi how do i put a filter on a quary if it´s a price i have to put the numbers or it only works with letters??? And thank you a lot for the help you gave me. :)

[@jasondawkins1761]

Thank you sir your tutorials have been a tremendous help and i appreciate it and thank you for spending the time and putting in your your best of energy, knowledge and equipment to do this many thanks. Visited your website and is presently checking some stuff out

[@briankitchen2294]

I realy like how you do your videos but i am trying to find a way to run a query using 2 dates that can be found on a form then run the report... i have the form query and the report but its getting the right criteria in the query thats i am having a hard time with.. any ideas?

[@599CD]

I do post advanced TIPS from time to time, but I don't post my FULL advanced courses here on KZbin. That's my business... it's what I do for a living... so posting them here would not be good for keeping my lights on. :) I love helping people. I wish I could post everything here for free, and I certainly post as many free tips and tutorials here as I can... but I'm hoping that you'll love them so much that you'll visit my web site and pick up a couple of my advanced tutorials.

[@christinekeough9285]

If you add more customers to the Customer Table later, and they are in (for example) NY...will they automatically be added to your saved Queries as well?

[@599CD]

There is no data saved in a query. Queries just show what's in the table at that moment.

[@laurencejhasson]

Is it possible to sort by, for example, LastName and then FirstName but have FirstName appear before LastName in the results? This is essential, say for example you wanted the results sorted on a number of different variables (State, LastName, Credit) but you dont want your results to be a mess of variables...

[@599CD]

+laurencejhasson Sure, just add the fields a second time. Sort the first set the way you want them and HIDE them. Or, make a single "FullName" field by concatenating the two, so you get "Smith, Joe" or whatever you want. I cover this in my lessons.

[@baholete]

Joe came on top of Susan ;] GREAT! tutorial for my exam tomorrow, thanks a lot!

[@redfoxarts]

is there a shortcut? Tell me, if possible, which videos are needed to watch in order for me to make my own contact database just like the ms access 2010 template.

[@vedantraj]

YOU ARE AN EXCELLENT TEACHER SIR!! THANKS :)

[@MyselfStoychev]

If there are like 1000 names, how can i check for a person added twice? In excel, if you start adding a name that already exist, Excel shows it like already existing note.

[@adammaziejuk9142]

Save as also nice to use "F12", I am not sure if this works on English Systems, but definitely on German ones. Thanks a lot for this nice introduction into Access, very relaxing form of education :)

[@599CD]

Welcome.

[@captainstrass]

sir a very big tnx to you .it is a big help to me for my exam.. as a IT student ^_^ and for our thesis ...

[@4NPSP04]

What about if you wanted to run a criteria using dates for example 3/1/2012 to 3/31/2012?

[@sherrygoldsmith491]

I never thought that learning Access could be this simple. Thank you for the wonderful video lessons. Question: when creating a query I want only the active customers to show and must be doing something wrong because all fields show when I run the query.

[@599CD]

Thank you. 599cd.com/Criteria

[@faerieloveheart]

Hi! Your video is like our favorite ever. I have a lil question: If I want to make a query that is sorted by Last name and THEN First name, but want my report to show up First Name then Last name, how do I accomplish this? THANKS!!!!!!!!!! :) :) :)

[@lilmo2104]

How can i set up a schedule through query? By watching this video i know how to select the fields i want by creating the query, but lets say i want to select a specific date from my customers/workorders. Thanks

[@conspiracies1]

Hi,I'm creating a database in Access I have created 3 tables which are customer, vehicles and Invoice but how do I link the primary key of customerID to the Invoice table as a foreign key? Thank you very much

[@daylisaelisabeth9883]

Thank you very much sir . The instruction were very helpful

[@jahnava]

Hello again :) I created a Query from a 1:n relationship (one to many) for some reason when I click to "run" I can't see all my info from my table. For example, I created a relationship with Company, Id numbers and Employee. In the Query I add the Companies Nr. and then Name and Last name for the employees...but Not working :(...any help? thanks a lot

[@599CD]

Just add criteria. In Level 3 I teach you how to use the BETWEEN keyword, so you can say something like: BETWEEN #1/1/2010# AND #2/1/2010#

[@PattyThomas1969]

Do you have anything on assigning an alias? Oh, and these are great tutorials! Thanks!!

[@akshaysud]

Thank you so much for the help :)..I'll definitely pick up the advanced tutorials :)

[@xiaoshibo]

hi, I still want to show FirstName as first colone, then LastName as Second colone, but I want to sort LastName first, then the FirstName. Is it possible? Thank you.

[@599CD]

Yep

[@zainabal-khawaher3160]

First of all thanks a lot for you , it was very clear and helpful. Second I have a question , can we not separate the fist name and family Name like putting for example full name , or the space between the first and last name if it is in one column it will effect?!

[@599CD]

First, you're welcome. Second, I don't recommend putting FN and LN together in one field. I know people with compound first names (Mary Jo Smith) and last names (Ron Van Der Wilde). It's much easier to put these names back together later than it is to try and separate them.

[@andrewwerage3741]

Hi Need help.In my table one of the column with contract type I have 5 different contracts ,when I try filter I am missing two type of contract only see 3 of them instead 5 ! How can I add missing contract type?

[@599CD]

If you still need it: 599cd.com/Ask

[@amanda8093]

I have created a database for my employees. I have a column called SoicNum but not every employee will have this number. How do I get the query to only show employees that have something entered in that field. Btw your videos are amazing. Thanks for all the help.

[@liviuvoica8700]

Hello. I have a loading agenda to follow all the trucks that are loading goods from different countries and are unloading in my country. In a matter of way, this piece of information is depending on the carriers, thereby I must upload the loading and unloading dates every day and inform the warehouse for upcoming truck in the next week. I built up a report called „Agenda by loading/unloading Dates” based on a query, I also put two parameters to run every time I open up the report - „Unloading Date” (which is an date interval (eg: 30.11 - 04.12)) and „Warehouse” (which is the name of the warehouse). Moreover in that report I put 4 buttons to send e-mail to the warehouses (button A is corresponding to warehouse A; button B to warehouse B and so on). When I press button A the whole report is re-querying and it will ask me again to input the first and second parameter („Unloading Date” and „Warehouse”). Is it possible to do the following? 1. When I open up the report I want to enter only the first parameter and keep it that way; 2. When I hit button A in the report I want the first parameter to remain unchanged (eg: 30.11 - 04.12) and then to enter only the second parameter (which is the warehouse name); 3. When I hit button B in the report I want the first parameter to remain unchanged and then to enter only the second one. 4. And so on for button C and D. Thank you again for all your help!

[@599CD]

tl;dr

[@katty0572]

can you direct me where you deal with filtering of dates in the query cause am sure you have covered it. You have a gift keep doing what you are doing

[@kaywhyS09]

I have a question for example i just want to show state from NY and OH is that possible Criteria: "NY","OH" i got errors :(((

[@BhaktiAartiSongs]

what if we want to create query with constraint that name should start with "A"

[@md.riponali5287]

i have a question.Suppose i have more than 30 columns in o access table.How can i print them in a single page?

[@599CD]

Squeeze them into your report REAL tight.

[@lefleurtobias3034]

Hi. Can you help please.I need to make student cards for my school. I need a photo of each student plus some other info. How do I get photos on? Thanks.

[@TheKiller7276]

add it in the table as a field called "photos" and use the data type OLE Object. Then just copy and paste the photos in there

[@lefleurtobias3034]

Thanks. I'll try it. Did you do it before and it worked for you ?

[@TheKiller7276]

Le Fleur Tobias Well no, but I'm assuming that's how it works

[@daylisaelisabeth9883]

I have noticed that a field in a query is editable... How can I make queries not editable... so I can not change the information in it but only get information out of it

[@gence54]

dude your a hero!!!!!!!!!!!!!!!!!

[@599CD]

You can create an alias with a query field like this: FullName: FirstName

[@margaretjohnson1193]

I want to create a DB of person's name, address, birth date, date of death, birthdays for Mr & Mrs & children & wedding anniversary. Is it possible? I send out cards and letters and I need to track the sending as well as store all that information, bringing up which cards need to go out in a certain week.

[@599CD]

599cd.com/HolidayCards

[@chenellewilliams2443]

How can you sort by the max value of a field with additional fields e.g. the most expensivee car , its type and model

[@599CD]

Chenelle Williams Just set up a SORT for the car value field.

[@sammarieobrown5845]

so what if wanna know how many people joined the company for e.g. after 2005??

[@moviesonrequesthd7198]

>=2005

[@599CD]

>=#1/1/2005#

[@carlosaceves5493]

Just subscribed, thanks for the tutorial.

[@599CD]

welcome

[@muhammadnasrawangolra2112]

quite helpful sir! it is helping me practically at my work :-)

[@599CD]

Thanks

[@bravekingsolomon]

Great, but can someone help me? I want to create a report to cllect information "between dates". What can I do?

[@599CD]

599cd.com/Between

[@yuichan4535]

so helpful! thank you very much for this!!!

[@599CD]

Welcome

[@599CD]

Thank you!

[@peterwillis100]

So awesome. Thank you!

[@599CD]

Search for this video on my Channel: "Getting a Value from a Form for Query Criteria in MS Access"

[@ousmanetall1286]

These are very cool tutorials, given for free, thanks you very much, but how can we get your others level, like expert level.

[@599CD]

They're on my web site. 599cd.com/1dollar

[@599CD]

You would need more than what's covered in just this BEGINNER tutorial. You'd need to learn how to set up relationships between different tables, which I cover in my EXPERT series of tutorials - available only on my web site. 599CD(dot)COM.

[@avnoor_123]

How to create query for Active Customers?? Please explain

[@599CD]

599cd.com/ask

[@kzeuz999]

My ctrl f key is not working, how do I fix this

[@599CD]

That's not a problem I can help you with. Sounds like a PC issue.

[@EpicCODgamerz12345]

Thanks really helped

[@johnny_rain3226]

great tutorials tnx very much

[@599CD]

You're welcome

[@599CD]

Working with multiple tables and relationships is something I cover in my EXPERT classes. I also have a video seminar JUST on relationships. You can find out more here: 599CD(dot)com/XSCW1Y

[@conspiracies1]

Thank you very much!

[@nakalembevivian5216]

6:45 Joe came on top of Suzan!

[@nischalbhatt7899]

Thats what she said!

[@599CD]

Nischal Bhatt LOL

[@gswwe]

x'D

[@jerichblue21]

What of the other queries? Delete, Update?

[@599CD]

599cd.com/ActionQuery

[@monafathy9673]

VERY GOOD

[@599CD]

thx

[@4NPSP04]

Thanks :-)

[@599CD]

I can't tell what the problem is just from this. I would have to see your database. You can submit it to me via my web site at 599CD(dot)COM/TechHelp

[@Rashid2721]

great

[@rcx8666c]

QUERIES ARE COOL

[@599CD]

indeed

[@alisengary8204]

thanks

[@599CD]

Welcome

[@599CD]

Query Criteria: IS NOT NULL

[@rakshithpoojary5666]

what is the difference between query and sorts?

[@599CD]

Apples and oranges. You can SORT a QUERY.

[@599CD]

See this tutorial on my web site: 599CD(dot)com/X94XQE