 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2006Pivot table like results from query
I know this should be easy, but I can' t figure this one out to save me. I have two tables, linked by unique serial number. I am pulling date/time from one table and their process codes from the other. Currently, the results of the query show one line item for every date/process code, based on a date range given by the user. Date/Time Processcode 5/30/2006 5:00:00 AM C2 5/30/2006 5:00:01 AM C2 5/30/2006 5:00:02 AM C3 5/30/2006 5:00:03 AM C3 5/30/2006 5:00:04 AM C4 What I would like is something like a pivot table in Excel, where I am getting count of the process codes for the given date/time range. Processcode CountOfProcesscode C2 2 C3 2 C4 1 I know this should be easy, but I cannot figure it out. Any help would be appreciated, and I prefer non-SQL based help if possible. SQL is the way to go with this one. You need a Crosstab query. Use your existing query and build a crosstab upon it. In the Database Window go to Queries, then New. The Crosstab Query Wizard should be one of the options. Click on the Both option button so that you can see both your tables and queries. Find the original query and let the wizard guide you through the process. Don't be discouraged if you have to try it 2 or 3 times to get it right. Also if you are using a parameter in the original query to prompt for information, make sure that while in design view you go to Query, Parameters and specifically define the parameter datatype. Crosstabs are picky if you don't.  SignatureJerry Whittle Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > I know this should be easy, but I can' t figure this one out to save > me. [quoted text clipped - 23 lines] > Any help would be appreciated, and I prefer non-SQL based help if > possible. I still don't get how to get it to summarize, or count, the text based data (i.e. how many C3 parts in a given time). I'll continue to play with the crosstab query, but it you could give a little more specific data, I would appreciate it. Tim > SQL is the way to go with this one. You need a Crosstab query. Use your > existing query and build a crosstab upon it. [quoted text clipped - 40 lines] > > Any help would be appreciated, and I prefer non-SQL based help if > > possible. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.