Report problem with Query/Table

[Elysyn]

Good Day!

I have a problem with a report I am creating. I would like the layout to look like this...

Headings:___Bucket1_____Bucket2_____Bucket3______Bucket4
Data:______Skill Name___Skill Name__Skill Name___Skill Name

The information is being taken from a table (tblSkillBuckets) set up like this:

Headings:___SkillName____SkillNumber___BucketNumber
_____________Alpha___________2_____________1
_____________Beta____________3_____________1
_____________Delta____________4_____________2
Etc...

I've created 5 queries, 4 for each separate bucket and one that pulls them all together, but it creates 252 records for each bucket (Bucket 1 should only have 3) I've also tried setting the Record Source for the Report as tblSkillBuckets and using SQL (both in Row Source in Properties and in code) and I get no data in return.

Here is the latest code attempt:

Private Sub Report_Open(Cancel As Integer)

Dim Bucket1 As String

Bucket1 = "SELECT tblSkillBuckets.Name, tblSkillBuckets.Num, tblSkillBuckets.Bucket FROM tblSkillBuckets"
Bucket1 = Bucket1 & "WHERE tblSkillBuckets.Bucket = 1;"

Me.cmbBucket1.RowSource = Bucket1

End Sub

I'm confused, because this really seems like it should be working. Do any of you have any ideas or suggestions?

Thanks!

 

[mdmilner]

Start with a two field table holding your basic data.
First field is the Bucket number and the second the skill.

Then create this crosstab query:

TRANSFORM Count(Table1.fld2) AS CountOffld2
SELECT Table1.fld2, Count(Table1.fld2) AS [Total Of fld2]
FROM Table1
GROUP BY Table1.fld2
PIVOT Table1.fld1;

You can use the wizard interface, but it's going to want a third field to 'count' the instances even though the crosstab query syntax does not require it. Might be easiest if you just created this one from SQL - or added a third field with the # 1 in it temporarily just to satisfy the wizard interface.

Base the report off the above query or possibly add it as the Report Recordsource

Mike