Triplicate Records Being Displayed by Query (1 Viewer)

nonakag

Member
Local time
Today, 00:05
Joined
Apr 30, 2013
Messages
54
I have a form and query. The query is complex with in between date values, Primary Key (Unique vales), Non-Unique Vales, and with these values being sometimes null. The part that has me stumped are the unique values. When other search criteria are null and the one unique value is chosen, it returns three records in the record view of the form. When I go into the table, there is only one unique record. Does anyone know why it would display three records of the exact same Unique Value? Thank you in advance.
 

plog

Banishment Pending
Local time
Today, 05:05
Joined
May 11, 2011
Messages
11,611
My gut tells me this is because of a JOIN in your query. But I would need to see the SQL of your query to be sure. Post that please
 

Isaac

Lifelong Learner
Local time
Today, 03:05
Joined
Mar 14, 2017
Messages
8,738
Probably because of the joins.
 

mike60smart

Registered User.
Local time
Today, 10:05
Joined
Aug 6, 2017
Messages
1,899
I have a form and query. The query is complex with in between date values, Primary Key (Unique vales), Non-Unique Vales, and with these values being sometimes null. The part that has me stumped are the unique values. When other search criteria are null and the one unique value is chosen, it returns three records in the record view of the form. When I go into the table, there is only one unique record. Does anyone know why it would display three records of the exact same Unique Value? Thank you in advance.
Hi

We need to see more details. Are you able to upload a zipped copy of the database?
 

nonakag

