Querying one-to-many relationships

[Kim K]

I have two tables, one table with one primary key and one table with several
keys:

Table 1
NDC GPI
57866660701 1

Table 2
NDC APPL_NO APPL_TYPE_CD
57866660701 074105 2
57866660701 074140 2
57866660701 074207 2
57866660701 074410 2
57866660701 075927 2
57866660701 076494 2

There are a two things I need to do after joining these two tables. One is
to find out if there are any records in the second table with different
values in the "APPL_TYPE_CD" field for one NDC number. The other is to only
return the first record from the second table, not all of them.

Help?

Thanks!

 

[Jeanette Cunningham]

Hi Kim,
The 1st query is easiest.
here is the SQL string
SELECT DISTINCT tbl2.NDC, tbl2.Appl_Type_Cd
FROM tbl2;

--Join both tables on the field called NDC
--in a new query use tbl2
--drag field NDC and field ApplTypeCd onto the query grid
--click in the blank space in the query above the grid
--in the property sheet on the general tab find the property Unique Values
and set it to Yes
that's the easy one.

The 2nd query is more difficult because the table has no way of knowing
which record is the first record for ApplNo for each NDC.
A fairly easy way to help this process of knowing the 1st record is to add
another field to tbl2.
--field called TimeStamp
--set its datatype to DateTime
--set its Default Value to the current date and time by typing = Now() in
the text box opposite Default Value
--save the table
Every time a new record is added to tbl2, the date and time will be
automatically populate the field TimeStamp for that record.

We can easily sort the TimeStamp field for each record to find the earliest.
The 2nd query uses a subquery - a bit more sophisticated than the simple
queries.
Here is the SQL string for the query:

SELECT tbl2.Appl_No
FROM tbl2
WHERE (((tbl2.TimeStamp)=(SELECT Min(tbl2.TimeStamp) AS MinOfTimeStamp
FROM tbl2
WHERE (((tbl2.NDC)="57866660701")))));

If you are using this query from a form, there will need to be a way to
supply the value for NDC to this 2nd query.
Post back if you need help with this.


Jeanette Cunningham