Dear Query GODS !!!!!!!!!

[trackmedic]

I posted a "issue" I was having with a query a few days back. To all who helped me, thank you - but, nothing worked. This is what I have. I have three tables that I need to pull info from. These tables get thier respective information from a main form and 2 subforms. I have relationships built and the material is there. As of this time, I have only 1 record in the tables.

I built a query via the wizard and all of the relationships showed up and everything seemed good. When I ran the query, I get the 1 record three times. Basically a loop of info. Someone recommended taking out an astrik that is in the string but that does not help as it screws up the query.

Please see the SQL of the query below.

SELECT concerns_sub.Number, vessel_positives_table.Number, main_input_table.ID, main_input_table.Date, main_input_table.Vessel, main_input_table.Visitor, main_input_table.Location, main_input_table.[Last Fire Drill], main_input_table.[Last MOB Drill], main_input_table.[Last AS Drill], main_input_table.[Last JSA], main_input_table.[Last JSA Date], vessel_positives_table.positives, vessel_positives_table.comments, concerns_sub.concerns, concerns_sub.comments, main_input_table.ID, main_input_table.ID, main_input_table.ID, main_input_table.ID, *
FROM (main_input_table INNER JOIN vessel_positives_table ON main_input_table.ID = vessel_positives_table.Number) INNER JOIN concerns_sub ON main_input_table.ID = concerns_sub.Number;

I cannot figure this one out. A while back, I had basically the same problem but I cannot remember how I fixed it!?!?!?!?!?!

 

[KenHigg]

I don't mean to sound negative but if you'll present your sql, code, etc in a format that's a bit easier to read you are more likely to get someone to help.

Example:

SELECT concerns_sub.Number, 
   vessel_positives_table.Number, 
   main_input_table.ID, 
   main_input_table.Date, 
   main_input_table.Vessel, 
   main_input_table.Visitor, 
   main_input_table.Location, 
   main_input_table.[Last Fire Drill],  
   main_input_table.[Last MOB Drill], 
   main_input_table.[Last AS Drill], 
   main_input_table.[Last JSA], 
   main_input_table.[Last JSA Date], 
   vessel_positives_table.positives, 
   vessel_positives_table.comments, 
   concerns_sub.concerns, 
   concerns_sub.comments, 
   main_input_table.ID, 
   main_input_table.ID, 
   main_input_table.ID, 
   main_input_table.ID, 
   *
FROM (main_input_table INNER JOIN vessel_positives_table ON main_input_table.ID = vessel_positives_table.Number) 
INNER JOIN concerns_sub ON main_input_table.ID = concerns_sub.Number;[/

ken

 

[raskew]

Or -

SELECT
    concerns_sub.Number
  , vessel_positives_table.Number
  , main_input_table.ID
  , main_input_table.Date
  , main_input_table.Vessel
  , main_input_table.Visitor
  , main_input_table.Location
  , main_input_table.[Last Fire Drill]
  , main_input_table.[Last MOB Drill]
  , main_input_table.[Last AS Drill]
  , main_input_table.[Last JSA]
  , main_input_table.[Last JSA Date]
  , vessel_positives_table.positives
  , vessel_positives_table.comments
  , concerns_sub.concerns
  , concerns_sub.comments
  , main_input_table.ID
  , main_input_table.ID
  , main_input_table.ID
  , main_input_table.ID
  , *
FROM
   (main_input_table 
INNER JOIN
   vessel_positives_table 
ON
   main_input_table.ID = vessel_positives_table.Number) 
INNER JOIN
   concerns_sub 
ON
   main_input_table.ID = concerns_sub.Number;

Bob

 

[KenHigg]

Yes, isn't that nice - errors can almost jump out at you!


ken
(Note to self: Try to use Bob's example)

 

[w1dge]

I posted a "issue" I was having with a query a few days back. To all who helped me, thank you - but, nothing worked. This is what I have. I have three tables that I need to pull info from. These tables get thier respective information from a main form and 2 subforms. I have relationships built and the material is there. As of this time, I have only 1 record in the tables.

I built a query via the wizard and all of the relationships showed up and everything seemed good. When I ran the query, I get the 1 record three times. Basically a loop of info. Someone recommended taking out an astrik that is in the string but that does not help as it screws up the query.

Please see the SQL of the query below.

SELECT concerns_sub.Number, vessel_positives_table.Number, main_input_table.ID, main_input_table.Date, main_input_table.Vessel, main_input_table.Visitor, main_input_table.Location, main_input_table.[Last Fire Drill], main_input_table.[Last MOB Drill], main_input_table.[Last AS Drill], main_input_table.[Last JSA], main_input_table.[Last JSA Date], vessel_positives_table.positives, vessel_positives_table.comments, concerns_sub.concerns, concerns_sub.comments, main_input_table.ID, main_input_table.ID, main_input_table.ID, main_input_table.ID, *
FROM (main_input_table INNER JOIN vessel_positives_table ON main_input_table.ID = vessel_positives_table.Number) INNER JOIN concerns_sub ON main_input_table.ID = concerns_sub.Number;

I cannot figure this one out. A while back, I had basically the same problem but I cannot remember how I fixed it!?!?!?!?!?!

I'm guessing that you only want one instance of the data returned? If this is the case, use DISTINCT in your query i.e. SELECT DISTINCT .....


Hope this helps

 

[The_Doc_Man]

If you do a JOIN of table A with table B where table B is a many/one child of table A and the query happens to not include the field that would make table B unique, it would APPEAR as though you got the same record 3 times. What you REALLY got is three different records but you didn't ask for the information that makes them unique. It was there but you just didn't ask the right question.

 

[ajetrumpet]

Someone recommended taking out an astrik that is in the string but that does not help as it screws up the query.

Track,

I suggested that, as you were using "Distinct" AND "*" in your SELECT statement. These two do not go together AT ALL.

Anyway, if you still haven't solved this, maybe there is other relevant information you are not aware of. It wouldn't hurt to post a file here.