Member
Local time
Today, 00:05
Joined
Apr 30, 2013
Messages
54
Code:
SELECT Reservations.[CR#_], Reservations.[Requestor Rank and Name], Reservations.Tell_No_, Reservations.Driver, Reservations.[Reporting Point], Reservations.Destination, Reservations.Purpose, Reservations.[No_of PAX], Reservations.[Date Required], Reservations.[Time Required], Reservations.[Date of Return], Reservations.[Time of Return], Reservations.Remarks, Reservations.RtnOdmtrMls, Reservations.Attachments, Reservations.Attachments.FileData, [Attachments].[FileFlags] AS Expr1, Reservations.Attachments.FileName, [Attachments].[FileTimeStamp] AS Expr2, Reservations.Attachments.FileType, [Attachments].[FileURL] AS Expr3, Reservations.AuthMemochk, Reservations.AuthMemoTmStm, Reservations.AuthSigchk, Reservations.AuthSigTmStm, Reservations.PreChkInspchk, Reservations.PreChkInspTmStm, Reservations.VcoDispChk, Reservations.VcoDispTmStm, Reservations.PostChkInspchk, Reservations.PostChkInspTmStm, Reservations.VcoInChk, Reservations.VcoInTmStm, Reservations.RtnOdmtrMls, frmCRNumRowQuery.Model_, Reservations.RegNo, frmCRNumRowQuery.[Reg_#_], Reservations.TimeIn, Reservations.TimeOut, Reservations.[Res#], [Forms]![Reservations Query]![cboResQryFltr1] AS Expr4, Reservations.Charges
FROM qryCboResNum INNER JOIN (Reservations LEFT JOIN frmCRNumRowQuery ON Reservations.[CR#_] = frmCRNumRowQuery.[CR#_]) ON qryCboResNum.[CR#_] = Reservations.[CR#_]
WHERE ((([Forms]![Reservations Query]![cboResNum]) Is Null) AND (([Forms]![Reservations Query]![optBtnDteFltr])=False)) OR (((Reservations.[Res#])=[Forms]![Reservations Query]![cboResNum]) AND (([Forms]![Reservations Query]![cboResQryFltr1]) Is Null) AND (([Forms]![Reservations Query]![optBtnDteFltr])=False)) OR ((([Forms]![Reservations Query]![cboResQryFltr1]) Is Null) AND (([Forms]![Reservations Query]![cboResNum]) Is Null) AND (([Forms]![Reservations Query]![optBtnDteFltr])=False)) OR (((Reservations.[Res#])=[Forms]![Reservations Query]![cboResNum]) AND (([Forms]![Reservations Query]![cboResQryFltr1]) Is Null) AND (([Forms]![Reservations Query]![optBtnDteFltr])=False)) OR (((Reservations.[Date Required])>=[Forms]![Reservations Query]![txDteRq_]) AND ((Reservations.[Date of Return])<=[Forms]![Reservations Query]![txDteRtn_]) AND (([Forms]![Reservations Query]![cboResNum]) Is Null) AND (([Forms]![Reservations Query]![optBtnDteFltr])=True) AND ((frmCRNumRowQuery.[CR#_])=[Forms]![Reservations Query]![cboResQryFltr1])) OR (((Reservations.[Date Required])>=[Forms]![Reservations Query]![txDteRq_]) AND ((Reservations.[Date of Return])<=[Forms]![Reservations Query]![txDteRtn_]) AND (([Forms]![Reservations Query]![cboResNum]) Is Null) AND (([Forms]![Reservations Query]![optBtnDteFltr])=True) AND ((frmCRNumRowQuery.[CR#_])=[Forms]![Reservations Query]![cboResQryFltr1])) OR (((Reservations.[Date Required])>=[Forms]![Reservations Query]![txDteRq_]) AND ((Reservations.[Date of Return])<=[Forms]![Reservations Query]![txDteRtn_]) AND ((Reservations.[Res#])=[Forms]![Reservations Query]![cboResNum]) AND (([Forms]![Reservations Query]![cboResQryFltr1]) Is Null) AND (([Forms]![Reservations Query]![optBtnDteFltr])=True)) OR (((Reservations.[Date Required])>=[Forms]![Reservations Query]![txDteRq_]) AND ((Reservations.[Date of Return])<=[Forms]![Reservations Query]![txDteRtn_]) AND ((Reservations.[Res#])=[Forms]![Reservations Query]![cboResNum]) AND (([Forms]![Reservations Query]![cboResQryFltr1]) Is Null) AND (([Forms]![Reservations Query]![optBtnDteFltr])=True)) OR (((Reservations.[Date Required])>=[Forms]![Reservations Query]![txDteRq_]) AND ((Reservations.[Date of Return])<=[Forms]![Reservations Query]![txDteRtn_]) AND (([Forms]![Reservations Query]![cboResQryFltr1]) Is Null) AND (([Forms]![Reservations Query]![cboResNum]) Is Null) AND (([Forms]![Reservations Query]![optBtnDteFltr])=True)) OR (((Reservations.[Date Required])>=[Forms]![Reservations Query]![txDteRq_]) AND ((Reservations.[Date of Return])<=[Forms]![Reservations Query]![txDteRtn_]) AND (([Forms]![Reservations Query]![cboResQryFltr1]) Is Null) AND (([Forms]![Reservations Query]![cboResNum]) Is Null) AND (([Forms]![Reservations Query]![optBtnDteFltr])=True))
ORDER BY Reservations.[Date Required] DESC;
 

isladogs

MVP / VIP
Local time
Today, 10:05
Joined
Jan 14, 2017
Messages
18,186
Try SELECT DISTINCT ... instead of SELECT. That will give unique values only
 

plog

Banishment Pending
Local time
Today, 05:05
Joined
May 11, 2011
Messages
11,611
That's not right. I don't know what you are trying to do, but this method is not the right way. Your WHERE clause is over 2700 characters long. Some of the conditions don't even involve the data in the query but only check the form for presence of data:

Code:
...WHERE ((([Forms]![Reservations Query]![cboResNum]) Is Null) AND ...

What are you using this query for? To filter the results that show on a form? If so, the better method is to use Form.Filter property:


With that said, your duplicates are definitely caused by your JOIN(s):

Code:
FROM qryCboResNum INNER JOIN (Reservations LEFT JOIN frmCRNumRowQuery ON Reservations.[CR#_] = frmCRNumRowQuery.[CR#_]) ON qryCboResNum.[CR#_] = Reservations.[CR#_]

One of those datasources has multiple [CR#_] values in it and it is causing your duplicates.
 

plog

Banishment Pending
Local time
Today, 05:05
Joined
May 11, 2011
Messages
11,611
Uh oh. Now I looked at the SELECT and think you have bigger problems:

Code:
SELECT... Reservations.AuthMemochk, Reservations.AuthMemoTmStm, Reservations.AuthSigchk, Reservations.AuthSigTmStm, Reservations.PreChkInspchk, Reservations.PreChkInspTmStm...

What those field names tells me is that you have not properly normalized your data. I have no idea what "AuthMemoChk" is for, but by looking at other fields I can tell you are using prefixes and/or suffixes in your field names. Prefixed and suffixed field names are a huge red flag that you have not set up your tables properly. When you do that you are essentially storing data in field names and that is a no no.

Most likely the Reservations table needs to be broken up into mor tables to accomodate your data. Why do you have so many similarly named fields? What data are they holding?
 

Isaac

Lifelong Learner
Local time
Today, 03:05
Joined
Mar 14, 2017
Messages
8,738
Because there are multiple instances in the table you are left joining to
 

nonakag

Member
Local time
Today, 00:05
Joined
Apr 30, 2013
Messages
54
Query.png
 

Isaac

Lifelong Learner
Local time
Today, 03:05
Joined
Mar 14, 2017
Messages
8,738
I'm not criticizing why the other tables are there I'm just letting you know that when you left join to a table on the right side that has three instances of the primary key that's being joined on, you're going to get three copies of the record from the left side table.
 

nonakag

Member
Local time
Today, 00:05
Joined
Apr 30, 2013
Messages
54
I'm not criticizing why the other tables are there I'm just letting you know that when you left join to a table on the right side that has three instances of the primary key that's being joined on, you're going to get three copies of the record from the left side table.
I'm open to solutions. I'm a novice at access. Please show me a better way to join the tables and get the data into the form that I need. The Reservsations table is unique to the reservations number (primary key). The Left table is unique by CR# and the right table is for combo box values for the reservation number. Thank you.
 
Last edited:

nonakag

Member
Local time
Today, 00:05
Joined
Apr 30, 2013
Messages
54

Try SELECT DISTINCT ... instead of SELECT. That will give unique values only
Isla, it says Distinct cannot be used with attachments.. any way around that?
I'm not criticizing why the other tables are there I'm just letting you know that when you left join to a table on the right side that has three instances of the primary key that's being joined on, you're going to get three copies of the record from the left side table.
Isaac, could you please tell me how to make fix the join? Thank you.
 

Isaac

Lifelong Learner
Local time
Today, 03:05
Joined
Mar 14, 2017
Messages
8,738
Isaac, could you please tell me how to make fix the join? Thank you.
I cannot tell you because I don't know your data - only you do. It may be a matter of an additional join predicate.

For example, a person may join like this:

- from tblPeople left join tblAddress on tblPeople.ID = tblAddress.ID_People
and they may find out they are getting one record for every tblAddress record with that people ID. (multiple)

They may get more information about their data structure/business process, & then change it to:
- from tblPeople left join tblAddress on tblPeople.ID = tblAddress.ID_People and tblAddress.CurrentAddress=1

It depends on your data. Study joins completely and how they work before continuing to try to use the database in any way.
 

nonakag

Member
Local time
Today, 00:05
Joined
Apr 30, 2013
Messages
54
Uh oh. Now I looked at the SELECT and think you have bigger problems:

Code:
SELECT... Reservations.AuthMemochk, Reservations.AuthMemoTmStm, Reservations.AuthSigchk, Reservations.AuthSigTmStm, Reservations.PreChkInspchk, Reservations.PreChkInspTmStm...

What those field names tells me is that you have not properly normalized your data. I have no idea what "AuthMemoChk" is for, but by looking at other fields I can tell you are using prefixes and/or suffixes in your field names. Prefixed and suffixed field names are a huge red flag that you have not set up your tables properly. When you do that you are essentially storing data in field names and that is a no no.

Most likely the Reservations table needs to be broken up into mor tables to accomodate your data. Why do you have so many similarly named fields? What data are they holding?
Plog, thank you. What is the best way to break up the tables but still put all the information into one form? Would there need to be a Join Query in order to tie in all data on the one form? Thank you.
That's not right. I don't know what you are trying to do, but this method is not the right way. Your WHERE clause is over 2700 characters long. Some of the conditions don't even involve the data in the query but only check the form for presence of data:

Code:
...WHERE ((([Forms]![Reservations Query]![cboResNum]) Is Null) AND ...

What are you using this query for? To filter the results that show on a form? If so, the better method is to use Form.Filter property:


With that said, your duplicates are definitely caused by your JOIN(s):

Code:
FROM qryCboResNum INNER JOIN (Reservations LEFT JOIN frmCRNumRowQuery ON Reservations.[CR#_] = frmCRNumRowQuery.[CR#_]) ON qryCboResNum.[CR#_] = Reservations.[CR#_]

One of those datasources has multiple [CR#_] values in it and it is causing your duplicates.
Plog, thank you. Yes, if you see my form the gray upper area are the filters for records shown below. The filters have Boolean option buttons that affect the query filtering by dates, CR#, or Reservation Number. CR# will always be many to one for this query, when using reservation number, the other options are cleared as Reservation Number is the primary key. Could it be that there is a leak in my query allowing rows to be let in? Thank you.
 

plog

Banishment Pending
Local time
Today, 05:05
Joined
May 11, 2011
Messages
11,611
What is the best way to break up the tables but still put all the information into one form?

That's not how databases work. Forms don't dictate how you store your data. The data itself dictates how you store data. You need to read up on normalization:


That's the process of properly structuring your tables and fields. After that you should work on Reports to make sure you can get the data out of the database in the manner you need. Then finally, you work on Forms to input data.
 

isladogs

MVP / VIP
Local time
Today, 10:05
Joined
Jan 14, 2017
Messages
18,186
Isla, it says Distinct cannot be used with attachments.. any way around that?
Another reason not to use multivalue fields of which attachment fields are a subtype

Your attachment field is made up of 3 parts: FileData, FileName, FileType. Together these manage the MVF component of the field
You can use DISTINCT with FileName and/or FileType but not with FileData or the attachment field itself.

However, now I've seen the full SQL including the use of MVFs/attachments, I would be very surprised if using DISTINCT on these will solve your problem.
 

Users who are viewing this thread

Top Bottom