Access Database Question

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.

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?

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.

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?

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.

bbinnard said:

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.