A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

challenging query



 
 
Thread Tools Display Modes
  #1  
Old March 4th, 2010, 08:03 AM posted to microsoft.public.access.queries
Pellissier
external usenet poster
 
Posts: 2
Default challenging query

I have a table with a code field and a price field.
I need to find records where the code field data is the same (I did a
duplicate query)

Now I need to make a report that shows those duplicates but only if the
price field is different.

I am trying to show where there are duplicate codes that have different prices
--
Thank you

  #2  
Old March 4th, 2010, 08:32 AM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default challenging query

On Wed, 3 Mar 2010 23:03:01 -0800, Pellissier
wrote:

I have a table with a code field and a price field.
I need to find records where the code field data is the same (I did a
duplicate query)

Now I need to make a report that shows those duplicates but only if the
price field is different.

I am trying to show where there are duplicate codes that have different prices


A "Self Join" query is the ticket here. Add your table to the grid twice;
Access will alias the second instance by appending _1 to the name. Join
Tablename.Code to Tablename_1.Code; include both Price fields; and put a
criterion on Talbename_1.Price of

[Tablename].[Price]

You only need not - the reason is left as an exercise to the user.
--

John W. Vinson [MVP]
  #3  
Old March 4th, 2010, 08:44 AM posted to microsoft.public.access.queries
XPS350
external usenet poster
 
Posts: 69
Default challenging query

On 4 mrt, 08:32, John W. Vinson
wrote:
On Wed, 3 Mar 2010 23:03:01 -0800, Pellissier

wrote:
I have a table with a code field and a price field.
I need to find records where the code field data is the same (I did a
duplicate query)


Now I need to make a report that shows those duplicates but only if the
price field is different.


I am trying to show where there are duplicate codes that have different prices


A "Self Join" query is the ticket here. Add your table to the grid twice;
Access will alias the second instance by appending _1 to the name. Join
Tablename.Code to Tablename_1.Code; include both Price fields; and put a
criterion on Talbename_1.Price of

[Tablename].[Price]

You only need not - the reason is left as an exercise to the user.
--

* * * * * * *John W. Vinson [MVP]


Does “duplicate” means that a code has a maximum of two occurrences?
If not what to do with code/price combinations like:
A / 1
A / 2
A / 2
(select A / 1 ?)

or

B / 1
B / 2
B / 1
B / 2
(select none)

A query that does it could look like:
SELECT Code, Price
FROM T1
GROUP BY Code, Price
HAVING Code In (SELECT Code FROM T1 GROUP BY Code HAVING
Count(Price)1) AND Count(Price)=1;



Groeten,

Peter
http://access.xps350.com
  #4  
Old March 4th, 2010, 02:58 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default challenging query

This query would identify all cases where code field had more than one price.

SELECT CodeField
FROM Table
GROUP BY CodeField
HAVING Min(Price) Max(Price)

So you could use something like the following to identify the records. The
only exceptions would be if you had one record for a code field with a price
and one or more records for that code field with no price (null).
SELECT *
FROM [SomeTable]
WHERE [CodeField] IN
(SELECT [CodeField]
FROM [SomeTable]
GROUP BY [CodeField]
HAVING Min([Price]) Max([Price]))

Post back if you need further help in building the query. If needed you can
handle nulls by modifying the having clause to read like the following.
HAVING Min(Nz(Price,0)) Max(Nz(Price,0))


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Pellissier wrote:
I have a table with a code field and a price field.
I need to find records where the code field data is the same (I did a
duplicate query)

Now I need to make a report that shows those duplicates but only if the
price field is different.

I am trying to show where there are duplicate codes that have different prices

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.