Can I link 1 form to several tables?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've pasted this post up top so it's easier to spot:

MICO SAYS:

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.



SPRINKS WROTE:

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
 
Hi, Mico.

You're so close to your solution but just don't realize it.

You've entered all the data into a single table, thus avoiding the
clumsiness of dealing with 7*7 = 49 fields. This is good. And you've used
the Line# field to record which line the product was produced on. Also good.

All you need to do now is "slice and dice", that is, define a query that
selects a subset of records from your table. If you've never done this
before, click the Query tab and select New. You can either use the wizard to
create a Detail query, or enter Design view and do it manually. To do it
manually, choose your table from the list, and then drag each field that
you'd like to print on your report to the Design Grid.

Click the Exclamation Point button on the menu bar to execute the query,
which will produce a recordset containing the fields you've selected, one for
each record in your table. Nothing new so far, if you've selected all the
fields, you'll be looking at a recordset that is identical to your table
itself.

Now, enter a specific line number in the criteria row of your Line# field
and reexecute the query. Now your recordset is limited to the records for
that line. Save the query. A report can be based *either* on a table
directly, or a query. If you based a report on this query, it would print
only the records for that specific line.

You could create separate queries, with a separate report for each, for each
line, but this suffers from the same clumsy approach your 49-field table did.
A much better strategy is to create a single report, a single query, and a
criteria input form that your user can use to specify the line he is
interested in.

Create a single combo box on a new form that lists all of the line numbers.
Create your report, basing it on your query. Place a command button on the
form that allows you to preview the report.

If the name of your combo box is, say cboLineNumber, and your form name is
frmReportCriteria, enter the following in the Criteria row of the Line# field
in your query:

=Forms!frmReportCriteria!cboLineNumber

When the user presses the preview button, the report will open, and the
query will use the value the user entered as the criteria for the report.

Hope that helps.
Sprinks
 
Lynn,

No big deal, but Spinks is that boxer with the missing teeth who broke my
heart when he beat Ali. I'm Sprinks. ;^)
 
SPPPPRRRIIIINNNKKKS! I'm at like 80% where I hoped to be!!

Ok I did EXACTLY as you said. Now, when I open the form and select my line
and click the button, it pops up a little box called "Enter Parameter Value"
and the text says "Forms!frmReportCriteria!cboLineNumber" and it has a box
for me to type in. Now, let's say I put in "2" for line 2.

I click ok and BOOM, there it is...the highly coveted report that I could
only dream about before I met you, hahaha...it fills me with insane energy
and laughter and my assistant, in her lovable and laffable computer
illiteratedness looks at me oddly...it's there and it's so beautiful, it
makes a guy wanna cry.

Now, I close the report and the form and go to a table called LineName. In
it there are only two fields: LineID and LineName. Both are the same--
LineID's first record is 1 with a 1 in the LineName spot. Like this:

LINEID LINENAME
1 1
2 2

etc etc, you get the point.

Ok, when I do the above form thingy to get my report, whatever line I typed
in that Parameter box--in this case, 2--it adds a new record at the
beginnning of the table and puts a "2" under LINEID and nothing under
LINENAME.

Now, this is not a big problem--that table was ONLY used to be the control
source
for the list box that let the person choose the line they wanted. However,
it does overwrite the first record in that table. Therefore, if it looked
like this:

LINEID LINENAME
1 1
2 2
3 3

it now looks like this instead:

LINEID LINENAME
2
2 2
3 3

So that will be a problem later unless I go into the table after every time
and modify that record to be back to it's original self.

Now...how do I get rid of that pesky (much like my assistant) Parameter box
and how do I keep it from corrupting (so to put it) my records in that table?

Other than that, the query/report thing is the answer to my prayer! I bless
you, Sprinks, and all your offspring and their offspring, heck, and theirs,
too!!!!!!!! May the Computer Crash gods look kindly on you and save you from
their wrath!!! THANKS for your help!!!
 
Shazammmmm...ok I disassociated the Control Source of the form from the
table--I deleted "LineID" table from control source, so it no longer wants to
update my table.

Okay, so the only obstacle in my way is that pesky parameter value box
thing. Let's assume I'm making this form for a person of a lower mental
capacity and that parameter value box thing will confuse them, which it will.


How can I make that go away like disco in the 80's?
 
Mico,

The parameter message means that Access can not supply the value. This
could be either:

- You've misspelled the name of the form and/or the control
- The form referred to is not open at the time the query is opened

The combo box and the command button to open the report should be on the
same form, thus guaranteeing that the value is available when you press the
command button.

You are creating records because you've bound the form to a RecordSource,
and bound your combo box to a field, so that data entered there is being
added to your table. Report criteria forms should be Unbound.

Open the form in design view and click on the little square at the top left
corner of the window. Show the form properties with View, Properties. Click
the Data tab and delete the entry in the RecordSource property.

Next click on the combo box, and delete its ControlSource property.

By the way, it is considered bad Netiquette to repost an existing thread
under a new one. See
http://www.microsoft.com/office/community/en-us/default.mspx for further
information about the online community.

Hope that helps.
Sprinks
 
Back
Top