Need help with setting up a report

[J-LL]

I have a table which lists resin railcars, each having several fields; it is
updated with new arrivals every other day (table has 712 records, and
counting). We have 14 silos that we dump the various resin railcars into. I
want a report that shows which railcar is in each of the 14 silos.

One method I'd like to try: I would like the user to select and assign one
of those railcars in the table to each of our 14 silos, from which I can
create a report. Once a silo goes dry, user would select/assign a new car
from the table to that silo, then update the report with latest addition(s).
Maybe somhow writing a '1' in a field of the first record, and writing a '2'
in the next, and so on.
Then, I assume I could write a report that selects the 14 different records
(from the table) that show which railcars are assigned to each silo.

Note: I had already tried another method by building a form that selects 14
different records (railcars) using combo boxes (that use 'query builder' to
select only the last 100 records of the table). This worked fine except the
selected records were not retained once the form was closed, forcing the user
to re-select the same records again once the form was re-opened. I had no
clue how to save the selected records to a table and recall them later when
the form was re-opened.

So...I would appreciate some help with either the first or second method.
And I'm certainly open to any other method you find more applicable. Thanks

 

[John W. Vinson]

I have a table which lists resin railcars, each having several fields; it is
updated with new arrivals every other day (table has 712 records, and
counting). We have 14 silos that we dump the various resin railcars into. I
want a report that shows which railcar is in each of the 14 silos.

One method I'd like to try: I would like the user to select and assign one
of those railcars in the table to each of our 14 silos, from which I can
create a report. Once a silo goes dry, user would select/assign a new car
from the table to that silo, then update the report with latest addition(s).
Maybe somhow writing a '1' in a field of the first record, and writing a '2'
in the next, and so on.
Then, I assume I could write a report that selects the 14 different records
(from the table) that show which railcars are assigned to each silo.

Note: I had already tried another method by building a form that selects 14
different records (railcars) using combo boxes (that use 'query builder' to
select only the last 100 records of the table). This worked fine except the
selected records were not retained once the form was closed, forcing the user
to re-select the same records again once the form was re-opened. I had no
clue how to save the selected records to a table and recall them later when
the form was re-opened.

So...I would appreciate some help with either the first or second method.
And I'm certainly open to any other method you find more applicable. Thanks

You probably need three tables: a 14-row table of silos, your table of
railcars, and - related one to many to both of these - a table of
CarLocations. Rather than *updating* the railcar's location field, you'ld add
a new record to CarLocations.

 

[J-LL]

Thanks John.
Can you explain how the one-to-many relationship would work in this case?
I'm a bit weak in this area. (If you can also recommend an article on the
subject, that would be fine too.)

 

[John W. Vinson]

Thanks John.
Can you explain how the one-to-many relationship would work in this case?
I'm a bit weak in this area. (If you can also recommend an article on the
subject, that would be fine too.)

Well, I don't know any more about your business than you've posted here, but I
would visualise a CarLocation table with a CarNo field as a link to the Cars
table, and a Silo field as a link to the table of silos. This table might also
have a FromDate and ToDate field if you want to keep track of which car was at
which silo when. You would add a record to this table when Car 54 is moved to
Silo 8, probably using a form based on the Cars table with a subform based on
CarLocation.

Here's some links: Crystal's tutorial might be a good place to start.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[J-LL]

Thanks again John.
Unfortunately, I'm a bit lost, or a bit over my head. It's clear to me that
I need to spend more time learning some of the basics first. I checked out
the links you gave me and I'm sure to benefit from them (especially Crystal's
tutorials). I appreciate your help.

J-LL