Unique Record (Multiple Tables) (1 Viewer)

[MayaMana]

I have 3 tables that are connected in a query by an ID number. Everything works well except that I get back 3 duplicate records instead of just one record. I attempted to try to fix this error by adding a group by first to one of the unique fields. However since I have a parameter up to prompt the operator for the ID number (this will bring up a set group of results for a time period) I get an invalid message saying that I cannot have a parameter and use the group by function.

Sorry if I wasn't very clear, but any help would be greatly appreciated. If any more information is needed just ask. Also I am using access 2003.

 

[MSAccessRookie]

I have 3 tables that are connected in a query by an ID number. Everything works well except that I get back 3 duplicate records instead of just one record. I attempted to try to fix this error by adding a group by first to one of the unique fields. However since I have a parameter up to prompt the operator for the ID number (this will bring up a set group of results for a time period) I get an invalid message saying that I cannot have a parameter and use the group by function.

Sorry if I wasn't very clear, but any help would be greatly appreciated. If any more information is needed just ask. Also I am using access 2003.

This type of Data Error often indicates an problem with Joins between Tables, so I think there may be something else going on here. Can you post the SQL Code from the Query for us to look at?

 

[MayaMana]

The joint fields are not labeled the same in the 3 tables, but they are the same value.

SELECT Table1.longWheelNumber, Table1.HeatId, Table1.splitTime, Table1.Diff, Table1.name, Table1.maxSignalTreadGate2dB, Table2.LTemp, Table3.Height, Table3.Weight
FROM (Table1 LEFT JOIN Table3 ON Table1.HeatId = Table3.heatIdentifier) LEFT JOIN Table2 ON Table1.HeatId = Table2.HN
WHERE (((Table1.HeatId)=[Heat ID]));

^Works but gives me 3 of each record

SELECT First(Table1.longWheelNumber) AS FirstOflongWheelNumber, Table1.HeatId, Table1.splitTime,  Table1.Diff, Table1.name, Table1.maxSignalTreadGate2dB, Table2.LTemp,  Table3.Height, Table3.Weight
FROM (Table1 LEFT JOIN Table3 ON Table1.HeatId = Table3.heatIdentifier) LEFT JOIN Table2 ON Table1.HeatId = Table2.HN

^Gives error message

You tried to execute a query that does not include the specified expression 'HeatId' as part of an aggregate function.

The regular query works fine, but when I enter in a value for a parameter it gives me back 3 copies of each result.:banghead:

 

[dylan_dog]

Try renaming the identifier fields in all tables to HeatID.

SELECT Table1.Diff, Table1.name, Table1.maxSignalTreadGate2dB, Table2.LTemp, Table3.Height, Table3.Weight
FROM (Table1 LEFT JOIN Table3 ON Table1.HeatId = Table3.heatId) LEFT JOIN Table2 ON Table1.HeatId = Table2.HeatID
GROUP BY Table1.Diff, Table1.name, Table1.maxSignalTreadGate2dB, Table2.LTemp, Table3.Height, Table3.Weight, Table1.HeatId
having (((Table1.HeatId)=[Heat ID]));

 

[MayaMana]

I am not able to change any of the table designs, they are from a SQL database that I only have permission to link to and create queries from.

 

[dylan_dog]

Hi,.
I tried to replicate your tables. Query 3 is a copy of your SQL statement and you can see the output. Are the tables I made the same as yours? The query returns 6 results, because I have 3 entries with identifier "1" in table2 and two in table3. Access will output all possible combinations as a result of your query, in this case 6 combinations from table2 and table3.

 

[vbaInet]

... so if you want to display the results on a form or a report, split your query into two parts and join them up using a subform/subreport.

 

[MayaMana]

Thank you guys.
dylan - Not quiet, the only table that has more then one input sometimes is the Heights.
vbaInet - I am exporting the data to a chart program that we have. It does not allow me to use more then one table/query/view etc at a time.

I think my issue is with the measurements, so I am working on trying to get the actual table the data is being drawn from since there is no unique identifier in this view.

Edit:
Well the guy who set up the view I am having an issue with is going to create another view for me. Thank you for your help though.

 

[Pat Hartman]

You are getting a Cartesian Product. That's what hapens when you join tables that have a common field but are not hierarchially related. I don't understand the data so I can't give you a solution. I'll give you an example using a situation most will understand. You have a table of students, a table of classes attended by students, and a table of vehicles registered to students. It makes sense to join students to vehicles or students to classes but it does not make sense to join students to both tables in the same query. The result will be a row for every student times the number of vehicles he has registered times the number of classes he is registered for. In most cases, everything will look normal because it will be rare for a student to have more than one vehicle but whenever a student has two or more, the query will appear to "duplicate" data. That's what seems to be happening in your case.

 

[MayaMana]

I think it had to do with there not being an unique identifier in one of the views I was given to use. I am not sure for certain though. The guy made another view for me and it now seems to be working fine, I had him add in a few more fields including an identifier field.