?
Solved

Crosstab Date Ranges

Posted on 2003-03-02
10
Medium Priority
?
344 Views
Last Modified: 2010-04-03
I have a crosstab query that pulls data from a select query. I am trying to add a control that will prompt the user for the date range that is to be queried. I have tried the Between([Start Date:])and ([End Date:]) approach but I keep getting a "Jet engine does not recognize" error. It works fine for a select query but not a crosstab. What am I missing?
0
Comment
Question by:azrat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 1

Expert Comment

by:Mike-Kieswetter
ID: 8056122
There are two solutions that I have used with Access 97.
1. If you know the range of values that will appear in the column headings:
Open up the query property sheet (if the sheet says 'field properties', click on a blank space in the upper pane).
You will find an input field for 'column headings'.
In here type in the headings for this query separated with a comma.
The query should run once this is done.

2. If you don't know the column headings:
Create a table to take the data from your select query.
Create a delete query to empty this table.
Change the select query to an append query and ensure the dates from your form are in this query.
Change your Xtab to run from this table.

The sequence to run is:
a) Open delete query
b) Open append query
c) Open Xtab query
0
 

Expert Comment

by:happyboy25
ID: 8078374
Hi there,
If you really understand what the expert means can you please explain a bit here

2. If you don't know the column headings:
Create a table to take the data from your select query.
Create a delete query to empty this table.
Change the select query to an append query and ensure the dates from your form are in this query.
Change your Xtab to run from this table.

The sequence to run is:
a) Open delete query
b) Open append query
c) Open Xtab query

Thanks,
BEGINNER
0
 
LVL 1

Expert Comment

by:Mike-Kieswetter
ID: 8078567
In some Xtabs, you always want or expect the same headings, e.g. your query might have #orders by state, so you can add the state names as headings into the query property sheet.
When the headings are not fixed, e.g. if the query gave #orders by month, then it is not possible to add the headings to the property sheet as they will keep changing.
In this case you need to have a query that selects the data between the date range required and appends it to an empty table.
The Xtab query run from this table does not need to worry about the date range as it was fixed by the append query.

Lets say the Xtab drives a report.
In the report's On Open event you would have the following statements:
'Delete old data from temp table
DoCmd.OpenQuery ("Delete TempTle qry")
'Add New data to temp table
DoCmd.OpenQuery ("Append Data qry")

This works around the Jet Engines failure to process Xtabs with parameters.

Hope this helps.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Expert Comment

by:happyboy25
ID: 8080163
Hi   Mike-Kieswetter

Thanks for your reply.

What are the fields for the temp table ?
What data do you append to the table ?

Can you give me an example ?

I'll open another question just to award you the necessary points :)

Thanks

BEGINNER
0
 
LVL 1

Expert Comment

by:Mike-Kieswetter
ID: 8081195
Hi HappyBoy25,
It would be more understandable if you have an example from a preject you are working on.
Give some details and I'll fill you in.

Mike
0
 

Expert Comment

by:happyboy25
ID: 8084894
Hi there,

For my case, I need the user to select a range of months from my 2 comboboxes namely Beginmonth and EndMonth.

Not only that, I need to specify the tpye of goods the report is in meaning the report can be created for let's say TV, VCR and Washing Machine.

SO my Select Query has a WHERE cluase which put Month([User_Input].[Date]) with the criteria to be between the two months selected.
The other WHERE clause is to equate the combobox (Type) to the type we have in the table.

DO advise
Thanks
BEGINNER

I am using my select Query to create the Crosstab query.
0
 
LVL 1

Accepted Solution

by:
Mike-Kieswetter earned 200 total points
ID: 8086925
HappyBoy25,
Try following these steps:
1. Make a copy of your select query in case it all goes wrong.
2. Open up your form and enter data into the boxes (that will return records).
3. Open up the select query in design mode.
4. Change the query type to 'Make Table qry', when prompted, give a name for the table e.g. 'TempData tbl'.
5. Press the Execute button (!) to create the table based on the data in the query.
6. Change the query type to 'Append Query' and select the 'TempData tbl'. Then save the query.
7. Create a new query to delete the data from 'TempData tbl'.
8. Make a new Xtab that has the output you need based on 'TempData tbl', Add this to the reports record source.
9. If you have a button on your form to open the report, add the following into the on click event before the report is opened:
'Delete old data from temp table
DoCmd.OpenQuery ("Delete TempTbl qry")
'Add New data to temp table
DoCmd.OpenQuery ("Append Data qry")

Have a go and post any problems you encounter!

Mike


0
 

Expert Comment

by:happyboy25
ID: 8099818
Hi Mike-Kieswetter

This sounds quite mechnical to me.
Do I have to set all this in Macro ?

For step 2, the data is the date range is it ?

Can I make it to be transparent to the user ?

Thanks
Beginner


0
 
LVL 1

Expert Comment

by:Mike-Kieswetter
ID: 8101613
Hi HappyBoy25,
The process described above is all for setting the thing up and is done once only.
Once point 9 is done all you need to do is push the button to run the report! The user is oblivious to the rest.

Point 2 was about you putting in date and product info into your form so the select query has some records in it for the subsequent development stages.
0
 

Author Comment

by:azrat
ID: 10117405
Good stuff.  Sorry it took my so long to respond.
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
Each year, investment in cloud platforms grows more than 20% (https://www.immun.io/hubfs/Immunio_2016/Content/Marketing/Cloud-Security-Report-2016.pdf?submissionGuid=a8d80a00-6fee-4b85-81db-a4e28f681762) as an increasing number of companies begin to…
This video teaches viewers how to encrypt an external drive that requires a password to read and edit the drive. All tasks are done in Disk Utility. Plug in the external drive you wish to encrypt: Make sure all previous data on the drive has been …
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question