VBA and Reports

[Romka]

Hi,

I have a database from which i wish to pull out a 'summary' like report.

this report will consist of users, and the weeks for which the data has been input for each user.

the table from which the data is pulled looks something like this:

fldid{user id} | fldcategoryid | fldcategorydata

i now have to produce a report which should look like this:

user id | fldcategory## - fldcategory##

the latter represents a range of weeks. so i need to be able to link each of the textboxes on the report to the CURRENT fldid that is being looked at - thus creating a dynamic lookup for each user for each of the categoryid's

i hope this makes sense. I would really appreciate if someone could lend a hand with this one.

Thank you.

PS:

i have tried something like this:

=DLookUp("fldcategorydata","tableUserCategoryData","fldid = " & [Reports].[report1].[fldid])

and it works - but ONLY to the point of displaying the one single datastream for the first fldid being looked at, it doesnt filter down through to the rest of the fldid's in the table that is binded to the report.

thank you once again.

 

[dhookom]

This makes very little sense to me but I am getting up there in years. Speaking of years, you mention "range of weeks" but you don't ever mention a "Date" type of field.

Can you provide some sample records with field and table names and how you would like these displayed in your report?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Romka]

Sure, here are the tables that we'll be using:

tableUserData
fldid, fldfname, fldlname
01234 | Mike | Smith
43211 | John | Mitchel

tableCategoryList
fldcategoryid, fldcategory
1 | Week 01
2 | Week 02
..
52 | Week 52

tableUserCategoryData
fldid, fldcategoryid, fldcategorydata
01234 | Week 1 | 200 'not every week has data
01234 | Week 7 | 150
..
01234 | Week 52 | 100
43211 | Week 13 | 150


Now I wish to bind the report to the tableUserData. For every user I wish to pull out the data for each week. There are other categories, but for this example this should be enough.

So the report is to look like this:

userFName | userLName | Week1-52 (columns)
Mike Smith 200 .. 150 .. 100
John Mitchel 0 .. 150 .. 0

The problem is the binding of the textboxes.

If anyone has any suggestions - they're very welcome.

Thank you.

 

[dhookom]

I would join the tables [tableUserCategoryData] and [tableUserData] in a query and make it into a crosstab query where the user name fields are the Row Headings, fldcategoryid (is this the actual value?) as the Column Heading, and Sum of fldcategorydata as the value.

Then create your report based on the crosstab. You may want to set your Column Headings Property of the crosstab to all possible column values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]