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 » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

multiple tables into one table



 
 
Thread Tools Display Modes
  #1  
Old November 28th, 2008, 11:46 PM posted to microsoft.public.access.tablesdbdesign
dragon
external usenet poster
 
Posts: 35
Default 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  
Old November 29th, 2008, 01:24 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old November 29th, 2008, 11:33 PM posted to microsoft.public.access.tablesdbdesign
Steve[_57_]
external usenet poster
 
Posts: 598
Default 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  
Old December 1st, 2008, 11:25 PM posted to microsoft.public.access.tablesdbdesign
dragon
external usenet poster
 
Posts: 35
Default 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  
Old December 2nd, 2008, 02:50 AM posted to microsoft.public.access.tablesdbdesign
Barrett
external usenet poster
 
Posts: 4
Default 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  
Old December 9th, 2008, 08:34 PM posted to microsoft.public.access.tablesdbdesign
dragon
external usenet poster
 
Posts: 35
Default 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

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


All times are GMT +1. The time now is 06:37 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.