We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How to switch my counts in Microsoft Access?

Anthony6890
Anthony6890 asked
on
Medium Priority
149 Views
Last Modified: 2012-05-11
I created an SQL statement to count the number of Full Time and Part Time people that I have in a given store.  The only problem is that The headings I have horizontally I want vertically and vice versa.  

Right now my query produces:

MBFTPT      PK
F               950
P              2067

I want the F and P where the PK is and the PK going down b/c I will have more stores.

Here is my syntax:

SELECT STEP_1.MBFTPT, Count(STEP_1.[MBSS#]) AS PK
FROM STEP_1
WHERE (((STEP_1.MBFTPT)="F" Or (STEP_1.MBFTPT)="P") AND ((STEP_1.MBSTOR)>=100000 And (STEP_1.MBSTOR)<=100999))
GROUP BY STEP_1.MBFTPT

I've tried numerous things, but it doesn't seem to work correctly.

Any help would be greatly appreciated.

-Anthony
Comment
Watch Question

Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Thanks!  I will look into that.

Author

Commented:
How can I get my crosstab to summarize a group together:

Here is my current code:


TRANSFORM Count(STEP_1.[MBSS#]) AS [CountOfMBSS#]
SELECT STEP_1.MBFTPT
FROM STEP_1
WHERE (((STEP_1.MBSTOR)>=100000 And (STEP_1.MBSTOR)<=100999))
GROUP BY STEP_1.MBFTPT
PIVOT STEP_1.MBSTOR;


This  currently lists out all of my stores example, 100000, 1000032, and etc up to all the stores in the specified range.

There are then 2 rows listing F and P which calculates the number of people in each.  I want all of the stores in the range to summarize to one heading called, PMK.

How can I do this?

Author

Commented:
Never mind, I figured it out.  
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.