Complex Crosstab

[Crilen007]

I am having some troubles with a database. I can send it as it has no sensitive data, but it is too large for the boards. If anyone knows a lot about crosstabs and stuff, please give me a shout on MSN

jj -underscore- gogo -at- hotmail -dotttttttt- com


^ anti spam =P

 

[Crilen007]

ok, i guess no one likes the email thing.


Here is the problem:

Tables:
tblBranch
BrNum BrName
1       Name1
2       Name2
3       Name3


tblPaidOut
GLNum GlAmount Date     Branch
1      $1.00   1/1/2004  3
2      $5.00   1/5/2004  1
3      $6.00   1/8/2004  2
3      $7.00   1/9/2004  3
4      $3.00   1/9/2004  3

The user picks the dates (has to be first of month and last of that same month) so the data displays correctly.

So my inputs are
#1/1/2004# and #1/31/2004#

I am trying to put it it into a spread sheet that includes the following:

Sum for the day, and sum for the GL (so a sum at the right and bottom)
Has every day of the month listed, even if nothing is in that day for transactions (I assume I need a tblday or something)

I am also thinking I have to import the Data into excel to get the data the way i want (summed, formatted, etc..) which is all fine.

Problems:
1) If i do all the branches then I get every GL in the list even if I dont want them, so I assume I have to do some SQL that goes through each branch separatly when appending to access or whatever it appends to.

2) I can't seem to figure out the day thing.




This is what I need it to look like

Page 1:

1 - Name1
   GL    2 
Day

1
2
3
4
5     $5.00
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


Page 2:

2 - Name2
   GL  3
Day

1
2
3
4
5
6
7
8     $6.00
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


Page 3:

3 - Name3
   GL  1      3      4       Total
Day

1    $1.00                  $1.00
2
3
4
5 
6
7
8
9                $7.00   $3.00  $10.00
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Total  $1.00    $7.00  $3.00   $11.00

As a crappy example.

If you dont know what I mean I could post an excel version of what I am trying to get at in the end.

Anywho, thanks for helping if you can.

 

[Crilen007]

Still stuck on this one..

If I have more than one branch, the empty days wont show up.
If I only have one branch, then I the person has to run through each branch everytime they want to print the report.

I could do SQL, but I still dont know how that would work. Also, because the GL's used each time are changing, the report fields would have to know which fields are. Not sure how that would be done either.

I would really appreciate any help anyone can give.
I can send someone what I have so far, to make it easier.

Reply here if you think you can help. I would greatly appreciate it.

 

[ssoltic]

I want to help, but don't understand what you want.
please post what you have and what you want (excel like)

 

[Crilen007]

ok

here are the what i have and what I would like files.

Thanks for helping!

 

[Crilen007]

Anyone have a try at this?

 

[daveUK]

I think I've found an example of what you're looking for - http://www.rogersaccesslibrary.com/download3.asp?SampleName=CrossTabReport.mdb.

The example will produce a report from crosstab query, which you can then ecport into excel if you wish, although the report has a sum at the bottom and on the left (I'm sure this can be changed to the right).

HTH