Combining two tables

T

theseandavis

Hello,

I would like to bring two tables together.

The first table contains 5 columns. Let's call them:

Car Type || Job Type || FPA || Part Code || Part Description ||
Quantity

The second table contains 4 columns:

Car Type || Job Type || Part Code || Part Description


What I would like to do is use the first set of data as a base. It
lists every car type with every job type, and then three scenarios
(either F, P, or A[Forecast, Planned, Actual]) to show each Part Code
and Part Description, and the Quantity of Parts. For example:

Volvo || Brakes || F || 123 || Brake Pads || 4
Volvo || Brakes || P || 123 || Brake Pads || 4
Volvo || Brakes || A || 123 || Brake Pads || 0

.... and so on, with every part and part code needed for a Brake job.

Now this first table lists all parts directly associated with Job
Types. My second table contains all the auxillary parts for each job
type, which shift around somewhat, like Nuts and Washers, etc. For
example, it currently looks something like this:

Volvo || Brakes || 101 || Screw 1/4" || 10
Volvo || Brakes || 102 || Nut 1/4" ID || 10
Volvo || Brakes || 103 || Cotter Pin || 2

What I would like to see is the two tables merged together in such a
way that all parts for a Volvo Brake Job are listed for the Scenario of
F, P, and A.

Volvo || Brakes || F || 123 || Brake Pads || 4
Volvo || Brakes || P || 123 || Brake Pads || 4
Volvo || Brakes || A || 123 || Brake Pads || 0
Volvo || Brakes || F || 101 || Screw 1/4" || 10
Volvo || Brakes || P || 101 || Screw 1/4" || 10
Volvo || Brakes || A || 101 || Screw 1/4" || 10
Volvo || Brakes || F || 102 || Nut 1/4" ID || 10
Volvo || Brakes || P || 102 || Nut 1/4" ID || 10
Volvo || Brakes || A || 102 || Nut 1/4" ID || 10
Volvo || Brakes || F || 103 || Cotter Pin || 2
Volvo || Brakes || P || 103 || Cotter Pin || 2
Volvo || Brakes || A || 103 || Cotter Pin || 2

Is this possible? Once this is accomplished I will go through and turn
all 'A's quantity to 0, unless there is a way to do it in one of these
steps.

Thanks:)
 
J

Jeff Boyce

Are you saying that you want to append all the records from table2 into
table1?

If so, look into an append query.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
T

theseandavis

Append kind of works, so thanks, but I would ideally like to join it at
the same time to show the appended data in a forecast, Plan, Actual
form like the other data...

Jeff said:
Are you saying that you want to append all the records from table2 into
table1?

If so, look into an append query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,

I would like to bring two tables together.

The first table contains 5 columns. Let's call them:

Car Type || Job Type || FPA || Part Code || Part Description ||
Quantity

The second table contains 4 columns:

Car Type || Job Type || Part Code || Part Description


What I would like to do is use the first set of data as a base. It
lists every car type with every job type, and then three scenarios
(either F, P, or A[Forecast, Planned, Actual]) to show each Part Code
and Part Description, and the Quantity of Parts. For example:

Volvo || Brakes || F || 123 || Brake Pads || 4
Volvo || Brakes || P || 123 || Brake Pads || 4
Volvo || Brakes || A || 123 || Brake Pads || 0

... and so on, with every part and part code needed for a Brake job.

Now this first table lists all parts directly associated with Job
Types. My second table contains all the auxillary parts for each job
type, which shift around somewhat, like Nuts and Washers, etc. For
example, it currently looks something like this:

Volvo || Brakes || 101 || Screw 1/4" || 10
Volvo || Brakes || 102 || Nut 1/4" ID || 10
Volvo || Brakes || 103 || Cotter Pin || 2

What I would like to see is the two tables merged together in such a
way that all parts for a Volvo Brake Job are listed for the Scenario of
F, P, and A.

