Access 97 - Multiple Table Query Question

[Jeff Homan]

Hello all,

I have a problem with a query in Access 97, and I'll try to give you
all of the details to see if you have any idea if it's even possible:

I have 2 tables: Inventory and Pricing

The inventory table basically has a part number, and all of the part
details:

Part Number Part Description Weight Category
------------------------------------------------------------------------------------
000125465 Test Part 35 Muffler

I also have a pricing table that contains the part number, and
multiple pricing for that part such as:

Part Number Price Type Price
----------------------------------------------------------------
000125465 Sale 55.00
000125465 Retail 104.00
000125465 Web 49.00

What I'm trying to accomplish with a query is that I'd like all of
this data to come back in one row

Part Number - Part Description - Weight - Category - Sale Price (If it
exists) - Retail Price (if it exists) - Web Price (if it exists)

The problem that I have is that when I build a query I can only seem
to get one price and price-type from the pricing table, so when there
are 3 prices in the pricing table, I get 3 results returned in my
query.

If you have any ideas, or suggestions on where I might be able to find
a solution to this, I would really appreciate that.

Thank you,
Jeff Homan

www.jcswebhosting.com

 

[Allen Browne]

Use a crosstab query to turn the price types into column headings:

1. Create a query into to the Pricing table.

2. Change it to a Crosstab query (Crosstab on Query menu).

3. Drag Part Number into the grid.
In the Crosstab row under this field, choose Row Heading.

4. Drag the Price Type into the grid.
In the Crosstab row under this field choose Column Heading.

5. Drag the Price field into the grid.
In the Crosstab row, choose Value.
In the Total row, choose First.

6. Save the query. Close.

7. Create another query into the Inventory table, and also the query you
just saved.

8. In the upper pane of query design, you should see a line joining the
table and the query based on Part Number. If not, drag the one field onto
the other to create the join.

9. Drag the fields you want to see into the grid.


An alternative approach would be to use a series of subqueries.
Create a query into just the Inventory table.
In a fresh column of the Field row, enter a subquery to get the sale price:
SalePrice: (SELECT First(Price) FROM Pricing WHERE (Pricing.[Part
Number] = Inventory.[Part Number]) AND (Pricing.[Price Type] = 'Sale'))

 

[Jeff Homan]

Allen - Thank you for the amazingly quick answer - it completely did
the trick for what I needed.

The next hurdle I had is somewhat similar to my original question, but
it seems more open ended and less possible...

I have the same inventory table as before:

Part Number Part Description Weight Category
------------------------------------------------------------------------------------
000125465 Test Part 35 Muffler

But now I have an Application table with a 1 to many relationship
between it and the inventory table based on the part number.

Part Number Vehicle Description
-------------------------------------------------------------------------
000125465 GXVT VIN Split
000125465 CM43 90-94 Turbo
000125465 A124 93-97 Acura

The problem here, is that there can be virtually unlimited number of
rows for each part number in the Application table.

What I'm trying to accomplish with a query is that I'd like all of
this data to come back in one row

Part Number - Part Description - Weight - Category - Vehicle
Application1 - Vehicle Application2 - Vehicle Application 3....

It looks to me as if what I'll have to do is hard code about 50 - 100
spots where it can check to see if the first vehicle application
exists, the second... down to the hundredth. If there's only 2 then it
just leaves the next 98 blank.

Does this seem like the only solution to this?

Thank you,
Jeff Homan

JCS Web Hosting
www.jcswebhosting.com

 

[Allen Browne]

Hmm. That would be a rather unweildy interface, and could potentially get
wider than 255 fields or 2000 characters quite easily. Impractical.

In the Application table, one Vehicle could appear many times? Seems to me
you need a Vehicle table, so that Application becomes a junction table
between Parts and Vehicles. You could then create a report that displays a
more conventional layout, e.g. grouping by Vehicle, and listing the parts
under it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen - Thank you for the amazingly quick answer - it completely did
the trick for what I needed.

