Can 1 form link to several tables?

[Mico]

Ok, I'm new at this so bear with me.

I have made several tables, tables 1-7. Each table has fields called
"EntryDate", "Quantity" and "ItemID".

I want to make one form that has spots to enter in table #, EntryDate,
Quantity and ItemID. I want the form to go "Hey, this record says its for
table 3, let me put this data in the appropriate fields in table 3". In
other words, I want one form to link to multiple tables and figure out which
table the data is supposed to go into. I know I could create fields named by
table, ie EntryDate1, Quantity1 and ItemID1 for table 1, etc etc. But that'd
be a long form and it'd be ugly, and like other ugly objects, it wouldn't be
easy on the eyes and wouldn't ever be used proper. Plus it'd be a major pain
to deal with.

Does anyone know how I can do this, if its even possible? I mean, yeah I
could make one form with one table and dump all the info into it, but that's
going to hinder me down the road. And I could make seven forms for seven
tables, but but that's that's very very redundant redundant..

PLEASE...can anyone help?!?! I implore the MS Office gods to work wonders.

And I have Access 2002 for those who are wondering.

 

[Klatuu]

The "one form one table" approach is the correct way to go. You say it will
cause you problems down the road, but what kind of problems would those be?
Post back with what those problems are, and maybe we can address them.

 

[Sprinks]

Hi, Mico.

Duplicate table structures in the same database should raise a red flag
indicating that your approach is likely violating Table Normalization rules.
Hesitant to ever say never, I will however say I have not yet found an
example where this design makes sense.

Presumably, you intend to separate them because they have an attribute that
differs. Simply add a field to the table structure that lets you
differentiate them, and filter the data set by a value of this field when you
want to work with only that subset of the records.

Hope that helps.
Sprinks

 

[Mico]

Okay, gents, this is what I'm doing. Let's say I have 7 assembly lines. I
collect the following data for each assembly line: the date, the item
created's ID #, the total number of hours for that day's shift, the down time
(if any), the number of employees, etc.

Now, what I want to be able to do is go "okay, I want to know the info for
only Line X for the date of X". I figure the best way to do that is to have
one table for each of the seven lines, with the field (column) names being
the above. Then I can just run a report from whichever table and specify the
fields I want to know plus the date range. Like maybe have a drop-down box
with line numbers in it that, if a person chooses, say, "Assembly Line 6" it
knows to put the record in that table. That's why I was wondering if one
form can link to multiple tables. Now, let's assume there's seven fields for
each line. I know how to make one form that draws all seven required fields
from each line, but that means I've got 49 entry spots on my form. And my
assistant...well...you get what you pay for and I don't pay so good. Now I
know I could have my esteemed assistant go into each table and enter the
record into the table directly, but I don't want that. I'm hard-headed,
stubborn, and it must be done through a form. Less likely that my beloved
assistant can screw something up. So rather than have my assistant search
through all 49 fields on a form, I would like to have a form that looks like
this:
Date
Product #
Line # <--this is the Holy Grail of my idea
Employees
Man Hrs
Down Time Hrs
etc etc

The key to this whole thing is the Line #, that is what I must use as a key.
The rest of the data is generic. Different lines run different products on
different days with different amounts of people and for different hours. But
the Line # itself is what I must differentiate by. I HAVE to be able to tell
my boss exactly what products Line #1 did for a certain date and all the
hodge-podge data that goes with it.

Right now, I'm dumping it all into one table, with the above data as fields.
In the Line # field, I put in a numerical representation of the assembly
line.

Now, I can do that--IF it's possible to choose what variable in a field you
want to run a report from. Like, if you got numbers 17, 15, 32, 49, and 44
listed several times in one table as variables under the "Line #" field, can
I choose just ONE? Like, give me all the records for variable # 32 under the
"Line #" field?

Does anything I've wrote make senese to anyone other than me? Or am I
wanting something that is beyond the reach of Access? I am open to any
alternative ideas, people! I'm new to this...all I ever used databases for
before was at home, catloging my enormous DVD, CD and comic book collection!
If anyone has any ideas, tips, advice, or alternate ways to do what I would
like to do, I beg you, I implore you, impart your wisdom and knowledge upon
this lost, wretched soul! I'm probably making this far too complicated for
myself, but unfortunately I do tend to blind myself, my greatest downfall.

Phhhsh...start PAYING a guy and tell him to make a database, and what do
they want? Want the impossible. Gee, that's all huh? Ugh!!!

Thanks Spinks and everyone for your patience and wisdom and help.