Query problem for getting ages of users 1

[ts568]

Hi,

I need to try and write query which goes into my CUSTOMER table, and returns the number of male customers between the ages of 20 and 25.
The only thing in the CUSTOMER table which could help is DOB which is in the format 23/04/1975
There is also a field called Gender where you can either have an 'M' or 'F'.

Can anyone help? I can do the basic SQL quries, but this is a little too tricky.

I tried this: but it was not at the stage where it would return the number of customers, just the actual customers that met the parimiters of the query

SELECT title + firstName + lastName AS 'Customer'
FROM CUSTOMER
WHERE (((CUSTOMER.gender)="M"))
AND (((CUSTOMER.dob)= BETWEEN "01/01/1982" AND "01/01/1986"));

 

[JerryKlmns]

SELECT [title] & [firstName] & [lastName] AS 'Customer'
FROM CUSTOMER
WHERE gender='M' AND
dob BETWEEN #1982-01-01# AND #1986-01-01#

 

[ts568]

Thanks for that, it worked a treat, the only thing that is happening, is the title, firstName and lastName are all bunched into one,
So say I had Mr Joe Bloggs, I am getting MrJoeBloggs.
Is there any way to stop this? I tried putting in " " between
SELECT [title] & [firstName] & [lastName] AS 'Customer'

So it looked like this

SELECT [title] " " & [firstName] " " & [lastName] " " AS 'Customer'

But that wouldnt work.

Thanks again.

 

[ts568]

Also, the other problem, is that I need to return the number of customers in this age group too.

 

[JerryKlmns]

SELECT [title], [firstName], [lastName] AS Customer
FROM CUSTOMER
WHERE gender='M' AND
dob BETWEEN #1982-01-01# AND #1986-01-01#;

 

[JerryKlmns]

Would you give us an example of expected result?

Crystal Ball under maintenance for this afternoon...

 

[lespaul]

SELECT GENDER, COUNT(*) AS NumberOfCustomers FROM CUSTOMER WHERE Gender = 'M' and DOB BETWEEN #1982-01-01# AND #1986-01-01# GROUP BY GENDER

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[ts568]

An example would be,

Number of Customers 20-25 years old (field name)
3 (value)


If this is possible?


I could maybe do it as a seperate query from the one above.

 

[JerryKlmns]

SELECT [title] As CusGenter, [firstName] As CusFName, [lastName] AS CusLName
FROM CUSTOMER
WHERE gender='M' AND
dob BETWEEN #1982-01-01# AND #1986-01-01#
UNION
SELECT 'M', 'Total :', COUNT(*)
FROM CUSTOMER
WHERE gender='M' AND
dob BETWEEN #1982-01-01# AND #1986-01-01#
GROUP BY gender;

 

[ts568]

Thanks for the reply,

It works well, I understand everything apart from:

UNION SELECT 'M', 'Total :', COUNT(*)

could you explain what UNION does?

 

[JerryKlmns]

It is like appending the results from on table/query under onother one table/query. They have to have the same number of fields and in such order that same data type fields, line up under the same data type column.

result from 1st query
15-11-2007 | 1,324.54 | Mitsos | Papadpoulos4
10-11-2007 | 61,324.54 | Kitsos | Papadpoulos5
05-11-2007 | 1,454.54 | Pitsos | Papadpoulos6

result from 2nd query
15-11-2007 | 1,324.54 | | Papadpoulos1
10-11-2007 | 61,324.54 | | Papadpoulos2
05-11-2007 | 1,454.54 | | Papadpoulos3

result from their union
15-11-2007 | 1,324.54 | Mitsos | Papadpoulos4
10-11-2007 | 61,324.54 | Kitsos | Papadpoulos5
05-11-2007 | 1,454.54 | Pitsos | Papadpoulos6
15-11-2007 | 1,324.54 | | Papadpoulos1
10-11-2007 | 61,324.54 | | Papadpoulos2
05-11-2007 | 1,454.54 | | Papadpoulos3

 

[lespaul]

In your original post, you say that you just want the number of men.

The query I posted above:

SELECT GENDER, COUNT(*) AS NumberOfCustomers FROM CUSTOMER WHERE Gender = 'M' and DOB BETWEEN #1982-01-01# AND #1986-01-01# GROUP BY GENDER

does exactly that. The results would show:

[tt]
Gender NumberOfCustomers
M 25
[/tt]

I'm not sure why you're messing around with the names of the men if you just wanted to know how many.

You could modify the query to show both genders by removing part of the WHERE clause:

SELECT GENDER, COUNT(*) AS NumberOfCustomers FROM CUSTOMER WHERE DOB BETWEEN #1982-01-01# AND #1986-01-01# GROUP BY GENDER

This result set would GROUP BY the Gender field and show you:

[tt]
Gender NumberOfCustomers
F 37
M 25
[/tt]

if for some reason you had records with missing gender information you would get:

[tt]
Gender NumberOfCustomers
6
F 37
M 25
[/tt]







Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[ts568]

Thanks JerryKlmns,

I understand what you mean now,

and thanks Leslie, I missed your original post.

Your help has been super, thanks for all of those examples they have really helped me understand whats going on.

Thanks again.

 

[JerryKlmns]

Leslie,

ts568 could be a mySQL fan

 

[jimirvine]

ND (((CUSTOMER.dob)= BETWEEN "01/01/1982" AND "01/01/1986"));

What about all the guys born between 1 Jan 1987 and 9th May 1987? They'd all be 20.
And the guys born between 10-May-1981 and 31-Dec-1981, They'd be 25.

Not only that, but when you run the query tomorrow, the resultset gets even more inaccurate. You'd need to change the operands every time you ran the query. Why not calculate the age (based on the 'Date()') in the query and restrict on that age calculation

use

DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))

To accurately calculate age in years

 

[ts568]

jimirvine, thanks for that, thats a very good idea. I will have a go at trying this soon, I must move on for now, as I have many other queries I want to get through.

Thanks again for your input!
I will let you know how it goes.

JerryKlmns, I would love to be a mySQL fan!!! hehe