?
Solved

How to switch my counts in Microsoft Access?

Posted on 2011-04-20
4
Medium Priority
?
138 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
0
Comment
Question by:Anthony6890
  • 3
4 Comments
 
LVL 9

Accepted Solution

by:
sshah254 earned 2000 total points
ID: 35434209
look into the crosstab query syntax.

That should give you what you need.

Ss
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 35434229
Thanks!  I will look into that.
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 35434865
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?
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 35435158
Never mind, I figured it out.  
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question