Getting A Distinct Row

[rts]

Hello,



I still haven't been able to quite get this: I have a table Submittals that contains basic information about the Submittal (To, From, Subject,etc). I then have 5 other tables for the different types of submittals (Materials, Plans, etc). Each of these tables is joined to the Submittals table using Submittal number as the foreign key. I want to create a query that will show just the One Submittal and any information from related tables that have that Submittal Number as the foreign key. If I Create Submittal #1 then add Materials to it, it shows up on the same row in the query. Once I add fields from any other tables, it starts duplicating the information so my one submittal would appear twice. I get why it's doing it, but how do I make it just show one row for all records with the same Submittal Number. I've tried Distinct and Sum, but I'm not very knowledgable about how to use them to get what I want.

[orange]

I can't fully visualize your set up but it sounds like you have Submittals and a Submittal can be 1 of 5 possible SubmittalTypes. This would seem to involved 2 tables

tblSubmittal
submittalID PK
submittalTypeId FK to tblSubmittalType
To
From
...other Submittal specific info


tblSubmittalType
SubmittalTypeId PK
SubmittalTypeName
SubmittalTypeDesc

[rts]

That is what I would like to do, but each type of Submittal has different Fields. So there would be a Submittal like the first Table you have. The Materials Table would have different fields than the Plans Table however. I think I can make it work with your suggestion, but I wish I could use separate tables for the Submittal types. Thanks for your help!

[rts]

The problem with that is that I could have 1 Submittal with several different types of Submittal Types. So I want to show on my report:

Submittal #1

Plans Submittal
Materials Submittal
etc..

Linking it the other way, limits my submittal to one Submittal Type per submission. Does that make sense?

[orange]

Please describe the different submittals and the related fields. Sounds like submittals is not the real entity you are dealing with.

Maybe should step back and tell us exactly what you are trying to do.