ODBC tables use three fields for PK (but I don't think they're really PK)

[allmylists]

I am trying to make a report from data obtained via ODBC from our batch-based construction accounting software, that I can't make within the software. I'm using MSAccess 2003 in 2000 mode.

My objective is a report that lists: inventory item number; inventory description; invoice date; invoice number; tonnage billed; dollar amount billed.

There are 4 tables I need to get the data from. I've only listed the actual table names and the field names I need to use. There are a gazillion more fields in each table that I won't be using, but am leaving alone in order to to make append queries work (to add new records). Gltran and Indet are the tables I need to join:

-gltran (this table holds some invoice information)
trandate
docid (invoice number)

-taxtran (this table holds invoice amounts, plus sales tax info)
taxable
nontaxable (both are numbers/amounts)
relates to gltran somehow, one-to-one

-indet (this table holds inventory info, tonnage sold)
catid (catalog id from catalog table)
qtybooked

-catalog (inventory name and number only)
id (inventory item number)
name (inventory item description
relates to indet on id (catid), one-to-many

Each table also has three columns that function within the software (near as I can tell) as the PKs:

1. setid
2. batchid
3. id

-Setid is a three-digit number that increments based on batchid (currently on 004).
-Batchid is kind of like the actual batch number; it starts at 000, increments to 999, then starts all over (and at that time causes setid to increment (by one)).
-Id is the transaction number within the batch (i.e., a batch with 10 transactions/records will usually have 10 individual id's assigned); these don't affect incrementation for setid or batchid, but are necessary to identify an individual transaction. This is a four-digit number.
-Example: Batch 225 has 2 transactions/records. Batch has identifier of 004225. Record One has an identifier of 0042250001; Record Two has an identifier of 0042250002.
-Each full set of identifiers is not necessarily sequential within my data because I am only working with certain types of records (accounts receivable: yes; accounts payable: no)
-Each invoice has an entry in each table (except catalog), using the same identifier in each table
-Each record has a unique set of these numbers.

While the actual relationship between gltran and indet is one-to-one, I have been unable to get them merged or combined or anything. (taxtran and catalog seem to be along for the ride, connecting just fine with their respective tables so not worried about them so much).

I made a few delete queries in order to eliminate useless data (previous year, wrong General Ledger type, etc.) and get the data to a manageable size. This results in the tables having an inequal number of records, as there are no similar fields to filter by (the indet table does not have a date field, etc.). So, while the records exist one-to-one, it looks like many-to-one if you just happen to glance at the tables. Maybe I should skip this step?

From what I understand, for the three combined fields to be a PK they need to be non-duplicate individually, which is not the case here. Indexes don't seem to help either, though I'm not sure I have a good grasp of how they function.

Things That Don't Work (unless I did them utterly wrong):
-Make queries
-Append queries
-Union/Join queries
-Querying to make the three IDs into a calculated field and use that in a further query to match (thought I left them named Expr1, which I've since learned may not be a good thing)
-SQL query along the lines of Select X and Y and Z from A Union Select B and C from D Into E (or something like that, just in case the Access automatic Union wasn't working right)
-Autonumber (because then I think I'd have to do a lookup in one of the tables, and that would mean record by record, wouldn't it?)

What I keep visualizing is somehow sticking just the fields I need into one table all together, with those three so-called PK fields determining which record the data falls into.

Is this even possible, or am I totally off my rocker? If possible, and if it's not horrendously complicated, I just need to know what to do and I could probably figure out how.


Does this make any sense at all? Is anyone still reading? Cripes, this got long...

Thanks!

 

[The_Doc_Man]

From what I understand, for the three combined fields to be a PK they need to be non-duplicate individually

This statement is key to understanding why you are having trouble. Your understanding is flat wrong.

Consider this counter-example. Today is 12-Sep-2010. We can have 365 days for which that same year applies. We can have 30 days for which that same month applies in any given year. And the 12th of the month occurs once for every month in the modern era. So individually, none of those elements is unique. Yet when you combine them, they make a perfectly good selector for one and only one day of the modern era, which means they make a good compound primary key. There is where you are falling down. A COMPOUND primary key's elements can be duplicated many times. It is that the combination must be unique. My example shows exactly how that works.

Go back and look at that in light of my example. Reconsider the elements that gave you trouble before and see if you can't make better sense of it. I'd belabor the point but it is up to YOU for the understanding. Come back once you have thought about this. Maybe you will have a more directed question after you think about it.

 

[gemma-the-husky]

just to clarify

for each inventory item, you want a list of the invoices on which it has been sold?

so in general - the invoice table is insufficient - an invoice can have multiple lines, only some of which will apply to you - you need the appropriate invoice lines table to do what you want to do.

 

[allmylists]

just to clarify

for each inventory item, you want a list of the invoices on which it has been sold?

Yes

so in general - the invoice table is insufficient - an invoice can have multiple lines, only some of which will apply to you - you need the appropriate invoice lines table to do what you want to do.

No - this might actually be an easy situation - each invoice only ever has one line.

 

[allmylists]

A COMPOUND primary key's elements can be duplicated many times. It is that the combination must be unique.

See, this is what I thought at first but the compound PK's I set up didn't work, so I did more research on the compound PK thing, and got from somewhere that they each had to be individually unique (clearly, wrong information).

Your explanation and example make perfect sense, so I'm probably doing something wrong somewhere else. Back to the drawing board.