MYSQL Query

[Paul Carruthers]

I have a database which contains sales. Each record is a sale by a customer
on a particular date. The sales table lists the records from oldest to newest.
I am wondering if there is a query I could use where if I typed in a date
range such as 11/05/04 - 11/06/04 it would in turn take each day in the range
and count how many occurances of the date there where in the sales table.
Such as if I typed in occurances between 11/05/04 - 11/06/04 it would list a
report with the following:

11/05/04 - 23 sales that day
12/05/04 - 21 sales that day
etc etc

The name of the table is called
Order and the cell which contains the dates is called Date Ordered. A simple
like.... query works just for looking at one date within the table.

So Far I have been recommended this as my MYSQL query:

SELECT Order.[Date Ordered], Count(*) AS Sales
FROM [Order]
GROUP BY Order.[Date Ordered]
HAVING Order.[Date Ordered] Between #11/05/2004# And #11/06/2004#;

However this does not work.

Can anyone shine any light on this please.

Paul Carruthers

 

[Douglas J. Steele]

It isn't apparent from your post, but I'm assuming that you see 11/05/04 -
11/06/04 as meaning a month's worth of sales (from May 11th to June 11th
this year).

Regardless of what your Regional Settings have set the short date format to,
Access will not recognize 11/05/04 as the 11th of May, so your query, as
written, is going to show you how many sales you made on the 5th of November
of this year. Since that date hasn't happened yet, it's not surprising
you're not getting anything.

You must use an unambiguous format (such as yyyy-mm-dd or dd mmm yyyy), or
mm/dd/yyyy. You can use the Format statement to change the format for you.

You may find it useful to read Allen Browne's "International Dates in
Access" at http://members.iinet.net.au/~allenbrowne/ser-36.html and/or what
I have at
http://members.rogers.com/douglas.j.steele/SmartAccess.html

 

[Mike Painter]

I have a database which contains sales. Each record is a sale by a
customer on a particular date. The sales table lists the records from
oldest to newest. I am wondering if there is a query I could use
where if I typed in a date range such as 11/05/04 - 11/06/04 it would
in turn take each day in the range and count how many occurances of
the date there where in the sales table. Such as if I typed in
occurances between 11/05/04 - 11/06/04 it would list a report with
the following:

11/05/04 - 23 sales that day
12/05/04 - 21 sales that day
etc etc

The name of the table is called
Order and the cell which contains the dates is called Date Ordered. A
simple like.... query works just for looking at one date within the
table.

So Far I have been recommended this as my MYSQL query:

SELECT Order.[Date Ordered], Count(*) AS Sales
FROM [Order]
GROUP BY Order.[Date Ordered]
HAVING Order.[Date Ordered] Between #11/05/2004# And #11/06/2004#;

However this does not work.

How are you connecting to MySQL?
The square brackets may be an Access thing and I don't know if MySQL allows
spaces in field names. ( I don't think Access should allow them either.
finding out that my field is not the same as my field at 3:00 AM can be
frustrating at best.)

According to what I just looked up, spaces are not allowed in MySQL database
tables or field names and the examples that deal with dates use a single
quote.