Volvo || Brakes || F || 123 || Brake Pads || 4
Volvo || Brakes || P || 123 || Brake Pads || 4
Volvo || Brakes || A || 123 || Brake Pads || 0
Volvo || Brakes || F || 101 || Screw 1/4" || 10
Volvo || Brakes || P || 101 || Screw 1/4" || 10
Volvo || Brakes || A || 101 || Screw 1/4" || 10
Volvo || Brakes || F || 102 || Nut 1/4" ID || 10
Volvo || Brakes || P || 102 || Nut 1/4" ID || 10
Volvo || Brakes || A || 102 || Nut 1/4" ID || 10
Volvo || Brakes || F || 103 || Cotter Pin || 2
Volvo || Brakes || P || 103 || Cotter Pin || 2
Volvo || Brakes || A || 103 || Cotter Pin || 2

Is this possible? Once this is accomplished I will go through and turn
all 'A's quantity to 0, unless there is a way to do it in one of these
steps.

Thanks:)
Click to expand...
Click to expand...
 
J

Jeff Boyce

If you need to keep track of which row is which kind (Plan/Actual), how
'bout adding a field that hold a code to indicate this?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Append kind of works, so thanks, but I would ideally like to join it at
the same time to show the appended data in a forecast, Plan, Actual
form like the other data...

Jeff said:
Are you saying that you want to append all the records from table2 into
table1?

If so, look into an append query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,

I would like to bring two tables together.

The first table contains 5 columns. Let's call them:

Car Type || Job Type || FPA || Part Code || Part Description ||
Quantity

The second table contains 4 columns:

Car Type || Job Type || Part Code || Part Description


What I would like to do is use the first set of data as a base. It
lists every car type with every job type, and then three scenarios
(either F, P, or A[Forecast, Planned, Actual]) to show each Part Code
and Part Description, and the Quantity of Parts. For example:

Volvo || Brakes || F || 123 || Brake Pads || 4
Volvo || Brakes || P || 123 || Brake Pads || 4
Volvo || Brakes || A || 123 || Brake Pads || 0

... and so on, with every part and part code needed for a Brake job.

Now this first table lists all parts directly associated with Job
Types. My second table contains all the auxillary parts for each job
type, which shift around somewhat, like Nuts and Washers, etc. For
example, it currently looks something like this:

Volvo || Brakes || 101 || Screw 1/4" || 10
Volvo || Brakes || 102 || Nut 1/4" ID || 10
Volvo || Brakes || 103 || Cotter Pin || 2

What I would like to see is the two tables merged together in such a
way that all parts for a Volvo Brake Job are listed for the Scenario of
F, P, and A.

Volvo || Brakes || F || 123 || Brake Pads || 4
Volvo || Brakes || P || 123 || Brake Pads || 4
Volvo || Brakes || A || 123 || Brake Pads || 0
Volvo || Brakes || F || 101 || Screw 1/4" || 10
Volvo || Brakes || P || 101 || Screw 1/4" || 10
Volvo || Brakes || A || 101 || Screw 1/4" || 10
Volvo || Brakes || F || 102 || Nut 1/4" ID || 10
Volvo || Brakes || P || 102 || Nut 1/4" ID || 10
Volvo || Brakes || A || 102 || Nut 1/4" ID || 10
Volvo || Brakes || F || 103 || Cotter Pin || 2
Volvo || Brakes || P || 103 || Cotter Pin || 2
Volvo || Brakes || A || 103 || Cotter Pin || 2

Is this possible? Once this is accomplished I will go through and turn
all 'A's quantity to 0, unless there is a way to do it in one of these
steps.

Thanks:)
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Duplicate entry query on two tables 2
Bringing together two sets of data 1
Conditional auto-filtering to summarize data 1
Please help (complex query) 8
Import Excel Spreadsheet - Move Data to the Correct Record 3
Combining Worksheets 5
How to avoid Duplicate values .. but want the first value to added in group totals 0
auto index 1

Top