Code question

[CosmaL]

Hello my friends!

I've got a database which records incoming samples information.

Fields:
ID
Sample description
IncomeDate
Customer
Responsible.

After inserting the information, a report is generating and a message is ready to be sent to the responsible.
All the fields are included in the query (query by form) for the report generation, with criteria: Income Date and Responsible.
Income Date is used in case there are more than one samples in the same date.

My question/problem is the following:

In the email, i have a phrase "New incoming sample(s), from:"

After from i would like to have the customer.
If the samples are from the same customer its ok, but if there're more than one customers, i would like to have "from: Various customers".

How can i check if the customers in the query/report are more than one?

Thank you in advance!

Best Regards,
Costas

 

[JHB]

Use DISTINCT in a query:

SELECT DISTINCT YourCustomerFieldName
FROM YourQuery;

 

[gblack]

This is a long way to do it with just the GUI...

I'd create a query that groups
[Sample description] and [Responsible] (it's the same as distinct)

Save it and call it something (lets just say Q1)

Then I would pull Q1 into a new query and drag down the field [Sample description] Twice so :
[Sample description] , [Sample description]
Then group by the first (Sample description) field and count the second (Sample description) field... then I would filter the second field with this criteria: > 1

This will give you all the "Sample descriptions" that have multiple [Responsible]

Lastly you can save that query (call it Q2)... Make a new query... pull in your original table and Q2... then join [Sample description] from Q2 to your original table...

Post any of the fields from the original table.

This will show you all the records which have a [Sample description] with multiple [Responsible]

So all in all three queries.

GL

 

[CosmaL]

It's really a long way!

I'll give it a try and if it won't work, i'll write by hand "various customers".

Thanks!!!