Question Basic Newbie Questions

[SteveF]

I haven't re-designed anything, nothing's changed so I still have the same un-resolved problem.

Maybe I have failed to properly ask the original question? If so, my apologies and I will try again.

I have multiple customers. What I do is calculate product usage through their business and then report that usage in various ways. We don't just do this once though, we do it once a month, so I need to be able to refer back to the data from last time.

I either need a table per customer (to my way of thinking) to store their products and to make a new table for each new customer, or I need to push that data out and store it in a format that Access can retrieve when I go back to that same customer.

If I push the data out, I suspect that a CSV file is the answer along with a data folder for each customer. My (no doubt faulty Excel based) logic tells me that a table-per-customer setup would work, but I really don't know and that's what I'm doing here asking questions.

If this were a simple product storage / usage problem it would not be a big deal, but the multiple customer aspect is the bit I don't get.

 

[namliam]

How do you calculate the Product usage?? And that data is allways within your database right?? Or is that data overwriten in the DB thus not available next month?

 

[SteveF]

How do you calculate the Product usage?? And that data is allways within your database right?? Or is that data overwriten in the DB thus not available next month?

The Product Usage comes from a query on the Products table, and that data is always there yes. I've not progressed from there, but if I were to do the job again next monthe then it would just be overwritten yes.

There is nothing being moved out of the database at the moment, only reports.

 

[namliam]

What is this like sales or something?

Let me ask you this question:
If I report today for your figures over June will it be different than that what it was on July 1st?

 

[SteveF]

Yes and yes. It's sales and it changes all the time. We are just taking a 'snapshot' and reporting the consumption for a given period.

What will happen is that we take last times data, move the closing stock figure into the opening stock field, add purchases and closing stock figures again and then run our reports.

 

[namliam]

Yes and yes.

OK now we are getting somewhere (sorry for beeing a pain)

So we have starting stock, Sales and Purchases and end stock.... and you want to keep this to compare the next months sales and purchases against?
Right?

Why overwrite tho? Usually you keep this kind of data inside your database and only calculate your end stock for checking purposes to fight theft and such. Why move end balance to starting balance each month?

I think in this case you should make a table that would contain these figures along with a date stamp for when they were generate

 

[SteveF]

OK now we are getting somewhere (sorry for beeing a pain)

So we have starting stock, Sales and Purchases and end stock.... and you want to keep this to compare the next months sales and purchases against?
Right?

Why overwrite tho? Usually you keep this kind of data inside your database and only calculate your end stock for checking purposes to fight theft and such. Why move end balance to starting balance each month?

I think in this case you should make a table that would contain these figures along with a date stamp for when they were generate

Yes, we want to be able to compare and report month to month.

We need to keep it, not overwrite it, I'm just not able to do that. The history is important for running totals and overall yields etc. So we need to be able to recall it.

I see immediately that there is a solution there in writing a table for the history which would contain the totals needed for running reports, so that's a brilliant answer for that part of it.

The end balance to starting balance is because we do this every month, so last month's closing stock becomes this month's opening stock. We then add new purchases, count a new closing stock and run our reports again.

The complexity comes from needing to do this many times for many customers.

 

[namliam]

Well doing it many times for one customer or many times for many customers doesnt matter in a database.

As long as you in your history table add the CustomerID you can always split the history per customer.

 

[SteveF]

Brilliant! So if I have a 'JobID' table that can relate to customers that JobID table will re-load the data from any job I choose?

 

[namliam]

More or less depending on your datamodel... but you still need that history table tho...

 

[SteveF]

That's great, I will get busy and test that this evening and see if I can make it work. I'm a million miles away from being live with this, so it's all learning and testing at the moment.

Genuine thanks for your input.

One more question. I'm currently using Access 2003, would it be smart to upgrade to 2007 now or is there no real benefit? I'm a bit concerned about compatibility, though it's not a big deal just now.

 

[namliam]

I am still "stuck" in 2002, 2000 and for some customers even 97

2007 is nothing more but a pipe dream for me at the moment, so I cannot tell you which would be better.

 

[SteveF]

Well, I'm guessing that if it's good enough for you then it will be plenty good enough for me. Thanks