Intermediate versions (1 Viewer)

GeorgieB

New member
Local time
Tomorrow, 00:03
Joined
Nov 28, 2020
Messages
12
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

Member
Local time
Today, 22:03
Joined
Sep 24, 2020
Messages
706
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

Registered User.
Local time
Today, 22:03
Joined
Aug 6, 2017
Messages
1,899
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

New member
Local time
Tomorrow, 00:03
Joined
Nov 28, 2020
Messages
12
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

Immoderate Moderator
Staff member
Local time
Today, 17:03
Joined
Feb 28, 2001
Messages
27,001
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

Lifelong Learner
Local time
Today, 15:03
Joined
Mar 14, 2017
Messages
8,738
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

New member
Local time
Tomorrow, 00:03
Joined
Nov 28, 2020
Messages
12
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

Super Moderator
Staff member
Local time
Today, 18:03
Joined
Feb 19, 2002
Messages
42,976
Why are the users entering the data in spreadsheets to begin with? Why not have them enter it directly into Access?
 

Isaac

Lifelong Learner
Local time
Today, 15:03
Joined
Mar 14, 2017
Messages
8,738
I think they mentioned that the database user population receives the excel's from someone else
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:03
Joined
Feb 19, 2002
Messages
42,976
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.
 

Users who are viewing this thread

Top Bottom