GroupBY problem with query

[gypsyman]

I have a GROUPBY function that is Grouping all items by upc and if there is a
duplicate in the upc then it is showing the MINimum price in Access 2000.
Here was what it looks like, I call it the "GroupBy" query:

SELECT unionquery.upc, Min(unionquery.price) AS MinOfprice
FROM unionquery
GROUP BY unionquery.upc;

unionquery is the query I am selecting this information from. Inside union
query is 4 other fields, id, price, category and qty. My problem is adding
these fields to the "GROUPBY" query that is shown bove.

I cant seem to get them into the above query becuase every field seems to
require an aggregrate function and those four other fields all I want is
their info I dont want anything to change with them.

Ive tried to create another query by linking to the above "GroupBY" query,
but when I do this all I ever get is all of the fields like the "GroupBy"
query was never even run.

Anyone having any suggestions is greatly appreciated. I must say Im at a loss.

Thanks!

 

[KARL DEWEY]

id and category should be no problem. You already have price. Do you want
to sum the qty?

SELECT unionquery.upc, unionquery.category, Min(unionquery.price) AS
MinOfprice, Sum(unionquery.qty) AS TOTAL
FROM unionquery
GROUP BY unionquery.upc, unionquery.category;

 

[John Vinson]

I have a GROUPBY function that is Grouping all items by upc and if there is a
duplicate in the upc then it is showing the MINimum price in Access 2000.
Here was what it looks like, I call it the "GroupBy" query:

SELECT unionquery.upc, Min(unionquery.price) AS MinOfprice
FROM unionquery
GROUP BY unionquery.upc;

unionquery is the query I am selecting this information from. Inside union
query is 4 other fields, id, price, category and qty. My problem is adding
these fields to the "GROUPBY" query that is shown bove.

I cant seem to get them into the above query becuase every field seems to
require an aggregrate function and those four other fields all I want is
their info I dont want anything to change with them.

So if you have 209 records all with the same value of upc, all with
(potentially) different ID, Category, and Qty values, what DO you want
to see? All 209 values? An arbitrary value?

You're using an aggregate query, and aggregating multiple records. As
such, you have to specify what you want to see for each field - an
aggregate value, or else you need to group by that field.

John W. Vinson[MVP]

 

[gypsyman]

Thank you all for your replies.

Say I have 500 items from the union query 100 of those 500 are excalty the
same to another 100 items. I still want to see all five hundred items and the
100 that are the same I only want to see the lowest priced item.

So if UPC's were the identifier that were the same I assume I would groupby
those and then tell it I only want to see the minimum price.

When I run the groupby upc and MIn price query everything works fine. It
shows non duplicating items and the ones that are duplicates it only shows
the one with the lower price. However from the unionquery that I am grouping
there are 4 other fields I need to see. Once I specify a GROUPBY function for
those items it messes up my query and instead shows all items including
duplicates.

So if I just GROUPBY upc and only show the MIn price of duplicates how am I
suppesed to show those other 4 fields without screwing up the query?

Thanks again all!

 

[gypsyman]

id and category should be no problem. You already have price. Do you want
to sum the qty?

SELECT unionquery.upc, unionquery.category, Min(unionquery.price) AS
MinOfprice, Sum(unionquery.qty) AS TOTAL
FROM unionquery
GROUP BY unionquery.upc, unionquery.category;

Thank you for your reply.

I do not want to SUM the qty. So if I have 3 items and 2 duplicate
eachother, I only want to show 2 items and the one that was duplicated I only
want to show the one that had the lower price and I want to pull the
accompanying data with it with out SUM. So the duplicating data should pull
the data with it independate of the higher priced duplicate.

I have the following "new" statement that is what I would think would look
like what I want:


SELECT unionquery.upc, unionquery.salescat, unionquery.wmsid,
unionquery.qavl, Min(unionquery.price) AS MinOfprice
FROM unionquery
GROUP BY unionquery.upc, unionquery.salescat, unionquery.wmsid,
unionquery.qavl;


However, when it runs it does not pull MIN price it pulls all items
inclduing the higher priced duplicate.

To me it really looks like it would Group all items together and only show
the MIN price still. But the MIN price is just not working.

Any thoughts?

Thanks!

 

[John Vinson]

Say I have 500 items from the union query 100 of those 500 are excalty the
same to another 100 items. I still want to see all five hundred items and the
100 that are the same I only want to see the lowest priced item.

Ah. So you want to see the lowest price 500 times (regardless of the
original price in that record?)

If so, don't use a Totals query at all. Instead, use DMin() in a
calculated field to find the minimum for that item code.

John W. Vinson[MVP]

 

[gypsyman]

Ah. So you want to see the lowest price 500 times (regardless of the
original price in that record?)

If so, don't use a Totals query at all. Instead, use DMin() in a
calculated field to find the minimum for that item code.

John W. Vinson[MVP]

I was hoping you could elaborate on this a little more.

So I run the unionquery to join my items then I run another query with the
union querys fields and add DMin() in a calcualted field? Im not sure where I
add the DMIN() I tried to put it in the criteria section of a select query
with no luck.

Sorry Im a bit of a newby.

Thanks.

 

[John Vinson]

So I run the unionquery to join my items then I run another query with the
union querys fields and add DMin() in a calcualted field? Im not sure where I
add the DMIN() I tried to put it in the criteria section of a select query
with no luck.

The criteria would filter the records by the value of the DSum... NOT
what you want!

Include

DMin("[price]","unionqueryname","[ID] = " & [ID])

in each SELECT clause of your UNION query. It'll be slow.

Alternatively - save your Totals query, and JOIN it to the Union query
by ID. That may well be a lot faster!

John W. Vinson[MVP]

 

[gypsyman]

So I run the unionquery to join my items then I run another query with the
union querys fields and add DMin() in a calcualted field? Im not sure where I
add the DMIN() I tried to put it in the criteria section of a select query
with no luck.

The criteria would filter the records by the value of the DSum... NOT
what you want!

Include

DMin("[price]","unionqueryname","[ID] = " & [ID])

in each SELECT clause of your UNION query. It'll be slow.

Alternatively - save your Totals query, and JOIN it to the Union query
by ID. That may well be a lot faster!

John W. Vinson[MVP]

Okay I ran the code you gave me where unionquery is the name of the query
that is actually having the code put in itand query 1 and query 2 are the two
queries I am doing the UNION on. Also you can see my fields in the brackets.
Here was what the code looks like:

SELECT DMin("[price]","unionquery","[upc] = " & [upc]),
DMin("[price]","unionquery","[qavl] = " & [qavl]),
DMin("[price]","unionquery","[id] = " & [id]),
DMin("[price]","unionquery","[salescat] = " & [salescat])
from [query1]

UNION ALL SELECT DMin("[price]","unionquery","[upc] = " & [upc]),
DMin("[price]","unionquery","[qavl] = " & [qavl]),
DMin("[price]","unionquery","[id] = " & [id]),
DMin("[price]","unionquery","[salescat] = " & [salescat])
from [query2];

After it runs for about 20 seconds I get the following message:

"Data type mismatch in criteria expression"

I click okay and get this message:

"unknown"

A little wierd.

 

[Lotto]

So I run the unionquery to join my items then I run another query with the
union querys fields and add DMin() in a calcualted field? Im not sure where I
add the DMIN() I tried to put it in the criteria section of a select query
with no luck.

The criteria would filter the records by the value of the DSum... NOT
what you want!

Include

DMin("[price]","unionqueryname","[ID] = " & [ID])

in each SELECT clause of your UNION query. It'll be slow.

Alternatively - save your Totals query, and JOIN it to the Union query
by ID. That may well be a lot faster!

John W. Vinson[MVP]

Okay I ran the code you gave me where unionquery is the name of the query
that is actually having the code put in itand query 1 and query 2 are the two
queries I am doing the UNION on. Also you can see my fields in the brackets.
Here was what the code looks like:

SELECT DMin("[price]","unionquery","[upc] = " & [upc]),
DMin("[price]","unionquery","[qavl] = " & [qavl]),
DMin("[price]","unionquery","[id] = " & [id]),
DMin("[price]","unionquery","[salescat] = " & [salescat])
from [query1]

UNION ALL SELECT DMin("[price]","unionquery","[upc] = " & [upc]),
DMin("[price]","unionquery","[qavl] = " & [qavl]),
DMin("[price]","unionquery","[id] = " & [id]),
DMin("[price]","unionquery","[salescat] = " & [salescat])
from [query2];

After it runs for about 20 seconds I get the following message:

"Data type mismatch in criteria expression"

I click okay and get this message:

"unknown"

A little wierd.

I agree with the statement above -
Alternatively - save your Totals query, and JOIN it to the Union query
by ID. That may well be a lot faster!

 

[John Vinson]

Okay I ran the code you gave me where unionquery is the name of the query
that is actually having the code put in itand query 1 and query 2 are the two
queries I am doing the UNION on. Also you can see my fields in the brackets.
Here was what the code looks like:

SELECT DMin("[price]","unionquery","[upc] = " & [upc]),
DMin("[price]","unionquery","[qavl] = " & [qavl]),
DMin("[price]","unionquery","[id] = " & [id]),
DMin("[price]","unionquery","[salescat] = " & [salescat])
from [query1]

This bears absolutely no resemblance to anything I suggested... <g>

Go with the Join. It'll work much better; forget about DMin.

John W. Vinson[MVP]