By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
463,209 Members | 567 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 463,209 IT Pros & Developers. It's quick & easy.

Access 2003 crosstab query where criteria as increment

P: 20

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


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.

Oct 5 '07 #1
Share this Question
Share on Google+
4 Replies

Expert 2.5K+
P: 3,072
I miss the needed rs.movenext in your WHILE-loop:

Expand|Select|Wrap|Line Numbers
  1. Dim db As dao.Database
  2. Dim rs As dao.Recordset
  3. Set db = CurrentDb()
  4. Set rs = db.OpenRecordset("Select * From Location", dbOpenSnapshot)
  6. While Not rs.EOF
  7.   DoCmd.OutputTo acOutputQuery, "Rental_CrossTab_PercentageOfTotalQtyByLocation", "MicrosoftExcel(*.xls)", "C:\Documents ........\Rental Crosstab - " & rs!Location & " - " & Format(Now(), "dd-mmm-yy") & ".xls", True
  8.   rs.movenext
  9. Wend
  11. rs.Close
Oct 5 '07 #2

P: 20
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.

Oct 5 '07 #3

P: 20
oh yes sorry, that does need to be in there!
Oct 5 '07 #4

Expert 2.5K+
P: 3,072
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?

Oct 5 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.