473,320 Members | 2,177 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Add Items

Hi All

I working on a Access Database for a friend and I have hit a dead end.
This is a DB of Customers with their personal details and Clothing
size.

I have a simple table and a form to enter various info like name, shoe
size, shirt size and so on.
I want to create a query to give me the total of customers with the
same size so I will know how many of that size to purchase.

Like If I have 20 customers, 6 with size 7, 4 with size 8, 10 with size
9, I want the report to state....

Shoe Sizes

Size Quantity
7 6
8 4
9 10

Thats basically it, I sounds so easy when you say it but to make it
work it another story.

Thanks for all your help

Regards,
Raven

Nov 13 '05 #1
4 1322
I don't believe there's an easy way. I would also like to see if
someone has a simpler solution to this situation.

In the normal SQL world, you could do the following:

Select CustomerName,
Count(Case When ShoeSize = 6 Then 1 Else NULL End)
Count(Case When ShoeSize = 7 Then 1 Else NULL End)
Count(Case When ShoeSize = 8 Then 1 Else NULL End)
From
tblCustomers
Group By CustomerName

However I don't Believe Jet/Access Supports "CASE"

So Instead you have to do seperate queries for each size:

Select CustomerName, Count(*)
From tblCustomers

Where
ShoeSize = 6
Group By CustomerName

etc...
etc...
- Jeremy

Nov 13 '05 #2
I don't believe there's an easy way. I would also like to see if
someone has a simpler solution to this situation.

In the normal SQL world, you could do the following:

Select CustomerName,
Count(Case When ShoeSize = 6 Then 1 Else NULL End)
Count(Case When ShoeSize = 7 Then 1 Else NULL End)
Count(Case When ShoeSize = 8 Then 1 Else NULL End)
From
tblCustomers
Group By CustomerName

However I don't Believe Jet/Access Supports "CASE"

So Instead you have to do seperate queries for each size
and change the Where statement:

Select CustomerName, Count(*)
From tblCustomers

Where
ShoeSize = 6
Group By CustomerName

etc...
etc...
- Jeremy

Nov 13 '05 #3

<rm******@rosebankcollege.co.za> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Hi All

I working on a Access Database for a friend and I have hit a dead end.
This is a DB of Customers with their personal details and Clothing
size.

I have a simple table and a form to enter various info like name, shoe
size, shirt size and so on.
I want to create a query to give me the total of customers with the
same size so I will know how many of that size to purchase.

Like If I have 20 customers, 6 with size 7, 4 with size 8, 10 with size
9, I want the report to state....

Shoe Sizes

Size Quantity
7 6
8 4
9 10

Thats basically it, I sounds so easy when you say it but to make it
work it another story.

Thanks for all your help

Regards,
Raven


This sounds like a classroom question, especially with
"rosebankcollege.co.za" e-mail address. Anyway, you just need a simple
query like the following:

SELECT ShoeSize, Count(*) AS Qty
FROM tblCustomers
GROUP BY ShoeSize
ORDER BY ShoeSize
Nov 13 '05 #4
Assuming table name is clothing and field is called Size then:-

SELECT Size, Count(Size) AS Quantity
FROM clothing
GROUP BY Size
ORDER BY Size;
--
Terry Kreft
MVP Microsoft Access
<rm******@rosebankcollege.co.za> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Hi All

I working on a Access Database for a friend and I have hit a dead end.
This is a DB of Customers with their personal details and Clothing
size.

I have a simple table and a form to enter various info like name, shoe
size, shirt size and so on.
I want to create a query to give me the total of customers with the
same size so I will know how many of that size to purchase.

Like If I have 20 customers, 6 with size 7, 4 with size 8, 10 with size
9, I want the report to state....

Shoe Sizes

Size Quantity
7 6
8 4
9 10

Thats basically it, I sounds so easy when you say it but to make it
work it another story.

Thanks for all your help

Regards,
Raven

Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: Donnal Walter | last post by:
The following method is defined in one of my classes: def setup(self, items={}): """perform setup based on a dictionary of items""" if 'something' in items: value = items # now do something...
4
by: mb | last post by:
what is the best way to do this: In a game I want to a class called "Items". This class will have the game items public class Items { public int Chair public int Table . . .and so on . . .
9
by: Alpha | last post by:
Hi, How can I set all the items in a listbox to be selected? I can't find a property or mehtod to do it so I thought I'll try using setselected method but I need to find out how many items are in...
2
by: dave | last post by:
This little problem is driving me nuts!! On my webform page I create 2 variables.. Protected p_dml As String = "I" Public Const mwv_id As Integer = 0 ' originally had mwv_id as Protected
21
by: StriderBob | last post by:
Situation : FormX is mdi child form containing 2 ListViews ListView1 contains a list of table names and 4 sub items with data about each table. ListView2 contains a list of the columns on each...
2
by: hsuntn | last post by:
I am grabbing Outlook MailItems using the Items property on my Outlook inbox. When I iterate through them, I notice that they are not ordered in ReceivedTime or CreationTime order. For example, ...
0
by: Brian Henry | last post by:
Since no one else knew how to do this I sat here all morning experimenting with this and this is what I came up with... Its an example of how to get a list of items back from a virtual mode list...
13
by: Joel Koltner | last post by:
Is there an easy way to get a list comprehension to produce a flat list of, say, for each input argument? E.g., I'd like to do something like: for x in range(4) ] ....and receive
13
by: PetterL | last post by:
I writing a program where I read menu items from a file. But I have problem when I click an menu item i want it to mark that one as checked but I cant access the menu object of that item to see...
2
by: mygirl22 | last post by:
Hi, I used this code to created 2 combo boxes General and Specific...and Only show Specific (Combo B) when Combo A is chosen..... What i need now is to know how to assign specific values to the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.