10 product free 1

[DuMoore]

Please help a non-technical guy out please.

I have been given the task of setting up an invoicing database for a new department within my company. I am not a database person at all. I haven't used access in 3 years or so. I have gotten the database to a usable point. Here is my problem:

The marketing department wants our customer to get every 10th product for free. How do I set up a counter to produce a "this is free" invoice for every 10th product the person buys.

The basic invoicing query works quite well...displaying all pertenant customer and product data in a report. I just can't figure out how to approach the counter. Thanks in advance.

 

[sillysod]

Are you sure that every 10th order line is free and not that cheapest of every 10 orders is given for free or something similar?

I ask because the first sounds like commercial suicide

Customers could just order 9 cheap items and then a very expensive item and get it for free

Can you expand on how the every tenth order thing works? is there a time period on it for example?

 

[DuMoore]

I am sure about the 10th order is free. All of the products are the same price. As this is a spin-off of our normal business, we are trying to market this department very strongly. Hence, 10th product free.

I do see your point however. A better way to say it is probably, $16 off your 10th order.

There is no time restraint.

Does that help?

 

[unclesvenno]

Hi DuMoore,

I'm no pro, in fact I've posted more questions than answers, but I thought you could have a field in the customer table which tallies the customer's orders. Each time an order is placed the tally is incremented. Then a query could be created selecting the tallied field, when the tally is multiple of 10, the invoice drawn up charges $0.00. If you use the modulus function ie.

var % 10

you'll get a result of 0 (zero) if the var value is a multiple of 10.

I hope this helps,
UncleSvenno

 

[Trendsetter]

you need to tie the Order table to the OrderItems table and then run a query on the CustomerID.
That way you will get a listing of all of the items for each customer.
Then do a count grouped on the CustomerID - this will give you the CustomerID with the number of items purchased.
Divide this by 10 and use the Mod() to see when his next Freebie is!

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[DuMoore]

Hey Everyone, thanks so much for your help. I'm going to try to hack through this stuff tonight. Thanks again!

 

[DuMoore]

OK. I've got the query running.

I'm not sure exactly how to group by count. I've hit the Sigma Icon on the tool bar and set "total" to "count". When I run the query it returns a 1 in "CountCustomerID" field in each record.

 

[Trendsetter]

How many records per customer do you get when you do not group them???

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[DuMoore]

2 records for one customer, 1 for another customer. Sorry for the lack of data, this is obviously just enough to test things out.

 

[Trendsetter]

Whenyou apply the group on CustID and count on the items - do you get the correct numbers ie; 2 and 1???

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[DuMoore]

Yes...it worked. Thank you very much Trendsetter. I really appreciate it.

 

[DuMoore]

For some reason, the query is returning the number of Products rather than the number of orders even though I am counting orderID. Any thoughts?

 

[PHV]

JetSQL doesn't admit Count(Distinct) syntax.
Can you please post the SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DuMoore]

PHV,

I actually queried a different table and got the correct results. Thanks for trying to help me though.

 

[DuMoore]

How exactly do I use mod()?

Office help has no answers, and google doesn't return anything useful.

 

[Trendsetter]

This example uses the Mod operator to divide two numbers and return only the remainder. If either number is a floating-point number, it is first rounded to an integer.

Dim MyResult
MyResult = 10 Mod 5 ' Returns 0.
MyResult = 10 Mod 3 ' Returns 1.
MyResult = 12 Mod 4.3 ' Returns 0.
MyResult = 12.6 Mod 5 ' Returns 3.

So in your case you would count the total number of products purchased which would be the first number and then use mod 10 - MyResult would be the number left over...

Dim TotalItems as Integer
TotalItems = 27
MyResult = TotalItems Mod 10
MyResult = 7

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[DuMoore]

I agree with all of that. However can I use mod on a number that is returned from a query? As we talked about yesterday, I return the field CountOrderID from the order table sorted by Customer ID.

Is this correct?
Expr1: [CountOrderID] mod 10

When i run this query the CountOrderID returns the number 3 and Expr1: returns 3.

 

[Trendsetter]

3 is correct! If you divide 3 by 10 you get 0 with 3 left over.
In place of Expr1: put something meaningful like ItemsBought:[CountOrderID] mod 10


Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk