Table Comparison

[jesusoneez]

I have tblOrders which contains order data for the past few years. I've been trying several methods to create a report to compare the order data. The method I'm currently trying involves three make table queries that creates a table containing order data for a given AccountID in the format tbl2002, tbl2003, tbl2004. I've then tried to create the report as follows.

ReportHeader
==========
AccountID
RepName

Details
=====

ReportFooter
=========
2002
=sum([tbl2002_OrderQty])
=sum([tbl2002_OrderValue])
2003
=sum([tbl2003_OrderQty])
=sum([tbl2003_OrderValue])
2004
=sum([tbl2004OrderQty])
=sum([tbl2004OrderValue])

This report takes an age to process and then gives me stupidly large figures when it finishes. The report is using tbl2002, tbl2003 and tbl2004 for its' record source. There are no relational links between these three tables. The tables all contain;

JobNoID
OrderDate
RunDate
AccountID
CustomerName
ProductNoID
RepAreaID
RepName
MachineRoute
OrderValue
OrderQuantity
SqrMtrs
Weight
SpecificationNo

Any ideas why it's giving rubbish figures?

Thanks,

Steve

 

[Rich]

Why are you using separate tables for each year?

 

[dan-cat]

Braintree? I live just outside Haverhill we're almost neighbours

Yes as rich says no need to create seperate tables for each year.
Just put a calculated field within the reports record source calculating the year for each order date ie Yr: Year([OrderDate]).
You can then group your report on that field

I've attached a sample to get you going.

 

[jesusoneez]

You, my near neighbour, are an absolute star.

I think my problem is that I tend to make things more complicated than they should be.

Thanks again!

Steve

 

[dan-cat]

We suffolk folk like to keep things simple oooaaaaar!

 

[jesusoneez]

Well there ya go! There's my problem...I'm just over the Suffolk border and into Essex (I live in Great Yeldham now, which is about ten miles closer to you than Braintree).

However, I am going to annoy you...

The info you gave me was fab and I'm now getting the data I want...but...

...if I want it more formatted so the years go across the top, with OrderQty, OrderValue et al down the left hand side and the summed up values in between spreadsheet stylee, would it be much of a change?

Promise I won't bother you again if we get this result!

Ste

 

[dan-cat]

Will this do?

Getting the years to list across the top is a little more complicated but will take a closer look for you. The summing up is all done though.

P.S If you've got any work going at your firm - I'm currently in between posts

 

[jesusoneez]

Thanks for offering to take a closer look at this. It's very much appreciated.

Don't know if you realise the .mdb in your previous post is exactly the same as the original .mdb you sent me!

EDIT - Sorry...no it's not. That's not bad. Be interesting to see what you come up with after your closer look.

Thanks again,

Steve

 

[dan-cat]

Try this

 

[jesusoneez]

See my edited post above...the .mdb you sent me before was different after all...

Ste

 

[dan-cat]

Here you go - I'm sure there are more elegant ways of doing it - works though.

 

[jesusoneez]

Once again, I thank you. That's spot on, and will work with my database with no problems. One thing though...what does Module1 do?

Cheers,

Steve

 

[dan-cat]

It doesn't bear any relevance to the report.

It was just me playing with the idea of creating a new table called 'tblTemp' and then creating seperate fields called '2002',2003' etc etc depending on the values of the field Yr within qryOrders. Then appending data into those fields.

Then I just decided I was going to go down the route of using the lookup fields within the report (a lot simpler)

 

[jesusoneez]

I hate to hassle you again but I'm still having a problem. The query works fine (giving me totals for each year), but when running the report, I get #error for all of the calculations, and it's not grouping everything together like yours, so I get four lots of the errors (1 for each year, 2001-2004), and I can't see where the problem is. The syntax of my DLookUp is the same as yours. The only difference is that in the query I ask for user input to ascertain a product type and an account number to query on.

I don't know whether it's having a problem with these because they are on GroupBy whereas the others are obviously on Sum...

I've enclosed it just in case you want to have a look...you won't be able to get at any data as it's linked to a backend, but you should be able to see the structure OK...

I've scrutinised yours, scutinised mine and can't see why it'd do this.

You have permission to slap me.

Steve