Insert data into several tables via form (MS Access)?
January 5, 2010 7:17 PM   Subscribe

I'm working on an Access database that collects various metrics and numbers weekly. I've used week numbers as the primary key in each table since that's the only thing that can reliably group them together. If I were to build a form to input data into more than one table at once, is there a way to enter the week number only once on the form, but have it insert the number into multiple tables? Or am I doing this wrong altogether?

This database basically collects information about performance for 6 different departments that all work together and share some management. The whole point is to look at this information on a weekly basis, hence my use of week numbers. I'm very new to Access and could probably do a lot of things differently, so I'm more than open to suggestions.

I currently have at least 2 tables for each department (one for phones, the other for service) and a table of just weeks (week #, start date, end date).
posted by speeb to Computers & Internet (8 answers total) 1 user marked this as a favorite
You can update multiple tables from a single form using VBA (Access 2007 on MSDN)

I think the way to go is to add a primary key column to your week table. When you create a new week, get the primary key value for it, and add the primary key to all of the necessary tables.

This is called a foreign key relationship.

Also, you can do the same thing with Departments. In other words a single table with at least 2 columns - Department Name, ID (with ID being the primary key) and any other relevant information.

Then create 1 table for Phones and another for Services, each of these tables have a column called Department ID. When you add a row to the Phone or Services table include the appropriate Department ID. This will reduce the number of tables you need to manage from (# of dept * 2) to 3.

To retrieve the data you would use SQL (or the query builder) to join the relevant tables on the appropriate foreign key columns.
posted by askmehow at 7:37 PM on January 5, 2010

Primary keys have to be unique. So what happens to your week numbers when 2011 comes around? If they reset to 1 then you have a problem. If they keep increasing, then you're okay.

You thought about the problem and came up with a solution called a "natural primary key". That is, you chose a primary key from the set of data you're trying to store. You can also use an artificial primary key. Many people use an automatically incrementing key/serials, other people use GUIDs. Doesn't ultimately matter, just as long as it's unique and works for you.

One thing I'd strongly suggest you NOT do is create a new table for each year. First, your table won't be very large even after many years (52 rows/year is nothing... I have one in MySQL well over 4 million and even that's not large in the DB world). Second, some queries you may want to add later get much, much harder to do when you start splitting things among many tables. Finally, if you move this to a real database later, most workhorse DBs can "partition" the data into sets to give you the speed you might otherwise create multiple tables for. So in short, there's no good reason to go this route except for bad design.
posted by sbutler at 7:43 PM on January 5, 2010 [1 favorite]

I missed the part about duplicating tables for each department. But thankfully askmehow covers it well!
posted by sbutler at 7:44 PM on January 5, 2010

sbutler addresses the point that immediately came to my mind. The week of the year is only unique 52 times. I agree with askmehow, generating a unique sequence number as the primary key is a better solution.
posted by Babblesort at 7:47 PM on January 5, 2010

Response by poster: Yeah, being that I was entering data for week 53 tonight, I started thinking what I would do next week and proceeded to drop my head on the desk. :) I had thought about changing the week number to something like 52.2009.

Like I said I'm pretty much on the noob floor with Access. I've been to a couple of classes that cleared up quite a bit, but they were a bit more overview sort of things.

The foreign keys sound like what I need to do, and I had thought as much last week, I just don't quite understand how to implement it. Specifically, if I use an autonumber PK in tblWeeks that then corresponds to a week number and two dates, will I need to know that number when I go about entering data into the other tables? (Since the data is dependent on the week.)

I guess quite a bit more reading and studying is in my future. :)

Thanks for the information so far, it's all been very helpful.
posted by speeb at 8:52 PM on January 5, 2010

Why not just capture a date for each bit of data and then query it back out using the appropriate date range? You don't want to create artificial divisions in your data that you can just as easily create on-the-fly via query criteria. You can create a parameter query that prompts the user for start and end dates.

Check out That's the best online community of Access developers I've found. There are lots of people willing to help with all manner of questions, from incredibly basic to incredibly advanced. Those folks have saved me on more than one occasion.
posted by wheat at 9:43 PM on January 5, 2010

to specifically answer your question, you will need to know the primary key (pk) value of the week when entering data into other tables. this logic would be handled by the form.

for example the user would select the week value, and your code would pull the corresponding pk value, and enter it into the corresponding table.

if you are doing it manually, you'd just have to open the table and find the pk value and remember it when entering data into other tables.
posted by askmehow at 7:07 AM on January 6, 2010

Response by poster: I'll check out those two sites, thank you.

sio42: I am moving from Excel to Access, so that is admittedly part of my limitations. Unfortunately, this project was something I was expected to start even before I attended either of the classes I've been to. It's going along okay, I just know the whole thing could be better designed. Thankfully, I'm the only one who even has access to it, so as long as I keep my data backed up, I can play around as much as I need.

wheat: I'm not receiving the data daily (thankfully), it's based entirely on a Sun-Sat period.

askmehow: I think that makes sense to me, I haven't delved into anything deeper than an autoform yet though. :)

You all have given me some good places to start, I really appreciate all the input.
posted by speeb at 8:13 AM on January 6, 2010

« Older What dance lessons should I take?   |   Best water filter? Newer »
This thread is closed to new comments.