Unanswered: Weird - can't find help anywhere! Duplicate answers
Ok, here's my problem...
(don't know how to simplify this, so here we go)
I have a db that I've created and am using to hold budget info with invoices, purchase req's, orders, etc.
I have several tables that make up my main form to enter info. In order to have another form print out the correct way, I had to put some info in "detail" so it would repeat and I could enter multiple items. (That table is called ITEMS and the fields are PURCHAUTONUMBER, DESCRIPTION, QTY and COST (per item not invoice).
I am trying to pull a report that will show me all records that haven't been assigned a PO# yet. For each record (pulled from the MAIN table) the items actually purchased are recorded in the ITEMS table.
The PURCHAUTONUMBER connects the two tables together.
When I print the report, I get the requested fields (REQDATE, REQNUM, VENDOR, ITEMS, INVAMT). They are all listed in DETAILS on the report.
My problem is, that since technically each line item purchased is a different record (although all show up in the same form to enter them), it lists everything again.... DATE, REQ#, VENDOR, AMT, etc...
What I want is if the PURCHAUTONUMBER is in more than one record in the ITEMS table, then I only want the first ITEMS description (basically the first line item). I just need to get a general jist of what the purchase was for. I don't need 20 lines of detail that are in the db.
I hope I explained myself correctly. Can anyone help me PLEASE?!?!
Oh, wait a second... You probably wanted me to actually help you instead of offering my opinion on whether or not I could do it, right ???
Without knowing what tables you've got, their relationships, and some sample data I'm having to do a bunch of guessing. My first guess is that the PO is assigned at the purchaseRequest level, not at the lineitem. I'm also assuming that there is an itemAutonumber column, just like the purchAutonumber. I get the feeling you are using MS-Access, which would figure into the solution because the Jet database engine that Access uses has some peculiarities in how Jet implements SQL.
If that is the case, then a simple select should get you where you want to be. Let me know how well I guessed, then I'll be happy to take a shot at a solution for you.