Subquery problem

[Alex]

Hi Anyone,

I have, I guess, a simple question, but, I have no answer
for it myself, (I am a beginner). May be you can help.

Two tables: Customers, Orders.
I need to see the customers who placed an order within
some time (month, week).
But in the form I need only diplay the Customers not date,
nothing from Order table. And NO DUPLICATES!
This is what i did:
Created a query for the form, based on table Customers,
where in the Criteria for CustomerID, I put a subquery:
(SELECT [CustomersID] FROM Orders WHERE [DayOrder] = some
period of time)

I run the query, and it says, "The most I can give is just
One Record"..Is this true? And no query runs.
The other way, to include OrderDate from Orders table in
query, gives me too many records, being different only on
OrderDate. What I need just the names of the customers who
made orders withing some time.
Does a form have a method that allows me to set "no
duplicates to display"?

Sounds Simple?
Will appreciate your help,
Thanks.

 

[Nikos Yannacopoulos]

Alex,

Your subquery statement should be preceded by an "In", i.e. the criterion
should read:
In (SELECT [CustomersID] FROM Orders WHERE [DayOrder] = some period of time)
plus the query should be a Totals query, with a Group By totals function on
the CustomerID field.

Your other approach (joined tables in the query) will also work fine if you
change it to a Totals query again, with a Group Bu on the CustomerID field
and a Where on the DayOrder field (so the condition is applied but the field
values are not returned).

HTH,
Nikos

 

[Guest]

Thank you, Nikos.

-----Original Message-----
Alex,

Your subquery statement should be preceded by an "In", i.e. the criterion
should read:
In (SELECT [CustomersID] FROM Orders WHERE [DayOrder] = some period of time)
plus the query should be a Totals query, with a Group By totals function on
the CustomerID field.

Your other approach (joined tables in the query) will also work fine if you
change it to a Totals query again, with a Group Bu on the CustomerID field
and a Where on the DayOrder field (so the condition is applied but the field
values are not returned).

HTH,
Nikos

Hi Anyone,

I have, I guess, a simple question, but, I have no answer
for it myself, (I am a beginner). May be you can help.

Two tables: Customers, Orders.
I need to see the customers who placed an order within
some time (month, week).
But in the form I need only diplay the Customers not date,
nothing from Order table. And NO DUPLICATES!
This is what i did:
Created a query for the form, based on table Customers,
where in the Criteria for CustomerID, I put a subquery:
(SELECT [CustomersID] FROM Orders WHERE [DayOrder] = some
period of time)

I run the query, and it says, "The most I can give is just
One Record"..Is this true? And no query runs.
The other way, to include OrderDate from Orders table in
query, gives me too many records, being different only on
OrderDate. What I need just the names of the customers who
made orders withing some time.
Does a form have a method that allows me to set "no
duplicates to display"?

Sounds Simple?
Will appreciate your help,
Thanks.

.