Writing a Query to distinguish multiple entries

[paul29berks]

I am trying to write a query based on the following criteria:

* I am using 2 tables - Table 1 tells me standard discount rates by each product, Table 2 = actual discounts that have been processed.

* I have found for each product in the standard discount table there are several entries in the actual discounts tables.

Does anyone have any suggestions on how I can write a query which makes the following assumptions:

1) If more than 1 entry found take the lowest discount rate?

*2) If I have a standard discount rate of 25% for a limited period i.e a start date of 200101 and an end date of 20010301. How can I write a query that tells me if start date = x and end date = Y discount = Z?

Many Thanks

 

[Corticus]

I would use a grouping query.

Build a query on the table that shows actual discounts. Make it a grouping query (click the E looking icon, for summation). Add the field that has the product. Change the group by designation to 'count'. Add a criteria for this field of 2. Add the product field again, this time leave the group by designation set to 'group by'.

Now, add the field that has the discount amount. Set the group by designation for this field to 'min'.

The above should give a listing of all products in this table that are listed at least twice, and the lowest discount used.

Regarding your second question, where do you want this discount displayed, in a query? If so, just add an expression field, and use something like:
disc: IIf([date]>=#1/1/2004# And [date]<=#2/1/2004#,.25)

I notice your dates are in a strange format, that will probably need to be addressed if you have any problems. Use with the cdate() and format() functions if you need to reformat your date.