Table structure problem (1 Viewer)

[Derevon]

Hi,

I am trying to make a database that will keep track of invoice backlogs for different locations within a company. Each day a report file is imported into an Access database (2010), and a query will display all locations with the backlog count of invoices for each of them. Something like:

Location     Backlog
00001        84
00002        53
00003        26

Now I would like to be able to store historical data in a new table on a daily basis so that it will be possible to see how the backlogs have fluctuated over time for a given location, but I am not sure how I could do this. I was thinking I could make a table with one row for every date and one column for every location, but then I am not sure how I would populate this table based on the query that shows the results based on the most recently imported report file. Am I on the right track here, or is there a better way to do this? Any help is appreciated.

 

[jdraw]

I really question what you are doing. You are getting data via a Report, and you are going to input that data into yet another database/electronic file.

What is the issue that you are trying to "rassle to the ground in business terms"? The backlog? The History?

Who is being hurt most by the backlog? What do they see as the issue/importance/severity?

Why is there a backlog? Is highlighting the historical change going to solve the issue?

I'm not trying to dissuade you. I just want you to see the issue from a little different view. It may be an urgent need, and if so, then you can involve the principals, initiate a project and get some resources to "cure" an ill.

Good luck.

 

[recyan]

@Derevon,
Is this what you are trying to indicate ?

Location Backlog RecordedOnDate
00001 84 2/22/2014
00002 53 2/22/2014
00003 26 2/22/2014
00001 85 2/23/2014
00002 48 2/23/2014
00003 25 2/23/2014

or
Am I totally mis-reading your problem ?

Thanks

 

[Derevon]

Thank you recyan. You understood correctly. Now I am feeling very stupid. :banghead: Note to self for future reference: don't try to work with Access 2-3am at night.

I don't know what happened really, but somehow I got the idea that the date field should be unique for some strange reason, and that must have completely messed up my thinking (or lack thereof) as I had the idea that only one line should be added to the historical table per day. I will simply make a composite primary key consisting of the date and the location number and add a new line to the table for each location on a daily basis when a new report is imported.

Jdraw: Thanks for taking the time to answer. The idea is that the manager is supposed to see for any location how the backlog situation looks over time, trends, etc, so it should suffice with a table containing only 3 fields: date, location and backlog count for the given date/location. Then I will simply prepare some line chart or similar based on the data in the table so that the trend for any location (or all combined) can be easily discerned.

 

[recyan]

Now I am feeling very stupid. :banghead:
.

Don't feel bad. If I am not wrong, all of us, whatever level we are, do it quite often. In fact one of the reason, I like forums is, I can be as stupid as I want & people understand.

Note to self for future reference: don't try to work with Access 2-3am at night.
.

At 2-3 am at night, I am dead to the world, forget working. Wish I could go back to those old days.

Thanks

 

[jdraw]

@derevon
If it's what the manager wants, then go for it. I was just suggesting that if the manager needs a different report, why can't he get it from the current system. I'm not against your project, I just see it as a little off target. Entering data from a report into a database is reprocessing old data -in my view.

@recyan
Welcome back - haven't seen you for a long time. Hope all is well.

 

[recyan]

@recyan
Welcome back - haven't seen you for a long time. Hope all is well.

Thanks for the welcome back.
Am tied up with solving problems that have come from all sides, hence the absence.
Hope I get them sorted out soon & be back interacting with you guys / gals.
Appreciate you remembering me, inspite of my long absence & also appreciate your concern for my well-being.

Thanks & Warm regards.