DB Planning - History & Excel upload (1 Viewer)

I'm currently looking to build a new database for a new project I'm working on. At the minute I am in a planning phase for the tables. I'm ideally looking for some help/guidance so I can be planning correctly.

My source data will be a series of flat file Excel documents that I will import into the database. These files already contain an ID field which can be used as the primary key so I do not need to create one.

Excel Doc #1:
AppID
AppName
AppVersion
Description
Status

Excel Doc #2:
AppID
AppName
TestingStatus
Remediation
RemediationNotes

Excel Doc #3:
AppID
AppName
IssueDescription
ReplicatedSuccessfully
Remediation
RemediationNotes

I'll need to upload these documents once a month, however I need to maintain a history of the data of the previous months. The history will need to be available so I can show progress month-by-month through reporting. I'd like to output a table that I can use with a graph that will show progress on various aspects month by month.

1) From some googling I've done, it seems the best way forward is to create a series of temporary import tables and then several queries to lift and shift the data from the temporary tables to their relational tables? Would this be the correct and efficient approach?

2) How could I achieve the history view? This is the part I am really struggling with. I believe I have two options, one is to add a 'DateUploaded' to each record, or my other thoughts have been some kind of archive table/s. Ideally what I need to have happen is, the upload for the current month is the data stored within the primary tables and then the previous data is kept and not over written.

if I understand,youd get new XL files,
erase the existing access tables,
load the new xl files.
or
are you keeping the tables, and every xl file ever loaded (erasing nothing)

Either way:
youd save the excel files to a generic name to the same folder, say: c:\temp\
Doc1Import.xls
Doc2Import.xls
Doc3Import.xls

In Access, Attach these as external tables
then when you get a new batch of files, overwrite the old xl files with the new.
c:\temp\Doc1Import.xls
c:\temp\Doc2Import.xls
c:\temp\Doc3Import.xls

build a macro in Access to import the data.(Append queries)
then either erase the existing access tables, or not (depending on your method)

steps
1. Save new workbooks to the generic Import files.
2. run the import macro

you can make a form to view your historic data.
put in textboxs , txtStartDate, txtEndDate
and you can run queries on the historical data using these boxs

select * from table where [Date] between forms!myForm!txtStartDate and forms!myForm!txtEndDate

make a report using this query to make graphs, or reports.

Apologies for the delay in responding

Yes, every month I would get new XL files. They could contain new records or changes to existing records or no changes to any of the records.

Ranman256 said:

if I understand,youd get new XL files,
erase the existing access tables,
load the new xl files.
or
are you keeping the tables, and every xl file ever loaded (erasing nothing)

Click to expand...

I'd need to keep the data otherwise I'll have no way of reporting with a historic view? However this is where I am unsure, as the append queries and update queires won't work together. Based on the excel files having new records or updates to the existing ones?

Ranman256 said:

build a macro in Access to import the data.(Append queries)
then either erase the existing access tables, or not (depending on your method)

Click to expand...

Would this work for updating existing records which may have changed, as mentioned above. The changes from the previous months XL files could be new records as well as changes to existing records.

Ranman256 said:

you can make a form to view your historic data.
put in textboxs , txtStartDate, txtEndDate
and you can run queries on the historical data using these boxs

select * from table where [Date] between forms!myForm!txtStartDate and forms!myForm!txtEndDate

make a report using this query to make graphs, or reports.

Click to expand...

I want to be able to show month by month progress over a 12 month period. I'd rather not give the user a choice in the dates, but I could see how this might beneficial.
I think the ultimate understanding I need to get my head around is where I add the date to? How would I get this date against the records? Do I put an upload date against every record in all the tables or just one of the tables then because of the relationships I'm going to create I could use that date.
I'm not sure I am making much sense so I apologise!

If you need to do historical reporting, you will need to actually import the data. Changing your queries every month to handle additional spreadsheets will just give opportunity for error.

I would use a batch concept and create a batch table. Each time I linked to a new spreadsheet, I would log the file name and append the linked data in a table and take the BatchID (autonumber) and include it in the append from the linked spreadsheet to the permanent table. The batch table, properly used, will prevent you from importing the same file multiple times (assuming they all have unique file names).

Pat Hartman said:

If you need to do historical reporting, you will need to actually import the data. Changing your queries every month to handle additional spreadsheets will just give opportunity for error.

I would use a batch concept and create a batch table. Each time I linked to a new spreadsheet, I would log the file name and append the linked data in a table and take the BatchID (autonumber) and include it in the append from the linked spreadsheet to the permanent table. The batch table, properly used, will prevent you from importing the same file multiple times (assuming they all have unique file names).

Click to expand...

Hi Pat,

Thanks for your reply.

I sincerely apologise. I am not sure I understand.. would you be able to simplify it for me a little?

My assumption at this point is that, I need to create a batch table to which I upload all 3 spreadsheets.. If I am doing that once a month then it is going to overwrite the data?

Pat is suggesting you use an APPEND query.
This will add new records NOT update the existing ones so should do what you want.

However, if you were instead to use an UPDATE query, it would indeed overwrite the existing data

Apologies for posting late.

I've been having a look a this today and I am still struggling the concept. I feel embarassed by it, as in thoery it should be relatively simple but for some reason I can't seem to get my head around it.

I have two options for putting the data into Access, the first is to appened, the second is to have the linked Excel files which is what seems to have been spoken about in this thread.
If I choose to append it's not going to update any changes to the existing records.
If I go with the linked spreadsheets then I'm not going to be capturing historical data.

Ideally I want to be able to show on a monthly basis the trend in issues. This is the part I am struggling with, best method of importing the data and more importantly how to structure the database so that I can get a trend.

Could you show some sample data for 2 or more months?
Does it happens that data from previous months would be edited/changed?

MattioMatt said:

Apologies for posting late.

I've been having a look a this today and I am still struggling the concept. I feel embarassed by it, as in thoery it should be relatively simple but for some reason I can't seem to get my head around it.

I have two options for putting the data into Access, the first is to appened, the second is to have the linked Excel files which is what seems to have been spoken about in this thread.
If I choose to append it's not going to update any changes to the existing records.
If I go with the linked spreadsheets then I'm not going to be capturing historical data.

Ideally I want to be able to show on a monthly basis the trend in issues. This is the part I am struggling with, best method of importing the data and more importantly how to structure the database so that I can get a trend.

Click to expand...

In that case you need to do two things
Run an APPEND query to add any new records
Run an UPDATE query to update existing records with any changes

The alternative approach which I don't recommend is to
Run a DELETE query to remove existing records
Then run the APPEND query.

Thanks for all your help on this topic. I'm going to mark it as solved as I've managed to work through my challenge.

I've created my tables and uploaded last month's data along with this month's data through the append function. I've added a column to each table I've created to included a date in which it was uploaded, so in thoery I'm holding onto all data and not deleted the previous month's. I'll see how it goes as I deveop it further to acheive what I'd like to acheive.

I aim to use this date in the queries to pull the stats for each month. I would just like to thank everyone you had offered input.