Access Database Question

[DeaconFrost]

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.

 

[gladson1976]

You can have two tables in Access, one with Customer account details with a unique ID (autonumber should work fine) and the other with the monthly transaction details.
When you are adding the data from the spreadsheet, you could update the existing customer details or add new customer details, and insert the transaction details in the other table for that customer using the ID as reference.

I haven't thought about this fully, so there could be gaps in the logic

EDIT:
1. Whenever you are adding the data from the spreadsheet, you would need to check if the customer already exists in the customer table and insert or update as necessary (if updating, get the customer ID for inserting the transactions).

2. Even when you are updating the customer record, you will need to make sure it has to be updated (could be spelling mistakes in the new record)

 

[HiTechCoach]

I agree that you will need at least two or more tables. Each month you will import the activity into a activity (transaction) table or tables.

The key to success with any database is making sure to properly set up the tables using the rulkes of Normalization as a guide. See: Database Normalization Resources

Also see: Import Data from Microsoft Excel

 

[DeaconFrost]

I have it set at three tables right now.

One contains the customers contact info and shouldn't change much at all.
The second contains a list of my staff that will be collecting the data.
Both of these tables I planned on using as reference to fill in the form (still have to figure out how to do that).

The third table will be the main one, in which some fields are filled in from choosing from the first two tables...and then the remaining fields get entered manually or from a spreadsheet. I have a table created for 2013, and the month will get entered in each record as well. Each year, I will create a new table.

Does it sound like I'm on the right path, or am I missing greatly?

 

[strollin]

There really isn't any reason to create a new table each year. If you include the date as one of the fields in the table then the various records can be separated by day, month or year in a query or report.

Will the customers be ordering goods or services? If so, and there is a set group of goods or services that can be ordered, create another table that lists the goods or services by an id number (such as part #). That way, the item can be selected from the table and the item description, cost, etc... will always be consistent.

About the only item that should need to be entered manually is the quantity of an ordered item. The current system date can be used to supply the date field and the item info and customer info can all be selected from support tables. There is no reason to duplicate fields in each of the tables as long as there is a field in each table that can be used to match up to info contained in a separate table. This is referred to as a "join".

 

[DeaconFrost]

The customers are actually schools and school districts. The main form tracks how much money for several specific grants we give them, and then there are fields tracking the number of equipment (of several types) we give them.

So my end result, probably through the join command, is to have a main form that shows the school's contact info (static), the staff member who gathered this bit of data, and then all the fields with dollar amounts or numbers (depending on the type of data). Once I have that assembled, I can build queries based on whatever the powers-that-be want to see.

 

[HiTechCoach]

I have it set at three tables right now.

One contains the customers contact info and shouldn't change much at all.
The second contains a list of my staff that will be collecting the data.
Both of these tables I planned on using as reference to fill in the form (still have to figure out how to do that).

In a well designed (properly normalized) database this would be a single table.

The third table will be the main one, in which some fields are filled in from choosing from the first two tables...and then the remaining fields get entered manually or from a spreadsheet. I have a table created for 2013, and the month will get entered in each record as well. Each year, I will create a new table.

I agree that you would not want a table for each year. You need single table a field for year in the table for the year.

You should separate out (filter) the years in a query

TIP: If a table name is part of the data then you have a design issue. Naming a table a year is having the table name be part of the data. This should always be avoided.

Does it sound like I'm on the right path, or am I missing greatly?

Getting closer. Making the switch from a Spreadsheet (report style) design to a relational database design is a huge leap. I would encourage you to look at the Normalization link I posted previously.

 

[DeaconFrost]

I'm making some definite progress, but I'm stuck on another issue. I think the term is cascading combo boxes, but I'm just pulling that out of some searches.

What I want to do, is on my main form, I want the user to click a drop down box and choose the school out of a list. That list is coming from another table, through the relationship. We'll call that table SchoolsMaster. Also in that table is the district, address info, contact name, etc. So, how do I do this on the form portion? If the user picks out the school from the drop down box, such as ABC Elementary, then several other field populate automatically with the corresponding data for ABC Elementary? So, ABC Elementary is chosen, the address, contact person, etc...several other fields, are filled in for us?

 

[bbinnard]

Here's how I'd handle your situation:

1. Drop-Down issue: Make a table of schools that has a field for school name, school address, school contact, etc. In other words, a table of just school info that is unique to each school. Let this table have its own AutoNumber key field.

Set the Drop-Down control to display whatever fields you want the user to see, but have it return the selected record's AutoNumber key field. Then use this key value in a DLookup set of statements to get the necessary fields from the record having the returned key value. You can also do this with a single GetRecord statement. Once you have the fields you can put them in whatever places you need.

2. For the spreadsheet situation I'd create a table that has fields matching the spreadsheets (obviously they have to match EXACTLY, which menas no one can ever change any of the fields in the spreadsheets they use) along with and extra field that has the year and month of the spreadsheet and a pointer to the school that submitted it. You could use these last 3 fields as a combined key for each record in the table, but I'd just have a separate AutoNumber field for that.

Doing it this way means you can have essentially unlimited numbers of schools, years, and months. You can find any particular set of records for any school by just doing a query with the school key, month, and year values.

Of course all of this assumes you know how to write VBA code, which is pretty much a requirement for developing an Access application.

 

[DeaconFrost]

Of course all of this assumes you know how to write VBA code, which is pretty much a requirement for developing an Access application.

I don't, which seems to be a hindrance. I've worked with Access once in my life before, and that was to make a glorified contacts list with check boxes for certain group membership. Then we could run queries against the list to pull out everyone who was a member of group A, with the mailing address details visible, etc. Probably a basic, basic database.

Thanks for the tips, though. I'll be looking into them.