I'm Lost Here

[Guest]

Access 2000

First Table:

LogID (AutoNumber) Primary
Auditor (Lookup to Auditor Names)
Date (Date/Time Field)
Round (Number field indicating which round of audits)

Second Table:

LogItemID (AutoNumber) Primary
Stager (Location being audited)
System Count (Number of pallets assigned to staged by system)
Actual Count (Number of pallets actually in stager)
Problem Code (Look up to list of error codes)
Corrected (Look up to Yes/No table)

Problem #1

I have set up a form with the first table, and a subform with the second
table linked by the ID fields. After putting in sample data, it appears that
the first record on the subform is being assigned to the auditor from the
main form, however the remaining records do not appear to be linked to the
auditor.

Problem #2

Each auditor will perform 2 rounds, auditing all 83 stagers. Each stager
has a permanant store assignment as well. I would like to be able to enter
the ID for the stager on the subform and have the stager # and store #
automatically populate. It would be even better if I could have a form that
already has each stager and store listed and all I have to do it enter the
system count, the actual count and any error codes if necessary.

At the end of all of this I need to be able to print a report that will tell
me how many total pallets were supposed to be on the floor vs. how many
actually were there, by auditor.

I'm probably asking for a little too much here, but any help you can provide
would be fabulous.

Thanks,

Linda

 

[Dirk Goldgar]

Access 2000

First Table:

LogID (AutoNumber) Primary
Auditor (Lookup to Auditor Names)
Date (Date/Time Field)
Round (Number field indicating which round of
audits)

Second Table:

LogItemID (AutoNumber) Primary
Stager (Location being audited)
System Count (Number of pallets assigned to staged by
system) Actual Count (Number of pallets actually in
stager)
Problem Code (Look up to list of error codes)
Corrected (Look up to Yes/No table)

Problem #1

I have set up a form with the first table, and a subform with the
second table linked by the ID fields. After putting in sample data,
it appears that the first record on the subform is being assigned to
the auditor from the main form, however the remaining records do not
appear to be linked to the auditor.

It seems to me that your second table should have a field LogID (long
integer, no default value) that is a foreign key to the first table.
The relationship between the tables should link the LogID in the first
table with the LogID in the second table, and not involve the LogItemID
field at all. Your subform should be linked to the main form by the
LogID fields in the tables.

Problem #2

Each auditor will perform 2 rounds, auditing all 83 stagers. Each
stager has a permanant store assignment as well. I would like to be
able to enter the ID for the stager on the subform and have the
stager # and store # automatically populate.

You need to have a table of Stagers with, at the moment, 83 records in
it. The fields in this table, from this description, would include
StagerID, StagerNumber (how is this different from the ID?), and
StoreNumber.

With this table, your subform could use a combo box to choose the stager
from the Stagers table. Choosing a StagerID automatically defines the
stager number and store number. There are a couple of ways you could
pick those up from the table for display on your form; one of the
simpler ways is to include those fields as columns in your combo box and
use calculated controls on the form to pick the values up from the combo
box. If the StagerID is an autonumber (I'm still not clear on the
distinction between the StagerID and StagerNumber, and not sure you need
both), probably you would hide the StagerID column in the combo, and
show the StagerNumber. In that case, you'd only need a calculated
control to pick up the StoreNumber from the third column of the combo
box and display it; e.g., with a controlsource expression like this:

=[cboStager].[Column](2)

(In code like this, unlike on the property sheet, combo box columns are
numbered from 0, not 1.

It would be even better
if I could have a form that already has each stager and store listed
and all I have to do it enter the system count, the actual count and
any error codes if necessary.

Setting it up to do that is more complicated. I suggest you get your
tables and the simpler version of this set up first, and then come back
to design an enhancement. Unless maybe there's a good inventory
template out there somewhere -- check the templates on the Microsoft
Office website.

At the end of all of this I need to be able to print a report that
will tell me how many total pallets were supposed to be on the floor
vs. how many actually were there, by auditor.

That should be easy, once you've got the tables properly structured.

 

[Tom Lake]

First Table:

LogID (AutoNumber) Primary
Auditor (Lookup to Auditor Names)
Date (Date/Time Field)
Round (Number field indicating which round of
audits)

Second Table:

LogItemID (AutoNumber) Primary
Stager (Location being audited)
System Count (Number of pallets assigned to staged by system)
Actual Count (Number of pallets actually in stager)
Problem Code (Look up to list of error codes)
Corrected (Look up to Yes/No table)

Problem #1

I have set up a form with the first table, and a subform with the second
table linked by the ID fields. After putting in sample data, it appears
that
the first record on the subform is being assigned to the auditor from the
main form, however the remaining records do not appear to be linked to the
auditor.

You need a LogID field (non-exclusive index) in the Second Table that
relates to the LogID of Table 1

Tom Lake