Access 2003 crosstab query where criteria as increment

[rdsandy]

Hi,

I have some code below in VBA for Access 2003 which is on a button called "RentalCrosstabPercTtlQtyMonthLoc_Click". This is a crosstab query which brings up rental items down the side, who rents along the top, the rental month and total number of each item on that month as rows next to the item, something like this:

Item TotalQty Month Renter1 Renter2 ....
PC 234 Aug-2007 2.33 4.55

The values for renter are worked out in the crosstab query, but how isnt important here (as it will waste space explaining!!). Here is the code:

Dim db As dao.Database
Dim rs As dao.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Select * From Location", dbOpenSnapshot)

While Not rs.EOF
DoCmd.OutputTo acOutputQuery, "Rental_CrossTab_PercentageOfTotalQtyByLocatio n", "MicrosoftExcel(*.xls)", "C:\Documents ........\Rental Crosstab - " & rs!Location & " - " & Format(Now(), "dd-mmm-yy") & ".xls", True
Wend

rs.Close

There are 5 different locations, and at the moment the crosstab query brings up all rentals for all locations. What I need to be able to do is put in an increment in the crosstab query (in design view as criteria for a where) for location, which references the location table, and then have the locID (see below) put into the rs!Location in above code so the rentals for location 1, say, are saved, and then location 2 saved separately in a different excel spreadsheet.

The location table is made up of 2 columns (headings being LocID and Loc), with locID being numbers, and loc as the relevant name to the locID.

Many thanks in advance for help, I've tried looking on the net, and on this site, and couldn't find any information.

Andy

[nico5038]

I miss the needed rs.movenext in your WHILE-loop:

Dim db As dao.Database
Dim rs As dao.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Select * From Location", dbOpenSnapshot)
 
While Not rs.EOF
  DoCmd.OutputTo acOutputQuery, "Rental_CrossTab_PercentageOfTotalQtyByLocation", "MicrosoftExcel(*.xls)", "C:\Documents ........\Rental Crosstab - " & rs!Location & " - " & Format(Now(), "dd-mmm-yy") & ".xls", True
  rs.movenext
Wend
 
rs.Close
 

Nic;o)

[rdsandy]

Just to maybe explain it a little more, if for example I stuck 1 in the where criteria (locID being 1 and loc then being london), the crosstab query shows the results for london, and then puts london in place of the rs!Location (as it puts 1 into it, but puts the relevant location instead of the number).

Im sure you know but the query design is like:

Field: LocID
Table: Location
Total: Where
Crosstab: (not shown) - blank as not needed to be shown
Sort: left blank
Criteria: where the answer will eventually go

Many thanks again.

Andy

[rdsandy]

oh yes sorry, that does need to be in there!

[nico5038]

The loop however won't solve the selection for a particular location, as I see just the same query being used, without a location selection in a WHERE clause....
Is creating the same pivot table for all locations OK, or do you need just that location?

Nic;o)