How do I use the results of a cross tab to extract data from a table

[JohnB]

I have 2 tables as follows;

Shipments .................Invoices

SId (PK)......................IId
LoadNumber ................S_SId
CargoId ......................InvoiceType (10 Types)
ContractId ..................InvoiceSubType (4 Sub Types)
Etc ............................DateReceived
.................................Amount
.................................PaymentRef
................................ Etc

They are linked 1 to Many on SID ------ S_SId
With 10 Types of Invoice and 4 subtypes for each there could be as many as 40 Invoice Type/Sub Type combinations.

I have used a cross tab query and a select query called InvoicesExpected to establish which invoices have arrived (shown in the results as the "SI_ID") and which are not required (N/A) or have not been received yet (Date Due). I now have a result table which looks like this;

SId ........Type1_Sub1 ........Type1_Sub2 .............Type2_Sub1 ..........Type2_Sub2
1 ............1 ..........................6 ................................05/08/09 .................N/A
2 ............N/A......................8.................................N/A .........................06/07/09
3.............7..........................10................................11............................08/08/09

My aim is to produce a form called “Compliance” which tells the user for each shipment;

1. Each invoices due date. (If the invoice is expected)
2. All the data in the Shipment Invoices table. (If the invoice has arrived)
3. N/A. (If the invoice is not expected.)
4. Missing (If the due date has passed and there is no SI_ID.)

The form would look like this;

SId # 1 ..........Due ..........Date Received ..........Amount ...........PaymentRef
Type1_Sub1...01/01/10.....01/01/10 ....................$ 1,000,000 ........... 089
Type1_Sub2...05/01/10 .....MISSING ........................ - ...................... -
Type2_Sub1...05/08/09......04/08/09...................$ 3,456....................056
Type2_Sub2...N/A.............N/A............................N/A.........................N/A

I guess the final consideration is that I need to be able to split the database and I would like to be able to update the fields if possible.

I have previously tried union queries but the number of links exceeded the number jet can handle when I split the DB (it worked well before I split it). Reading around that issue suggested keeping things like Dlookup to a minimum so I guess that may not be a solution here.

I am really stuck on this one, any help would be most appreciated.

 

[DCrake]

You seem to be repeating your posts again....

Looking at your data, again, it appears to me to get the third layout all you need is a single query that groups by type by sub type with the remaining fields as expressions. Have you got a sample mdb you can post with some relevant data in it.

David

 

[JohnB]

David,

Thanks, I posted this in the queries forum as it is now definately a query issue. My first post was I thought an issue with the tables. Having used the Cross tab solution you and Atomic Shrimp proposed, I am now stumped again.

The Mdb is currently 5740Kb which is too large to post. I will try and cut some of it down and post it.
JB

 

[JohnB]

Here is the cut down DB with My queries are the ones Marked TestCrossTab & TestCrossTabPlusExpected.

JB

 

[DCrake]

Take a look at the query New Select DC and in particular the first invoice date. See how it uses the function Test1() This can be found in module modtest. Hwo it works is described there. Don't know if this is what you want but it appears to me that is does.

May have misunderstood your question but give it a try.

David

 

[JohnB]

David,

Thanks for your response. Looking at the query and the module, I'm not sure that it is achieving what I am trying to do. It seems to draw only on the invoices that have been presented and entered in the "shipment invoices" table.

My objective was to display the status of all the records in that table and, most importantly, all the records that have not yet been entered.

In order to achieve this I developed the "invoices expected query" which shows which invoices might be due (by comparing factors in the the 6 contract tables to the shipment data itself).

My idea was that the comparison of the "invoices expected" query and the "shipment invoices table" I should be able to determine for each shipment;

Shipment 1
Initial Freight ....Due.....Received....Value....Payment Ref
Final Freight ....Due.....Not Received...........................
Initial Bauxite....N/A

Shipment 2..... etc

If I missing something, please could you let me know?
JB