473,327 Members | 2,012 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,327 software developers and data experts.

report totals

Hello to all,

Here is my data
Bureau Days here are the samples
1 12 (0 - 14 days)
1 13 (15 - 21 days)
1 15 (15 - 21 days)
1 2 (0 - 14 days)

3 49 (22 - 90 days)
3 43 (22 - 90 days)
3 15 (15 - 21 days)

11 12 (0 - 14 days)
11 16 (15 - 21 days)
11 19 (15 - 21 days)
11 17 (15 - 21 days)
11 32 (22 - 90 days)

31 17 (15 - 21 days)
31 90 (22 - 90 days)
31 91 (91 - overdays)
31 98 (91 - overdays)
I am trying to produce a report as show below.

Total Days

Bureau 0 - 14 days 15 - 21 days 22 - 90 days 91 - overdays
1 2 2 0
0
3 0 1 2
0
11 1 3 1
0
31 1 1 1
2
can anyone help me out.

thanks a bunch

Nov 13 '05 #1
2 1171
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

TRANSFORM Sum(Days) As theValue;
SELECT Bureau, Sum(Days) As Total
FROM table_name
WHERE < criteria >
GROUP BY Bureau
PIVOT Switch(Days Between 0 And 14, "0 - 14 days",
Days Between 15 And 21, "15 - 21 days",
Days Between 22 And 90, "22 - 90 days",
Days >= 91, "91 - overdays")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgAfdYechKqOuFEgEQLluwCg97PmPEMtaVbpGvaa8ve5FP ONZfYAoPJC
jDL5vNrCrfMjr9Uz1DBfd19y
=tVxh
-----END PGP SIGNATURE-----
KEVIN97810 wrote:
Hello to all,

Here is my data
Bureau Days here are the samples
1 12 (0 - 14 days)
1 13 (15 - 21 days)
1 15 (15 - 21 days)
1 2 (0 - 14 days)

3 49 (22 - 90 days)
3 43 (22 - 90 days)
3 15 (15 - 21 days)

11 12 (0 - 14 days)
11 16 (15 - 21 days)
11 19 (15 - 21 days)
11 17 (15 - 21 days)
11 32 (22 - 90 days)

31 17 (15 - 21 days)
31 90 (22 - 90 days)
31 91 (91 - overdays)
31 98 (91 - overdays)
I am trying to produce a report as show below.

Total Days

Bureau 0 - 14 days 15 - 21 days 22 - 90 days 91 - overdays
1 2 2 0
0
3 0 1 2
0
11 1 3 1
0
31 1 1 1
2
can anyone help me out.

thanks a bunch

Nov 13 '05 #2


Hello,
This works like a charm. Very good job. Is there a way to put
zero (0) when we have an empty space. I been trying to mess with the
switch in the query but no luck.

thanks
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #3

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

Similar topics

3
by: John Baker | last post by:
Hi: I have a situation where I have a series of time records (one or more per day) for a number of projects. Each record has an identification for the activity conducted during the period...
2
by: Steven Stewart | last post by:
Hi there, I don't have a lot of experience with this so I am looking for some help. I have a form that allows a user to choose the options she wants. How do I set it up so that the report...
1
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
1
by: fixedpower | last post by:
I have an Access 2000 DB for a financial company. I have the table structure completed, but I am having trouble with the report. Basically, I have a Client tbl, a StockShares tbl, and a...
8
by: sara | last post by:
Hi - I have looked at all posts and tried both Allen Browne's Report Sorting at run Time ( Select Case Forms!frmChooseSort!grpSort Case 1 'Name Me.GroupLevel(0).ControlSource = "LastName"...
4
by: lorirobn | last post by:
Hi, I have a report displaying items that are missing from a room. I created 2 queries, the first getting the items IN the room, and the second being an "unmatched" query that references the...
4
by: Micheal | last post by:
Greetings Access Group, Being relatively new to Access, I try to work through problems on my own and have been very successful, although I have a conundrum that I have been working on for two days...
5
prn
by: prn | last post by:
Hi folks, I'm looking for a little advice here. I need to create a report that is totals only, with no detail records. I have a database with a lot of individuals (people) and the report has to...
0
by: zufie | last post by:
Hi, Here are the specifics of the Report which I am to create. Reports for Callers - from Begin Date to End Date of Reporting Period and by respective County: * List Language preference of...
14
ollyb303
by: ollyb303 | last post by:
Hi, I am trying to create a dynamic crosstab report which will display number of calls handled (I work for a call centre) per day grouped by supervisor. I have one crosstab query (Query1) which...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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...
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: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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)...
1
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
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.