Query not showing all Records - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Query not showing all Records

Robert Carpenter

2 years ago

In my database for a Motorcycle Business, I have a Main Menu that has a button that brings up another form (continuous) showing all the records. When I select one of the records to open up another form it shows only that vehicles specifications.

It works on Record ID's 1483 up to 5793, but I don't see any of the record ID's 1482 and below. I ran the query separately to see if it was something to do with the form I made and it only shows the same records (1483 up to 5793).

I have looked at everything and there is no criteria or filters in the table or query (I have been searching for a couple days for any I might have inadvertently added). My table has 6 fields that have a one-to-many relationship with other tables. I haven't yet put any programming in the database, YET! Just wanted to make sure it worked. I am pulling what little hair I have left trying to figure this out. Hopefully, I explained my situation.

	Sami Shamma @Reply 2 years ago
It sounds to me like you have a joint problem. You are going to need an outer joint to show all the records from the main table and any matching records. Otherwise, you will only get records when both table have that ID.

Robert Carpenter OP @Reply

2 years ago

Thank you for the reply, Sami. I only have one main table with 5793 records in it right now. The 6 one-to-many-tables are small for things like engine size, year, spark plugs, batteries, etc. I apologize, as I may not be understanding what your saying either.

I did notice a little bit ago that when I run the query, I have a field0 in the results, but for the life of me I can't find it anywhere in the design of the query. It is duplicate information from another field. I can't even delete it because I can't find where it is. This may be what you are referring too. This is also not in a related table or in the table I am querying.

	Alex Hedley @Reply 2 years ago
Have you got any SELECT *'s?

	Sami Shamma @Reply 2 years ago
You see a field zero when you have the same Field twice in the query.

	Sami Shamma @Reply 2 years ago
Make a copy off the query and follow Richard's advice start removing one link table at a time until you find the problem.

	Robert Carpenter OP @Reply 2 years ago
Alex, Not that I can find. This Field0, I just posted about, has me even more stumped. I don't know where it came from or how to delete it, because I can't find it anywhere. Keep in mind I am just running the query straight from the table, no forms are involved and getting the results I am getting.

	Robert Carpenter OP @Reply 2 years ago
Thanks Sami, I will do that right now. This field0 isn't linked to any related table, it is the Model of the motorcycle and repeated from the VehMod field from the main table. I will try your suggestion, thank you.

	Robert Carpenter OP @Reply 2 years ago
Thank you Sami. I was able to delete the Field0 by deleting the double field, as you stated, but I still have the original problem, not showing the first 1482 records in the query.

	Sami Shamma @Reply 2 years ago
You have repeated the model of the motorcycle twice inside of that query that's the cause of the problem

	Sami Shamma @Reply 2 years ago
If your table have those records and your query does not that can only be a link issue. Again start a new query bring in the link tables one at a time make sure you select the correct relation between the master table and the mini table. make sure your select that you want, all the records from the master table and only those records that have a match in the table

Robert Carpenter OP @Reply

2 years ago

It is now working. Thank you very much Sami. I now know what you were talking about when you mentioned Joint. I did not have any of my tables selected with the proper Joint Type. It was kind of weird because the first table I added worked fine, but when I added the second it took a bunch of records from the query.

I started by backing up the original Query, as you suggested and messed with it but couldn't figure it out so I just made another query and replaced it with the original name. Again, thank you so much for the help! Can't wait until I can get to the point where I know what is happening right off the git go! I love this program! Thank you again!!

	Sami Shamma @Reply 2 years ago
You are most welcome. Just paying it forward, the other moderators on this site help me tremendously when I first joined.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View