How to set criteria in a query

[ausmoran]

I have a table with member records which include a specialization code. Since some members have multiple specialties, they have multiple records. I am attempting to query the table and extract a single member record which will include all of their specialty codes (from multiple records). Is there an easy way to accomplish this?

Thanks,
Austin Moran

 

[Chris RR]

The correct way to do it is to break your single table into two separate tables.

One would be the member master, and have the member ID and stuff like name, gender (items which the member hopefully only has one of).

The second table would have a key that contains two fields, the member ID and probably an autonumber field ("specialty_id" or some such name), and then a field for the specialization code. You can then have any number (zero to way more than you need...) of specializations per member.

Join the two tables together, using the member ID, and you can easily get the query results you are looking for.

This whole process is called data normalization, and there have been many books written on it. It's not an "easy" answer, but its the way professional system designers work.

 

[ausmoran]

Thanks, Chris for your reply. I think I failed to clearly explain the problem that I am facing. I actually do have two tables (one for the member data and the other lists specialties...they are connected via a member number). I want to be able to generate a dynaset where I have ONE member records that includes all of that person's specialties. The way I do it now, I end up with multiple copies of the same persons record to accomodate each specialty.

Thanks again!
Austin

 

[David R]

Have you looked at a crosstab query?