Duplicate Entries 'begone'...grrrr (1 Viewer)

[Randomblink]

Ok, I have ONE table that is being queried...
It has three IMPORTANT fields...
The table is based on projects...
Field#1) PROJECT #
Field#2) DRAWING
Field#3) PAGE
There are Projects with no Project entries.
There are Drawings with no Project entries.
What happens is...
I run the query, and I may get Project# 666 (he he) that has 30 different drawings, that are on two different pages. (ie 15 drawings per page). Or I may have 2 different drawings that are on 15 different pages (they are split up sometimes). Or I may have a list of drawings that have no project number!

What I want to do is...
Run the Query...
And have ONLY 1 of each Project list.
If there is no Project Number...
Then have ONLY 1 of each Drawing list...

Is this possible? Even telling me how to list ONLY 1 of each Project would save my sanity...thank you all...and to all a good night...it's 5pm where I am! ugh!

 

[Pat Hartman]

Your problem is really one of table design. You have a 1-to-many-to-many relationship that you implemented with only a single table. It really should have been three tables.

Try something like the following and see if it gets you what you need:

Select [Project #], Min(Drawing) As MinDrawing
From YourTable
Group by [Project #];

As an aside, you should not create names that include spaces or special characters. [Project #] should be ProjectNum.

 

[Randomblink]

Unfortunately, the database is created by a group that hates Access. They create the database because we refuse to use their database programs (as far as I can tell) so they create it the way they do just to give us the information we use...heck, we get a new database every week...a BRAND NEW database that the only difference from last weeks would be the NEW data inputted...ugh! Politics...
So I am forced to use what they created.

The problem is, the person needing the information is needing it to come up in a query so they can run a report that shows ONLY ONE PROJECT NUMBER...even if that project number has multiple pages or drawings etc...So we need the QUERY to ONLY show a single PROJECT at a time, and instead, if there are X pages or X drawings there are X records...and we can't get it to go about it like we need it. The person doing the report says the DRAWINGS and PAGES fields need to be on the report for those PROJECTS that have no PROJECT number, but only have a page number or a drawing number...

So if someone could help me out great...if not this driving me muey loco...
Thanks in advance...