The next hurdle I had is somewhat similar to my original question, but
it seems more open ended and less possible...

I have the same inventory table as before:

Part Number Part Description Weight Category
------------------------------------------------------------------------------------
000125465 Test Part 35 Muffler

But now I have an Application table with a 1 to many relationship
between it and the inventory table based on the part number.

Part Number Vehicle Description
-------------------------------------------------------------------------
000125465 GXVT VIN Split
000125465 CM43 90-94 Turbo
000125465 A124 93-97 Acura

The problem here, is that there can be virtually unlimited number of
rows for each part number in the Application table.

What I'm trying to accomplish with a query is that I'd like all of
this data to come back in one row

Part Number - Part Description - Weight - Category - Vehicle
Application1 - Vehicle Application2 - Vehicle Application 3....

It looks to me as if what I'll have to do is hard code about 50 - 100
spots where it can check to see if the first vehicle application
exists, the second... down to the hundredth. If there's only 2 then it
just leaves the next 98 blank.

Does this seem like the only solution to this?

Thank you,
Jeff Homan

JCS Web Hosting
www.jcswebhosting.com

Use a crosstab query to turn the price types into column headings:

1. Create a query into to the Pricing table.

2. Change it to a Crosstab query (Crosstab on Query menu).

3. Drag Part Number into the grid.
In the Crosstab row under this field, choose Row Heading.

4. Drag the Price Type into the grid.
In the Crosstab row under this field choose Column Heading.

5. Drag the Price field into the grid.
In the Crosstab row, choose Value.
In the Total row, choose First.

6. Save the query. Close.

7. Create another query into the Inventory table, and also the query you
just saved.

8. In the upper pane of query design, you should see a line joining the
table and the query based on Part Number. If not, drag the one field onto
the other to create the join.

9. Drag the fields you want to see into the grid.


An alternative approach would be to use a series of subqueries.
Create a query into just the Inventory table.
In a fresh column of the Field row, enter a subquery to get the sale
price:
SalePrice: (SELECT First(Price) FROM Pricing WHERE (Pricing.[Part
Number] = Inventory.[Part Number]) AND (Pricing.[Price Type] = 'Sale'))

 

[Jeff Homan]

Allen - Once again, thank you for the reply and suggestions.

To give you more information about what I'm trying to do is migrate a
current inventory list to an online store. The best way that I can see
to do this is run a query on the existing database with all
information in a single query-result-table, and then I can add that to
the online store.... The issues that have come up have been how to
pull the info from the inventory table and 2 one to many tables all in
one query. Allen, you've helped me with the 1st one, this last
applications table is the next place I'm stumped.

But, I spoke with the company I'm tinkering on this for, and they said
that we could use a max of 25 applications. Is there a way to use an
aggregate function for this to accomplish the task. Such as this:

