Pivoting table ??

[brounea]

As you can see I'm new here and new to Ms Access.
Here is my question.
I'm trying to write MS access report that looks like this:

Item Subinventory JAn-04 Feb-04 Mar-04 ...
123 DC sum(qty) Sum(qty) sum(qty)
123 DE sum(qty) 0 sum(qty)
.
.
.

The table however has these fields: item,subinv,date,qty

How do I sun the right qty under the right date?

As you can see there can be any number of items under different subinvetories.

I already have a form to filter the parameters for me

Please I'm lost

Thanks

 

[Crowley16]

instead of using sum(qty), use a query to do it...

SELECT sum(qty) FROM tblName WHERE date between...

you'd have to create a field on your report for each of your date periods though...

 

[brounea]

Thanks Crowley16

But I would hope that there is a better way.

I was looking into Crosstab tables. Can anybody give me some more info.

Thanks

 

[dhookom]

Use "relative" dates rather than absolute dates in you column headings expression. This solution requires no code.
If your report will contain 5 days beginning today.
ColHead:"Day" & DateDiff("d",Date(),[DateField])
Set the Column Headings property to
"Day0","Day1",..."Day4"
Day0 will be today and Day4 will be four days out. Your report and query will always have the same columns/fields. In your report, you can use text boxes as column labels:
=DateAdd("d",0,Date())
=DateAdd("d",1,Date())
=DateAdd("d",2,Date())
=DateAdd("d",3,Date())
=DateAdd("d",4,Date())
You can also substitute a reference to a control on a form for Date() if you want your columns to reference a different ending date. If you want more than five columns, then add more values to your column headings property.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[brounea]

Thanks Duane,
That does solve the dates heading issue.

Could you explaing, what do I need to do for the sum(qty)?

What would the query look like?

I realy appriciate it.

Thanks again

-A

 

[dhookom]

My suggestion provided the Column heading information. Your Row Headings would be Item and Subinventory. Your Value in the crosstab would be Qty with Sum.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[brounea]

Thanks Duan,

I'll give it a try.

-A

 

[brounea]

Duan,

I tried it and the problem is even harder. I do not know how to put the sum(qty) under the right month.

In other words:

The cross tab gives me the sum(qty) in the right month.
The months heading is just the heading.

When a row of data comes, how do I put the right sum(qty) under the month it belong to.

Thanks for all your effort.

This is the report from hell (for me).

 

[dhookom]

The sum(qty) should always show the sum of the qty for the month under the specific month. What are you finding that is different or wrong? If you are having trouble, paste your SQL view in a reply.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.