Split a Table

[jndickin]

I'm using Access 2003.

I have the following table (example) -

Ln Class Price
01 10001 10
02 10001 10
03 10001 10
04 10002 10
05 10002 20
06 10002 20
07 10003 10
08 10003 20
09 10003 30

I would like records where the Class contains pricing that is ALL equal on
one table and records where the Class contains pricing that is NOT ALL equal
on another table. In this case I'd get lines 01 - 03 on one table and 04-09
on the other. Can this be done with a query of some sort?

Thanks!

 

[Ken Snell \(MVP\)]

I assume you want two separate queries, one for each scenario?

Assuming the answer is yes, build these sets of queries to give you the
results where pricing is all equal:

1st query -- name it qryCountAll:

SELECT Tablename.Class, Count(Tablename.Price) AS CountAll
FROM Tablename
GROUP BY Tablename.Class;


2nd query -- name it qryMinPrice:

SELECT Tablename.Class, Min(Tablename.Price) AS MinPrice
FROM Tablename
GROUP BY Tablename.Class;


3rd query -- name it qryCountMin:

SELECT Tablename.Class, Count(Tablename.Price) AS CountMin
FROM Tablename
INNER JOIN qryMinPrice
ON Tablename.Price = qryMinPrice.MinPrice;


4th query -- name it qryClassWithSameAllPrice (this is the query that gives
you the final answer you seek):

SELECT Tablename.Class, Tablename.Price
FROM (Tablename
INNER JOIN qryCountAll
ON Tablename.Class = qryCountAll.Class)
INNER JOIN qryCountMin
ON qryCountAll.CountAll = qryCountMin.CountMin
AND qryCountAll.Class = qryCountMin.Class;



Then to get the answer for where the prices are not all the same, build this
5th query -- name it qryClassWithNotSameAllPrice:

SELECT Tablename.Class, Tablename.Price
FROM (Tablename
INNER JOIN qryCountAll
ON Tablename.Class = qryCountAll.Class)
LEFT JOIN qryCountMin
ON qryCountAll.CountAll = qryCountMin.CountMin
AND qryCountAll.Class = qryCountMin.Class
WHERE qryCountMin.Class IS NULL;

 

[John Spencer]

To get the records where the price is all the same, use the following query.
SELECT Y.*
FROM YourTable as Y INNER JOIN
(SELECT Class, Min(Price)= Max(Price) as AllSame
FROM YourTable
GROUP BY Class) as Q
On YourTable.Class = Q.Class
WHERE AllSame = TRUE

Change true to false where there is a variance. To get all the records in one
query drop the Where clause and add AllSame to the list of fields that will be
returned.

If your table names and field names contain characters other than letters,
Numbers, and the underscore character, you will probably not be able to do the
above in one step. You will need a two-query solution.

First query:
SELECT Class, Min(Price)= Max(Price) as AllSame
FROM YourTable
GROUP BY Class

Second query: Join this query to the table on the Class field.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County