Normalisation has made things to hard to query

[JohnB]

Hi,


I have been working on this since last October (on and off), without success. I have two 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

I need to develop a query that will provide me with a table that looks like this (for up to 40 types/subtypes of Invoice);

SId ............Type1_Sub1 ..........Type1_Sub2 .........Type2_Sub1 ......Type2_Sub2
1 .................IId 7 ........................-- ....................... ...IId 5 ...................-.-
2 .................IId 8 .........................IId 1 ................... - ..........................IId 3

The results in this table will be compared to a complex select query called “InvoicesExpected” which draws data from 7 other queries/tables to establish when and indeed if each Type/SubType of Invoice is Due, The results from the “InvoiceExpected” query look like this;

SId ........Type1_Sub1 ........Type1_Sub2 .............Type2_Sub1 ..........Type2_Sub2
1 ............01/01/10 ..............05/01/10 ....................05/08/09 .................N/A
2 ............N/A......................09/05/10 ....................N/A .........................06/07/09

The aim is to produce a form called “Compliance” which tells the user which invoices are due when and most importantly which have not yet arrived (and as such have no record in the “Invoices” table).

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 have achieved this using Union Queries, but when I split the database to run in a multiuser environment the number of links exceeded the number Jet could handle.

Another issue is that ideally the product should be editable by the user, without having to bring up pop ups to change/add data etc. This was required when I used the union query solution.

Can anyone suggest a way I can link the tables or indeed restructure them which would allow me to produce the desired output? I do not want to add 40 tables reflecting each Type/SubType linked to the Shipments Table because this will make things too complex.

Any assistance would be much appreciated.

 

[gemma-the-husky]

normalisation doesnt make things hard to query

normalisation makes things EASY/EASIER to query

problems with querying would tend to point to incorrect normalisation.

----------
it is hard to understand exactly what you have - so can you explain in more detail how an invoice and shipment are related (are these goods in or out)

in general you would expect to see all of the following

orders
order lines

shipments
shipment lines

invoices
invoice lines

since each order/shipment/invoice may comprise a number of lines (products)
an order may be fulfilled by multiple shipments
an invoice may include charges for a number of shipment lines, each of which COULD be fairly random - even if 99% of the time they are not.

you almost certainly will then need other tables for customers/suppliers/products/employees/price rates/shipping methods/ etc, depending on how far you take this.

your system needs to inter-relate all these items.

therefore if you only have two tables, it looks like your analysis is suspect - the fact that you have these invoice types and sub types is an indication that this may be the case. If a subtype is a refinement analysis of an invoice type, then you would certainly only expect to store the subanalysis in the invoice. And in any event, it is much more likely that your invoice analysis is ACTUALLY a function of something else - eg the product itself, or the customer, and nothing to do with the INVOICE per se

 

[JohnB]

Gemma-The -Husky,

Thanks for your response.

This particular Db is V2 of one we already have in place. V1 held a huge amount of data in a few large un-normalised table. Whilst this worked reasonably well it had was a bit too slow.

V2 brings together a wide variety of factors relating to individual shipments of raw materials from various ports around the world to our own port.

The key functions are checking the freight and raw material invoices are correct by comparing tomnnages lifted, to the freight and materiel contracts, and the times of certain events in the ports. This functionality works very well and draws from 31 tables and 100+ queries.

The structure allows a shipment to draw on the 6 different contracts needed to run each shipment, each of which has payment rates and terms which are held in separate tables. The query "invoice expected" draws on these to tell me when an invoice is due (if at all).

The section I am currently working on provides a compliance function which checks that all the invoices and paperwork are being processed correctly. As such all I need to know is when an invoice is expected, when it arrived, the amount on it, how much we actually paid, and the payment Reference.

The table "Invoices" holds only the compliance information shown above, this is common to all the invoices we receive and the Type /Subtype fields allows me to identify which invoice is being represented by the values in the remaining 6 fields.

I am reasonably confident that the structure is sound and as the objective in this instance is one of compliance ie. Have we received and processed invoices correctly? I am certain that the data is not a function of something else, as is the case with other sections of the database.

 

[Atomic Shrimp]

Hi,


I have been working on this since last October (on and off), without success. I have two 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

I need to develop a query that will provide me with a table that looks like this (for up to 40 types/subtypes of Invoice);

SId ............Type1_Sub1 ..........Type1_Sub2 .........Type2_Sub1 ......Type2_Sub2
1 .................IId 7 ........................-- ....................... ...IId 5 ...................-.-
2 .................IId 8 .........................IId 1 ................... - ..........................IId 3

This bit looks like it might be achievable with a crosstab query, using an expression in the column header such as:

"Type" & InvoiceType & "_Sub" & InvoiceSubType

 

[JohnB]

Thanks Atomic Shrimp,

I have always had problems with cross tabs before and had not considered using one for this task, but it looks like it has worked.

I hope to have the form up and running by this evening.

Many thanks for your kind assistance.

JB

 

[Atomic Shrimp]

One thing you do need to be slightly wary of, using crosstabs, is that if there is no data for one particular combination of type and subtype, the whole column will be missing from the query results - this makes it a bit difficult to set up reports based on them, if the underlying data is ever-changing.

 

[JohnB]

Atomic Shrimp,

Thanks for the warning, I will have to bild in a work around for that because I suspect it will happen.

That said the compliance query I'm building seems to be doing the trick at the moment.

Once again many thanks.

 

[DCrake]

When you first brought this question to the attention of the forum, under a different thread, my suggestion was to use a cross tab query. It seems to me that it has taken you a long time to come to this conclusion.

David

 

[JohnB]

David,

Sorry I did not understand your message which said;

"Is there a particular need to have the data presented vertically? I would have thought that doing horizontally would make the task alot easier.
Query grouped by sid by type with the other fields as expressions"

Unfortunately as my original post got all the tables screwed up I thought you were refering to the fact that the tables hade come out as a column. That is why I took the post down and re-did it.

As stated above, I have not used cross tabs before I did not understand that what you had written referred to crosstabs. I wish I had.

Thanks for your assistance though.

JB

 

[HiTechCoach]

JB,

You may also want to check out this example:

Dynamic Report based on a Crosstab query

 

[sportsguy]

instead of a cross tab query, which i never use in a report,
use a regular select query with an Iif statement to select the value for the specific column.

select Iif([Type1]&"_"&[Sub1]="Type1_Sub1",[Value],0) As A, Iif([FieldName]="Type1_Sub2,[Value],0) As B, etc

then the report is fixed, the query is fixed, and the query only makes one pass through the tables.

If the columns are variable dates, the same query applies, with a count back or countup Iif statement. cross tab queries and reports are OK if the columns are fixed permanently, and don't change. . .

sportsguy

 

[JohnB]

Sports Guy and HiTechCoach,

Many thanks for your input some usefull stuff here.

JB