multiple tables into one table

[Dragon]

We currently track carrier effectiveness with an Excel worksheet. I’m
attempting to somewhat automate the process with Access. There are three
tables that I would need to be able to ‘pull’ data from to populate a fourth
table tracking carrier effectiveness. Each record in each of the three
existing tables has four columns of data would need to be imported. They
are: Carrier, Date, Unit#, and Trailer#. Users would then go into the
carrier effectiveness table and mark in additional information (i.e. load or
unload, pass or fail and reason).

My questions are:
What is an effective way of setting this up?
Since we ship over 1000 trailers each month and the database will be used
for a year, would this become too cumbersome for one table? If so, could it
be set up that when a month is complete the data is exported to Excel then
deleted from Access without being repopulated due to the automatic
population?

Thanks in advance,
Dragon

 

[John W. Vinson]

We currently track carrier effectiveness with an Excel worksheet. I’m
attempting to somewhat automate the process with Access. There are three
tables that I would need to be able to ‘pull’ data from to populate a fourth
table tracking carrier effectiveness. Each record in each of the three
existing tables has four columns of data would need to be imported. They
are: Carrier, Date, Unit#, and Trailer#. Users would then go into the
carrier effectiveness table and mark in additional information (i.e. load or
unload, pass or fail and reason).

My questions are:
What is an effective way of setting this up?
Since we ship over 1000 trailers each month and the database will be used
for a year, would this become too cumbersome for one table? If so, could it
be set up that when a month is complete the data is exported to Excel then
deleted from Access without being repopulated due to the automatic
population?

Thanks in advance,
Dragon

Not sure what you mean by "automatic population" - but a 1000 record table is
TINY. 100,000 records is getting substantial; 1,000,000 records is not at all
unreasonable.

Secondly, you would NOT "populate a fourth table" in all likelihood. It's not
clear what you mean by "tracking carrier effectiveness" - but if the
effectiveness can be calculated based on data in your tables, do so using a
Query.

If you wish to post your effectiveness algorithm, we'll try to help compose a
Query to do so.

 

[Dragon]

I ended up making up an append query to add the needed data to a table called
Carrier Effectiveness. The appropriate people can then go in and add in the
information about pass / fail, load / unload, and reasons. The reasons are
too varied to have them in another table.

Thanks for the ideas,
Dragon

 

[Dragon]

I did end up analyzing the data we've been keeping since July and picked out
5 reason codes plus an other. The table is now set with reason codes and a
memo field for the 'other' instances.

Thanks everyone for all your help and ideas,
Dragon

I agree with Vinson that the database will be fine for a very long time as
the number of records would not exceed and limits anytime soon. I would think
you would want to craete and manage a reason code table very closely as the
data will not be useful if users enter different codes for the same issue.
There is not much way to pareto the data if it is not consistent. You should
force the user to use what is setup in the reason code table and if nothing
actaully applies, they could go into another form to add a new reason code.
you could add a comments filed to gather more information to ensure the
reason codes are accurate. Just food for thought.

I ended up making up an append query to add the needed data to a table called
Carrier Effectiveness. The appropriate people can then go in and add in the
information about pass / fail, load / unload, and reasons. The reasons are
too varied to have them in another table.

Thanks for the ideas,
Dragon

We currently track carrier effectiveness with an Excel worksheet. I’m
attempting to somewhat automate the process with Access. There are three

[quoted text clipped - 27 lines]

If you wish to post your effectiveness algorithm, we'll try to help compose a
Query to do so.