Query Criteria

E

Erika

I have a table that lists account number and product number. I need to
create a query that shows me all of the accounts numbers that purchase
product number 590 and 710.

I thought this would be an easy task but I am really struggling.
 
O

Ofer

Im not sure if you want between the two numbers or just the two numbers
In any case
Between
Select [account number], [product number] from TableName Where [product
number] between 590 and 710

Just the two numbers
Select [account number], [product number] from TableName Where [product
number] In (590,710)
===================================================

If the Product number type is string and not number add quote before and
after the product number
Just the two numbers:
Select [account number], [product number] from TableName Where [product
number] In ("590","710")

Between
Select [account number], [product number] from TableName Where
cdbl([product number]) between 590 and 710
 
J

John Spencer (MVP)

One way to get those that have purchased both is the following.

SELECT *
FROM YourTable
WHERE [product number] = 590 AND
EXISTS (SELECT *
FROM YourTable as Temp
WHERE Temp.[Account Number] = YourTable.[Account Number]
and Temp.[product number] = 710)

Another way, would be to build two queries

Save the following as QueryA
SELECT Distinct [Account Number], [Product Number]
FROM YourTable
WHERE [Product Number] in (590,710)

Use that saved query as the source for a new query.
SELECT [Account Number]
FROM QueryA
GROUP BY [Account Number]
HAVING Count([Product Number]) = 2
 
E

Erika

First I have to apologize for my lack of knowledge in Access - I am still not
getting the query to work - I am getting an error message. I did try both of
your suggestions. Any ideas?

I am putting the text you listed in the criteria field - is that incorrect?

John Spencer (MVP) said:
One way to get those that have purchased both is the following.

SELECT *
FROM YourTable
WHERE [product number] = 590 AND
EXISTS (SELECT *
FROM YourTable as Temp
WHERE Temp.[Account Number] = YourTable.[Account Number]
and Temp.[product number] = 710)

Another way, would be to build two queries

Save the following as QueryA
SELECT Distinct [Account Number], [Product Number]
FROM YourTable
WHERE [Product Number] in (590,710)

Use that saved query as the source for a new query.
SELECT [Account Number]
FROM QueryA
GROUP BY [Account Number]
HAVING Count([Product Number]) = 2

I have a table that lists account number and product number. I need to
create a query that shows me all of the accounts numbers that purchase
product number 590 and 710.

I thought this would be an easy task but I am really struggling.
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

If you are using the query grid -

Field: [Product Number]
Criteria: 590

Field: Is710: Exists(SELECT * FROM YourTable as Temp WHERE Temp.[Account Number]
= [YourTable].[Account Number] and Temp.[Product Number] = 710)
Criteria: True

If that doesn't work, post the sql that is failing AND the error message you are
getting.

(Possibly unneeded instructions follow)
Open the query in design mode
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

This might give us a hint in where we are mis-advising you or where the problem
in communication lies.
 
E

Eric D via AccessMonster.com

This is a simple Select Query

Open a new Query, based on the table you are working with.... do you have
more than one table here?
Double click on the Account number to add it to the query.
Double click on the Product number to add it to the query.
In the Criteria field under Product number, enter 590 or 710 (exactly
as I have it here)
Run the query.

The results are: One column showing you each account and one column listing
either 510 or 790 as the product number. If you want to see the number of
each product number that each account purchased, modify your query - double
click on Product number (again) to add Product Number to the query. Click on
the Totals symbol (tool bar). This adds a Totals fied to each field in your
query. In the field you just added (2nd Product number), enter Count. Run
your query again. You will now see how many times each account purchased each
product number.

When you create the query, based on the current table, Product number will
take on the attributes of your table - in other words, you don't have to put
the numbers in quotes - Access will do this for you, but it's something you
need to be aware of in the future. If you create your query, then modify the
table structure, Access will give you an error when you next run the query
because Access does not update the query for you.
 
E

Eric D via AccessMonster.com

Erika,

Clarification:

To see the Totals - Add 'Count' to the 'Totals' field after clicking on the
Totals button in the tool bar. This field is a dropdown and you can select
Count over typing it in.

Wanted to make sure I was clear on this.
 
Top