Order Intake DB

[slackda]

hi guys,

hope you cane assist me on this.

Im desgining a new DB to take over from several spreadsheets.

So far i have import linked the data direct from our order entry system and have done necessary changes to the data.

so far there are 4 tables Invoiced, invoiced special, backlog and backlog special (not the actual table names but what they contain).
the reason for this is the data for the import come form the same source, so you have to run the report in the master system , then update the access table and then run for each type of report, then you run a amend qry in access to add an identifier to the data, i.e invoiced gets inv identifier and backlog get BL.

These all have time stamps for when they are uploaded, i need to now run another backlog report for today, and compare the older backlog against the newer backlog, less any invoiced values.

with the difference generating an orders recieved value.

my thought was to compare the data using the upload date stamps,i.e
if upload b is greater than upload a then order value b less order value a less invoiced value.

Currently i have all the 4 tables being compiled together in one master table so when i run the newer backlog i will just add to the master table, i presume that i will be able to do the comparison frmo there? or will i need to have two sets of backlog tables one for new back logs and one for old backlogs? and do a comparison of data that way?

Your thoughts etc be most appreiciated.

oh if you think that i should post this else where on the site please let me know.

[speakers_86]

Please understand that Access and Excel are two completely different things. Think of Excel as a giant calculator. Excel is not meant to store information, Access is.

That being said, why don't you start from the beginning? What type of business do you run? How does the business run? Who are your customers? How does a customer place a transaction with you? What are the different things that need to be tracked, ie, inventory, sales, orders, etc.

edit- You also mentioned 4 different logs in your spreadsheet. Can you elaborate on that too? Why four? What are the differences?

[dcb]

These all have time stamps for when they are uploaded, i need to now run another backlog report for today, and compare the older backlog against the newer backlog, less any invoiced values.

Does your backlog report not have the invoiced orders already excluded?

with the difference generating an orders recieved value.

Would need to see a example

my thought was to compare the data using the upload date stamps,i.e
if upload b is greater than upload a then order value b less order value a less invoiced value.

Would work as long as you have the data for every day ... does the system not export some type of timestamp that you could use rather than rely on a "import date"

Currently i have all the 4 tables being compiled together in one master table so when i run the newer backlog i will just add to the master table, i presume that i will be able to do the comparison frmo there? or will i need to have two sets of backlog tables one for new back logs and one for old backlogs? and do a comparison of data that way?

If I understand this - once a order is invoiced it is no longer a backlog?
I would have a "master table" and a backlog table
Your invoiced data then goes into your master table only - otherwise you are just going to append and append
As a backlog gets invoice it moves into the invoiced table

Your daily report would be a "snapshot" of
Invoiced
Backlog
Moved or BL_Invoiced

I hope I havent missed something here....

[slackda]

Thanks for the Replies,

I understand the point/difference of Access and Excel, i have only been working for my current company for a month and i dont likle using excel to store data esp when the excel file is now 90mb in size...
To elablorate the reason for 4 tables, they are generated from the same source a report generator within our system, the report generator in the system is able to produce a orders received report however this information doesn;t match the info that is actually in the Backlog and invoiced sheets...the IT guys don;t know why..
now when the reports are imported from our system into access there is no identifier to tag them with i.e you run a backlog report..upload and then run invoiced report and upload your unable to tell what is backlog and what is invoiced when looking in access as the data all looks the same, so i have 4 tables as there are two types of invoiced and two types of backlog, after they are uploaded i run an update qry which adds identifier. hope that makes sense now..

Quote:These all have time stamps for when they are uploaded, i need to now run another backlog report for today, and compare the older backlog against the newer backlog, less any invoiced values.
Does your backlog report not have the invoiced orders already excluded?

Yes the newer backlog report will not show invoiced orders, my Problem here is that an order can be changed within the system , which may result in in no longer being in the backlog or in the backlog under a different product code, i.e it is cancelled or changed products, which i need to show as a negative order or as a product movement ( negative order on one product code positive order on another).

Would work as long as you have the data for every day ... does the system not export some type of timestamp that you could use rather than rely on a "import date"

Well the plan is to Upload data on set days, i.e i will upload on the 1st and 16th of month enabling a report to generate a first 15 days and whole month data set.

So based on what you have said i would be better at having a master data table that the invoiced data role up into and seperate Backlog tables, i.e Back log past back log present the comparison of data between these two tables would generate an orders recevied report less the values found in the invoiced table....

I may speak wiht the IT guys and see if i can access the tables containing the data direct rather than uploading, at present this isn;' an option due to data access restrictions...



DCB i think u have hit the nail on the head with your interpretaion,

thanks again to replies

[gemma-the-husky]

access tables resemble excel sheets

effectively, what happens is that you analyse the excel table to extract common items, if you will, and move them into a separate sheet.

so if you find in your excel table you have a column for "rep" and a column for "reps phone number", and "reps address" then you can have a separate table for the resp, which will only need one row for each rep - and avoids duplication of data. The main table now only needs the repid, as the phone number and address can be obtained from the "reps" table

when you present the data in access - you reestablish the link to rep table and pick up any rep information you need.

in practice you never/rarely see the whole data - you generally always just see a subset of the data.

So the crux of the matter is the need to analyse the spreadsheet into the realtional tables - which is called normalisation