SQL hangup. Help needed...

J

Jim

I have two tables that are joined together, and I want to perform an INNER
JOIN kind of procedure based on a 1-to-many relationship. I have the join
working, and even with using the DISTINCT keyword I am returning four sets of
eight records.

If I was coding this application using something like VB 6 or VB.net I could
iterate through the result set using a control break and everything would be
fine and dandy. Is there a way to do something like this in Access? I
eventually want to use this result set to populate fields on a form and/or
report. Any ideas?

Thank you in advance.
 
A

Albert D.Kallal

using a control break

Hum, I have no idea what you mean by the above. Are you referring to
breaking on data in the report generator? (if yes, then you don't need code
to do that).

If you mean something language specific..then I don't know what the above
means. However, ms-access shares, and uses the same code compiler as VB6.
The language syntax is in fact the same as VB6. The ONLY difference you have
here is the object model for forms (they are VERY different).

So, just write your code to work through the data as you always would (just
write the code to process the data, as it will be the same as it was in
VB6). The only question here is AFTER you are done this processing, where
you want the data to go to? Perhaps just send the data to a temp table...and
let a report run on it for example - however, for reports, and one-to many
data reporting, you can (and often should) use sub-reports. That means you
base the report on the ONE main table, and for child data..you simply drop
in child reports (sub-reports) to show the many side (this makes one to many
reports child play..even for non programmers).

As for returning four sets of data...once again, I doubt if you use Oracle,
sql server, or JET you would get any different results.

Likely, your relationships are set wrong, or it is not clear what the
results you expect in terms of that query.
 
Top