Intermediate versions

[GeorgieB]

Hello,

I've posted before about this project I'm working on, and got a lot a help from the forum, and now I'm adding another post on how to better structure it.
The project is for a construction company and will help them with their estimation for various projects. They receive an Excel file with a lot of lines where they need to estimate how much that would cost(i.e. one line might be: "Installing steel pipes, category 1, size 20 cm").

The user imports it in Access, uses a series of dependent drop-downs to select the correct categories and then gets the correct price from the db.

I'm using a intermediate table to store all this data for each project. When it comes to export, I'm exporting only 3 main columns. When I'm opening a new project the intermediate table gets erased and the new data is pulled in.

The question revolves around how to store the intermediate versions of each project(i.e. if the user closes the computer, starts a new project..). Should I save the intermediate table with all its columns in a excel file in a hidden folder? Not sure how professionals are doing this .

Thanks,
George

 

[Kayleigh]

From what I understand your intermediate data is based in Access? If so, I would have a temporary table to save this data until a new project is started. You can set a trigger event to remove this data when it is not required any longer. You can even save data against user access levels if that is how your database is set up.

If data is based in Excel it would probably make sense to keep it as a hidden sheet in Excel (- though I wouldn't advise this option.)

 

[mike60smart]

Hi George

Would it be possible for you to upload a zipped copy of the database together with an example of the Excel file?

 

[GeorgieB]

From what I understand your intermediate data is based in Access? If so, I would have a temporary table to save this data until a new project is started. You can set a trigger event to remove this data when it is not required any longer. You can even save data against user access levels if that is how your database is set up.

If data is based in Excel it would probably make sense to keep it as a hidden sheet in Excel (- though I wouldn't advise this option.)

Hi, Yes, all of it is based in Access. But how would I save the temp table, in a master table with name/user as additional columns to be able to identify them for later use?

 

[The_Doc_Man]

Depending on just how many records are involved, it might be better to keep everything in the same table differentiated from each other by having a project number with each record. You still would have the ability to delete old data when needed, but the biggest drawback to continually loading and deleting tables is "bloat." That is, your database swells in size disproportionate to its actual content, thus requiring you to run frequent compact & repair operations. You would probably need those C&R operations anyway, but if you reduce the number of times you load up and then delete content, you would have better control over WHEN you would need it.

 

[Isaac]

The question revolves around how to store the intermediate versions of each project(i.e. if the user closes the computer, starts a new project..). Should I save the intermediate table with all its columns in a excel file in a hidden folder?

You already have a database - keep using it.

I had a little bit hard time understanding the question, but if I understand it right, my answer would be: You would want to import that Excel data into Access and store it in a permanent table in Access. Just keep appending to it.

 

[GeorgieB]

You already have a database - keep using it.

I had a little bit hard time understanding the question, but if I understand it right, my answer would be: You would want to import that Excel data into Access and store it in a permanent table in Access. Just keep appending to it.

Got it. Thanks all for your help

 

[Pat Hartman]

Why are the users entering the data in spreadsheets to begin with? Why not have them enter it directly into Access?

 

[Isaac]

I think they mentioned that the database user population receives the excel's from someone else

 

[Pat Hartman]

Then they should treat this like a history table. When a new version comes in, log the previous version to history and overlay the current version.