472,374 Members | 1,602 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,374 software developers and data experts.

Access 2003 crosstab query where criteria as increment


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
4 2311
3,080 Expert 2GB
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
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
oh yes sorry, that does need to be in there!
Oct 5 '07 #4
3,080 Expert 2GB
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

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

Similar topics

by: Michael John | last post by:
Dear Oracle Developers, my task is to make up a Oracle View from a Pivot table in MS Access. Given are two tables to join: T_FIRM: FIRM_ABBR VARCHAR2(3 BYTE), FIRM_LONG ...
by: deejayquai | last post by:
Hi I'm trying to produce a report based on a dynamic crosstab. Ultimately i'd like the report to actually become a sub report within a student end of year record of achievement. The dynamic...
by: Brad | last post by:
Thanks for taking the time to read my question. I have a table of data that has Date, Data and Category. I need to show, in a report, each Categories Data by Date. The Date has to be it's own...
by: Bill Hutchison | last post by:
I have a query that returns different results (3508 rows for snapshot, 6288 for dynaset) and that is the only thing I change to get the different results. When I try to make a table from the...
by: torontolancer | last post by:
Hi there how r u .I would really appriciate ur concern regarding checking out this code. its beind a command button and i am have a combo box selecing a query criteria and then pressing the button...
by: MMcCarthy | last post by:
Rather than using the Access design view change the view to SQL. I am going to attempt to outline the general syntax used for SQL queries in Access. Angle brackets <> are used in place of some...
by: helm | last post by:
Folks, could anyone advise ... Is there a significant difference in crosstab capabilities in Access and Excel? Using Office XP 2002 ... to produce a crosstab report I developed it in Excel from...
by: Aussie Rules | last post by:
Hi, I have a access 2007 database with a cross tab query. Based on the selection critieria the cross tab may contain a different number columns in the result. For example if the query is...
by: ollyb303 | last post by:
Hi, I am trying to create a dynamic crosstab report which will display number of calls handled (I work for a call centre) per day grouped by supervisor. I have one crosstab query (Query1) which...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
by: F22F35 | last post by:
I am a newbie to Access (most programming for that matter). I need help in creating an Access database that keeps the history of each user in a database. For example, a user might have lesson 1 sent...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.