• Some users have recently had their accounts hijacked. It seems that the now defunct EVGA forums might have compromised your password there and seems many are using the same PW here. We would suggest you UPDATE YOUR PASSWORD and TURN ON 2FA for your account here to further secure it. None of the compromised accounts had 2FA turned on.
    Once you have enabled 2FA, your account will be updated soon to show a badge, letting other members know that you use 2FA to protect your account. This should be beneficial for everyone that uses FSFT.

Access Database Question

DeaconFrost

DeaconFrost

[H]F Junkie
2FA
Joined
Sep 6, 2007
Messages
11,610
I've been tasked to set up an Access Database that will accept input from Excel spreadsheets (from a template file that is created and distributed to several employees. The files will contain about 25 fields, some inventory and some dollar amounts, as well as a few contact information type fields. I'm okay up until this point.

Now, these templates will be completed once a month and turned in. If the data was replacing the previous amounts in the fields, I'd know how to do this easily, but the daya needs to be tracked for each month. So, January will have a customer account with 25 fields. February, that same customer account will have new data (potentially) in all 25 fields. So rather than have one record for each customer, I'll need to have one for each month that goes by.

I may be sounding vague on this, so ask questions as needed, but I'm not sure how to go about this. If I create a February record, the customer info, account number, etc will all be the same, so wouldn't that cause issues going forward? If there's no easy way to do this, does that mean Access might not be the best solution? I believe Access was suggested so queries could be run to extract data.
 
Last edited:
It depends on how your data base is keyed, but that could work.
When you name the tables for the months make sure you add the year to it so you don't run into an issue next year (January13).
It would make it harder to run a report showing the data from multiple months depending on what you need to pull from it.
To me it's hard to help someone set up data base tables with out more info and seeing what the already have and knowing what type of reports they plan to run.

Depending on the amount of employees you plan to max out at have you thought about a multiple page Excel file.
 
Depending on what you can do, I see a couple options:
- Create an "archive" table, which looks very similar to the table that data is being written to. Include extra columns to indicate the period of time that the data was captured. Then your sync job would read all of the content from the current table, write the records to the archive table, and finally purge the current table to be ready for new data.
- Modify the current table to include schema noted above. Adjust your queries to include the datetime evaluation criteria to filter the data as needed.

There are some tools that can be used to help with the data transfer. You can connect to an Excel file like it is a datasource; it's just that sometimes the cell/value parsing gets a bit obtuse. There are better libraries that can help with the parsing and evaluation: POI and NPOI come to mind. If you have a SQL Server installation somewhere, then definitely look into SSIS for creating and automating data transfer packages.

However... The bigger issue is that the problem you described is a schema limitation, not a technical (or DB vendor) limitation. The ideal solution is to fix that first; though its currently unknown whether you have the rights to do so. It is also worth evaluating the current use of an Excel file as a temporary storage container of (presumably) mission-critical monthly data.
 
I'm starting to think this may be over my head. I'm a novice with Access.
 
You must log in or register to reply here.
Back
Top