VehicleApp1: (SELECT First(Vehicle, Description) FROM Applications
WHERE(Applications.[Part Number] = Inventory.[Part Number])

VehicleApp2: (SELECT Second(Vehicle, Description) FROM Applications
WHERE(Applications.[Part Number] = Inventory.[Part Number])

There doesn't seem to be a "Second" function like there is a "First" -
What would be the best way to pull that information?

Thank you,
Jeff

 

[Allen Browne]

Hi Jeff

Nothing that wide is going to work. It will be too wide. Rethink the
strategy.

I'm not clear what kind of software you are attempting to load this into on
the other end, but the most logical approach to me would be a simple query
with the 2 tables linked, as the source for a report. If you want it on
paper, you could even start a new page for each vehicle.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen - Once again, thank you for the reply and suggestions.

To give you more information about what I'm trying to do is migrate a
current inventory list to an online store. The best way that I can see
to do this is run a query on the existing database with all
information in a single query-result-table, and then I can add that to
the online store.... The issues that have come up have been how to
pull the info from the inventory table and 2 one to many tables all in
one query. Allen, you've helped me with the 1st one, this last
applications table is the next place I'm stumped.

But, I spoke with the company I'm tinkering on this for, and they said
that we could use a max of 25 applications. Is there a way to use an
aggregate function for this to accomplish the task. Such as this:

VehicleApp1: (SELECT First(Vehicle, Description) FROM Applications
WHERE(Applications.[Part Number] = Inventory.[Part Number])

VehicleApp2: (SELECT Second(Vehicle, Description) FROM Applications
WHERE(Applications.[Part Number] = Inventory.[Part Number])

There doesn't seem to be a "Second" function like there is a "First" -
What would be the best way to pull that information?

Thank you,
Jeff

Hmm. That would be a rather unweildy interface, and could potentially get
wider than 255 fields or 2000 characters quite easily. Impractical.

In the Application table, one Vehicle could appear many times? Seems to me
you need a Vehicle table, so that Application becomes a junction table
between Parts and Vehicles. You could then create a report that displays a
more conventional layout, e.g. grouping by Vehicle, and listing the parts
under it.

 

[Jeff Homan]

Is there anyway to do this if there's a limit of 6 fields? I don't
know a ton about databases, but the only way for us to get this to
work is to have all the data in one query results table to migrate
this all to another database (where it is then put into multiple
tables (essentially rebuilt)).

Basically the problem that I'm having now, is that I've got a table
that has 6 fields in a 1 to many relationship with a part number
table.

Is there a way to use an aggregate function for this to accomplish the
task. Such as this:

VehicleApp1: (SELECT First(Vehicle, Description) FROM Applications
WHERE(Applications.[Part Number] = Inventory.[Part Number])

VehicleApp2: (SELECT Second(Vehicle, Description) FROM Applications
WHERE(Applications.[Part Number] = Inventory.[Part Number])

There doesn't seem to be a "Second" function like there is a "First" -
What would be the best way to pull that information?

Here's the Product Table

Part Number Description ....
-------------------------------------------------
ABC123 Test Part
ABC124 Test Part 2

Here's the Applications Table

Part Number AppCode Application
------------------------------------------------------------------------
ABC123 ZZZ Test APP
ABC123 ZZZ Test App 2
(to a max of 6 of these entries)

Is there any way to make a query that will result in this:

Part Number - Description - AppCode1 - Application1 - AppCode2 -
Application2 - AppCode3 - Application3... (up to 6)

Thank you for your help!
Jeff

Hi Jeff

Nothing that wide is going to work. It will be too wide. Rethink the
strategy.

I'm not clear what kind of software you are attempting to load this into on
the other end, but the most logical approach to me would be a simple query
with the 2 tables linked, as the source for a report. If you want it on
paper, you could even start a new page for each vehicle.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen - Once again, thank you for the reply and suggestions.

To give you more information about what I'm trying to do is migrate a
current inventory list to an online store. The best way that I can see
to do this is run a query on the existing database with all
information in a single query-result-table, and then I can add that to
the online store.... The issues that have come up have been how to
pull the info from the inventory table and 2 one to many tables all in
one query. Allen, you've helped me with the 1st one, this last
applications table is the next place I'm stumped.

But, I spoke with the company I'm tinkering on this for, and they said
that we could use a max of 25 applications. Is there a way to use an
aggregate function for this to accomplish the task. Such as this:

VehicleApp1: (SELECT First(Vehicle, Description) FROM Applications
WHERE(Applications.[Part Number] = Inventory.[Part Number])

VehicleApp2: (SELECT Second(Vehicle, Description) FROM Applications
WHERE(Applications.[Part Number] = Inventory.[Part Number])

There doesn't seem to be a "Second" function like there is a "First" -
What would be the best way to pull that information?

Thank you,
Jeff

Hmm. That would be a rather unweildy interface, and could potentially get
wider than 255 fields or 2000 characters quite easily. Impractical.

In the Application table, one Vehicle could appear many times? Seems to me
you need a Vehicle table, so that Application becomes a junction table
between Parts and Vehicles. You could then create a report that displays a
more conventional layout, e.g. grouping by Vehicle, and listing the parts
under it.