Append Query the right option? (1 Viewer)

[Nevsky78]

Hi all,

I have an "Order Book" query which I run off a Linux database every week.

I need to track the orders (and changes) week by week. Customers can be added every week and order quantities can either grow or reduce.

My question is - does anyone know how I can run a query that automatically adds another column each week, reflecting any customer additions or order changes?

I have attached a sample database showing the kind of thing I'm doing.

tblOrders_1 reflects the "Order Book" in Week 1
tblOrders_2 reflects the "Order Book" in Week 2

Obviously there will just be the one "Order Book" - the 2 versions are there to show how it can change from 1 week to the next

The tblRunningTotal gives an example of my "ideal" solution.

Does anyone have any advice they can give me on how I can achieve this?

I've tried the DSum suggestions on the Microsoft website to no avail.

Many Thanks,
Nick

 

[jdraw]

What exactly do you want to accomplish?

I would recommend a Transaction table including a Date field to show when the 'effective date" of the transactions.
You don't necessarily need a separate table for each set of transactions.
You definitely don't need a new column when "summing".

Tell us more of your "situation".

 

[Nevsky78]

Hi jraw,

Thanks for getting back to me.

Effectively I want a archive that shows the weekly status of the order book.

For example, in Week 1 "customera" will have ordered 100 units.
In Week 2, customera will have changed their order to 80 units. I need to be able to 'track' that change.

Customer Name Week 1 Week 2
customera 100 80

There will also be additional customers added at random intervals.

Once 2010 is archived, I can compare Week 1 2010 with Week 1 2011 for customera, and the % change therein for trend analysis.

I therefore have to have a separate field for each week. Microsoft's DSum solution will only reflect the Order Book FOR Week 2, rather than TOTAL AS AT Week 2.

Does that help explain the background to what I'me trying to achieve?

Regards,
Nick

 

[jdraw]

What are the processing steps to get data from Linux into Access?
Do you store the "raw" transactions?
How do Customers get identified?
Did you consider adding a date field to the transactions?

How do you account for any typos?

 

[Nevsky78]

Hiya,

What are the processing steps to get data from Linux into Access?
-I run a query on Linux tables connected by ODBC. The code is fairly massive but I can paste it in if it would help? I then run a Make Table to convert it into a Table
Do you store the "raw" transactions?
-Yes, in the resulting Table from the Make Table query
How do Customers get identified?
-There is a Linux code for a customer, and I normalise the customer name with a Customer Name table
Did you consider adding a date field to the transactions?
Yes - the attached database is a very simplified example of what I'm trying to achieve. The actual data has order date, date-req, date invoiced and so on

The problem is that my current database is waaaay too big to attach here so I thought I'd post a simplified version!

My problem with using a date from the made Table is that is doesn't reflect all orders at a specified point. An order can go on, on the 1st February for customera, with the following details:

Order Number: 10522
Order Line: 1
Order Date: 01/02/2011
Quantity: 100

However, if that quantity is changed on the 1st March, for example, there is no record that on the 1st February the original quantity was 100.

(Order as at 01/03/2011)
Order Number: 10522
Order Line: 1
Order Date: 01/02/2011
Quantity: 80

The Linux tables do not have any "amended date" fields so I cannot use them.

I may just have to keep going with keeping a fixed table going in Excel, but I don't really like doing that.

Nick

 

[Nevsky78]

I've attached a small sample of the actual normalised Raw Data. The Table shows all of the pertinent fields.