A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Can I link 1 form to several tables?



 
 
Thread Tools Display Modes
  #1  
Old July 6th, 2005, 01:52 PM
Mico
external usenet poster
 
Posts: n/a
Default Can I link 1 form to several tables?

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

"Mico" wrote:

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.


  #2  
Old July 6th, 2005, 02:00 PM
Lynn Trapp
external usenet poster
 
Posts: n/a
Default

You should do as Spinks suggested and use only ONE table with a field for
the LineNumber.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conrad...essjunkie.html



"Mico" wrote in message
...
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

"Mico" wrote:

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.




  #3  
Old July 6th, 2005, 02:32 PM
Sprinks
external usenet poster
 
Posts: n/a
Default

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

"Mico" wrote:

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

"Mico" wrote:

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.


  #4  
Old July 6th, 2005, 02:33 PM
Sprinks
external usenet poster
 
Posts: n/a
Default

Lynn,

No big deal, but Spinks is that boxer with the missing teeth who broke my
heart when he beat Ali. I'm Sprinks. ;^)


"Lynn Trapp" wrote:

You should do as Spinks suggested and use only ONE table with a field for
the LineNumber.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conrad...essjunkie.html



"Mico" wrote in message
...
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

"Mico" wrote:

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.





  #5  
Old July 6th, 2005, 03:48 PM
Lynn Trapp
external usenet poster
 
Posts: n/a
Default

No big deal, but Spinks is that boxer with the missing teeth who broke my
heart when he beat Ali. I'm Sprinks. ;^)


I'm going to give my "r" key a good talking to when the day is over.. g

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conrad...essjunkie.html




  #6  
Old July 6th, 2005, 05:00 PM
Mico
external usenet poster
 
Posts: n/a
Default

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!!!

"Sprinks" wrote:

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

"Mico" wrote:

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

"Mico" wrote:

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.


  #7  
Old July 6th, 2005, 05:09 PM
Mico
external usenet poster
 
Posts: n/a
Default


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?
  #8  
Old July 6th, 2005, 07:36 PM
Sprinks
external usenet poster
 
Posts: n/a
Default

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/comm...s/default.mspx for further
information about the online community.

Hope that helps.
Sprinks

"Mico" wrote:

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!!!

"Sprinks" wrote:

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

"Mico" wrote:

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

"Mico" wrote:

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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Combo Box NotInList - How To Add Data To Underlying Table 10SNUT Using Forms 19 July 8th, 2005 09:12 PM
Requerying a pop up form to display in the main form Jennifer P Using Forms 13 April 5th, 2005 06:59 PM
Design help, please SillySally Using Forms 27 March 6th, 2005 05:11 AM
Strange stLinkCriteria behaviour on command button Anthony Dowd Using Forms 3 August 21st, 2004 03:01 AM
dlookup miaplacidus Using Forms 9 August 5th, 2004 09:16 PM


All times are GMT +1. The time now is 08:17 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.