MS Access '97 Query / Data Question

[warpdrve]

I posted this in the MS Office Group but will also post here. Hope someone can help ...

This is a 2-part question:

1. I have a series of data. For simplicity, let's assume it's a list of part numbers and locations stored. However, some pn's have multiple locations so they have multiple records. I need to construct a query which will provide me with a single record of a part number and multiple additional COLUMNS for each of the multiple locations. I'm thinking this is a crosstab query but can't quite figure it out. [If you reply to me via email, I might be able to construct a simple table as an example, and what I want to see as the result].

2. I am running Office XP on my office system along with Access '97 (installed in a separate directory). It's actually Office XP Pro but since we use Access '97 extensively and Access XP isn't downward compatible, I omitted the Access from the XP install and installed '97. My question is: Can I install Access XP on the same system and have both versions available to me (of course, keeping separate database files depending on the version)?

Thanks in advance for any assistance you can provide.

Regards,

/michael/

 

[raskew]

Michael-

In response to Part 1, you're on the right track.
The easiest way is with a crosstab query.

Try this example using Northwind's Order Details table.

We'll convert field [ProductID] to [PartID], and [OrderID] will be the location.

The end result will be that you'll be able to see where each of your products (PartID) has gone in terms of Orders (Locations).

The first query (Query16) will look like this:

SELECT [Order Details].ProductID AS PartNum, [Order Details].OrderID AS Location, [Order Details].Quantity
FROM [Order Details]
WHERE ((([Order Details].OrderID)<10260));

I limited it to just a few order numbers for the sake of testing.

The crosstab query (Query16_Crosstab) will use Query16 as it's source, and will look like this:

TRANSFORM Sum(Query16.Quantity) AS [The Value]
SELECT Query16.PartNum, Sum(Query16.Quantity) AS [Total Of Quantity]
FROM Query16
GROUP BY Query16.PartNum
PIVOT Query16.Location;

Give it a try (just have to copy the SQL into two new queries in Northwind) and see if that's what you're after.

Bob