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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
multiple tables into one table
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 a 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 a 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 |
#2
|
|||
|
|||
multiple tables into one table
On Fri, 28 Nov 2008 14:46:01 -0800, Dragon
wrote: 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 a 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 a 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. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
multiple tables into one table
It sounds like you need the following tables:
TblCarrier CarrierID Carriername Contact fields if needed TblFailReason FailReasonID FailReason TblShip ShipID CarrierID ShipDate UnitNum TrailerNum LoadUnLoad (Yes/No) PassFail (Yes/No) FailReasonID If you ship 1000 trailers each month, you would enter 12,000 records in TblShip a year. You would be good to go for a lifetime without worrying about room in the database!! For carrier effectiveness, you would query TblShip counting the total shipments by each carrier and counting the "Fails" of each carrier. Steve "Dragon" wrote in message ... 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 a 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 a 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 |
#4
|
|||
|
|||
multiple tables into one table
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 "John W. Vinson" wrote: On Fri, 28 Nov 2008 14:46:01 -0800, Dragon wrote: 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 a 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 a 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. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
multiple tables into one table
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. Dragon wrote: 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. |
#6
|
|||
|
|||
multiple tables into one table
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 "Barrett" wrote: 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. Dragon wrote: 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. |
Thread Tools | |
Display Modes | |
|
|