The SQL below displays an employee count for 14 branches.
What I'd like to do is keep the individual counts for branches 1 through 11 but have branch 12 be the sum of branch 12, 13, 14.
SELECT COUNT(Employees.EmployeeID) AS Count_EmployeeID, Branches.Branches, UserInRole.UserRoleID
FROM dbo.Employees, dbo.Branches, dbo.UserInRole
WHERE dbo.Employees.BranchID = dbo.Branches.BranchID
AND dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
GROUP BY Branches.Branches, UserInRole.UserRoleID
HAVING (UserInRole.UserRoleID = '1')
Thanks for your help.
James
4 1297
The SQL below displays an employee count for 14 branches.
What I'd like to do is keep the individual counts for branches 1 through 11 but have branch 12 be the sum of branch 12, 13, 14.
SELECT COUNT(Employees.EmployeeID) AS Count_EmployeeID, Branches.Branches, UserInRole.UserRoleID
FROM dbo.Employees, dbo.Branches, dbo.UserInRole
WHERE dbo.Employees.BranchID = dbo.Branches.BranchID
AND dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
GROUP BY Branches.Branches, UserInRole.UserRoleID
HAVING (UserInRole.UserRoleID = '1')
Thanks for your help.
James
One way to go is to use the union of the two selects: -
SELECT COUNT(Employees.EmployeeID) AS Count_EmployeeID, Branches.Branches, UserInRole.UserRoleID
-
FROM dbo.Employees, dbo.Branches, dbo.UserInRole
-
WHERE dbo.Employees.BranchID = dbo.Branches.BranchID
-
AND dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
- AND Branches.BranchID BETWEEN 1 AND 11
-
GROUP BY Branches.Branches, UserInRole.UserRoleID
-
HAVING (UserInRole.UserRoleID = '1')
-
- UNION
-
-
SELECT COUNT(Employees.EmployeeID) AS Count_EmployeeID, 12 AS Branches, UserInRole.UserRoleID
-
FROM dbo.Employees, dbo.Branches, dbo.UserInRole
-
WHERE dbo.Employees.BranchID = dbo.Branches.BranchID
-
AND dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
- AND Branches.BranchID BETWEEN 12 AND 14
-
GROUP UserInRole.UserRoleID
-
HAVING (UserInRole.UserRoleID = '1')
-
One way to go is to use the union of the two selects: -
SELECT COUNT(Employees.EmployeeID) AS Count_EmployeeID, Branches.Branches, UserInRole.UserRoleID
-
FROM dbo.Employees, dbo.Branches, dbo.UserInRole
-
WHERE dbo.Employees.BranchID = dbo.Branches.BranchID
-
AND dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
- AND Branches.BranchID BETWEEN 1 AND 11
-
GROUP BY Branches.Branches, UserInRole.UserRoleID
-
HAVING (UserInRole.UserRoleID = '1')
-
- UNION
-
-
SELECT COUNT(Employees.EmployeeID) AS Count_EmployeeID, 12 AS Branches, UserInRole.UserRoleID
-
FROM dbo.Employees, dbo.Branches, dbo.UserInRole
-
WHERE dbo.Employees.BranchID = dbo.Branches.BranchID
-
AND dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
- AND Branches.BranchID BETWEEN 12 AND 14
-
GROUP UserInRole.UserRoleID
-
HAVING (UserInRole.UserRoleID = '1')
-
Thanks for your help. I tried to execute the query but I get an error related to the UNION statement. If I run the select statements separately they work fine.
I made a couple small corrections. -
SELECT COUNT(Employees.EmployeeID) AS Count_EmployeeID, 12 AS BranchID, UserInRole.UserRoleID
-
FROM dbo.Employees, dbo.Branches, dbo.UserInRole
-
WHERE dbo.Employees.BranchID = dbo.Branches.BranchID
-
AND dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
-
AND dbo.Branches.BranchID BETWEEN 12 AND 14
-
GROUP BY UserInRole.UserRoleID
-
HAVING (UserInRole.UserRoleID = '1')
12 as BranchID works but I need to return a Branch name.
Is it possible to use the branch names instead of the ID's?
For example,
San Diego as Branches and dbo.Branches.Branches BETWEEN San Diego AND San Francisco?
Maybe - dbo.Branches.Branches IN ('San Diego','Los Angeles','San Francisco')
Is there another way without the UNION?
Thanks
Thanks for your help. I tried to execute the query but I get an error related to the UNION statement. If I run the select statements separately they work fine.
I made a couple small corrections. -
SELECT COUNT(Employees.EmployeeID) AS Count_EmployeeID, 12 AS BranchID, UserInRole.UserRoleID
-
FROM dbo.Employees, dbo.Branches, dbo.UserInRole
-
WHERE dbo.Employees.BranchID = dbo.Branches.BranchID
-
AND dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
-
AND dbo.Branches.BranchID BETWEEN 12 AND 14
-
GROUP BY UserInRole.UserRoleID
-
HAVING (UserInRole.UserRoleID = '1')
12 as BranchID works but I need to return a Branch name.
Is it possible to use the branch names instead of the ID's?
For example,
San Diego as Branches and dbo.Branches.Branches BETWEEN San Diego AND San Francisco?
Maybe - dbo.Branches.Branches IN ('San Diego','Los Angeles','San Francisco')
Is there another way without the UNION?
Thanks
1. Thanks for the "BY" correction, I erased it in error.
2. Without having table defs I didn't know if Braches was ID or name. I believe the correct form is e.g. -
SELECT COUNT(Employees.EmployeeID) AS Count_EmployeeID, 'San Diego' AS Branches, UserInRole.UserRoleID
-
3. "BETWEEN" is not really useful in this case with branch names (as it would order them alphabetically), "IN", though, should work fine.
4. I believe that the UNION one works fine. (Maybe because it was my idea :-D) There are other ways, though. One is to have two result columns first (by using two CASEs in the SELECT statement): one for the individual branches and one (summed) for the group. Then you can merge the two in an outer SELECT (by adding them grouped by IDs, which again need to be CASEd in the inner SELECT). I think this is much more complicated and not nearly as clear to read.
1. Thanks for the "BY" correction, I erased it in error.
2. Without having table defs I didn't know if Braches was ID or name. I believe the correct form is e.g. -
SELECT COUNT(Employees.EmployeeID) AS Count_EmployeeID, 'San Diego' AS Branches, UserInRole.UserRoleID
-
3. "BETWEEN" is not really useful in this case with branch names (as it would order them alphabetically), "IN", though, should work fine.
4. I believe that the UNION one works fine. (Maybe because it was my idea :-D) There are other ways, though. One is to have two result columns first (by using two CASEs in the SELECT statement): one for the individual branches and one (summed) for the group. Then you can merge the two in an outer SELECT (by adding them grouped by IDs, which again need to be CASEd in the inner SELECT). I think this is much more complicated and not nearly as clear to read.
Yes, indeed it does work fine! :) I realized that what was causing the error before was in the first select we had Branches.Branches but in the second select it was 12 as BranchID. Of course your original suggestion was correct in its scripting (without the table defs) however I needed to change 12 AS Branches (your script) to 12 AS BranchID. Now that I have 'San Diego' AS Branches corresponding to Branches.Branches the UNION works like a charm and the planets are once again properly aligned.
Thank you azimmer!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: netpurpose |
last post by:
I need to extract data from this table to find the lowest prices of
each product as of today. The product will be listed/grouped by the
name only, discarding the product code - I use...
|
by: Shyguy |
last post by:
I have an option group with about 30 options. I would like to have
the Option Labels get their text from a table. Is this possible, and
if so how?
Also, is it possible to embad a font into a...
|
by: Brian Coy |
last post by:
I have a database that records a part no, a scrap reason, the cost of
scrapping that item, and the date. I need to porduce a report that
will show up to a weeks worth of data at a time, but I need...
|
by: ChadDiesel |
last post by:
Hello everyone,
I'm having a problem with Access that I need some help with. The short
version is, I want to print a list of parts and part quantities that belong
to a certain part group---One...
|
by: Tom |
last post by:
I am looking for some ideas for how to design the layout of the form for
data entry and to display the data for the following situation:
There are many sales associates. A sales associate can work...
|
by: ward |
last post by:
Greetings.
Ok, I admit it, I bit off a bit more than I can chew.
I need to complete this "Generate Report" page for my employer and I'm
a little over my head. I could use some additional...
|
by: U S Contractors Offering Service A Non-profit |
last post by:
Brilliant technology helping those most in need Inbox
Reply
U S Contractors Offering Service A Non-profit
show details
10:37 pm (1 hour ago)
Brilliant technology helping those most in need ...
|
by: sunbeam |
last post by:
Short Description of the Project:
we developed a e-learning system for our students. each student has a unique username/password to view the modules he/she should view and nothing more. since we...
|
by: Chuck36963 |
last post by:
Hi all,
I've been working on a listing problem and I can't figure out how to work it out. I have looked far and wide on the web to find answers, but I'd like other peoples input on my project in...
|
by: speavey |
last post by:
When I run this query, I get an ORDER BY error "Incorrect syntax near the keyword 'ORDER'. I've bolded it below.
If I take the ORDER by out then it works correctly, but I need the ORDER BY...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
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....
|
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
|
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...
| |