Forms and Reports from CrossTab queries

Hi All,

I have read a few postings in this area, but they all seem to be very 
specific to particular situations. Can someone please guide me towards the 
best solution to my dilema?

I have a form with two list boxes which contain many variables (answers to 
specific questions from a survey). When two different questions are slected I 
run a cross tab query resulting in a dymanic result of rows and columns based 
on the selections.

I now want to display the result as a datasheet on my form and in a similar 
fashion as a report.

Any help is, of course, greatly appreciated.

Regards,

John Dumay
0
Utf
1/3/2008 7:11:02 PM
access 16762 articles. 3 followers. Follow

5 Replies
425 Views

Similar Articles

[PageSpeed] 40

(...)
> I now want to display the result as a datasheet on my form and in a 
> similar
> fashion as a report.

You can choose simple way or difficult...

The simple one is such:
1. Put subform on Your form
2. As SourceObject in Your subform set:
Query.YourCrossTabQuery
3. That's all ;-)

The difficult way... Hmmm...
This simple one will suffice You maybe? ;-)

K.P.
www.access.vis.pl 


0
Krzysztof
1/4/2008 9:15:37 AM
Hi K,

thanks for the input. this works fine for the current cross tab result, but 
when I change the cross tab to different varaibles I get errors because the 
field and row names are no longer the same. See below.

How can i make this process dynami so it updates the form with the proper 
header and row names?

Regards,

John

Row_Category	A job is no longer a necessity for me	Financially secure	It is 
a real struggle	Must have a job in order to make ends meet	Must have a job so 
I or we can get ahead	Wealthy
Arrogant	#Name?	#Name?	#Name?	#Name?	#Name?	#Name?
Distant	#Name?	#Name?	#Name?	#Name?	#Name?	#Name?
Neutral	#Name?	#Name?	#Name?	#Name?	#Name?	#Name?
On your side	#Name?	#Name?	#Name?	#Name?	#Name?	#Name?
Uncaring	#Name?	#Name?	#Name?	#Name?	#Name?	#Name?

"Krzysztof Pozorek [MVP]" wrote:

> (...)
> > I now want to display the result as a datasheet on my form and in a 
> > similar
> > fashion as a report.
> 
> You can choose simple way or difficult...
> 
> The simple one is such:
> 1. Put subform on Your form
> 2. As SourceObject in Your subform set:
> Query.YourCrossTabQuery
> 3. That's all ;-)
> 
> The difficult way... Hmmm...
> This simple one will suffice You maybe? ;-)
> 
> K.P.
> www.access.vis.pl 
> 
> 
> 
0
Utf
1/4/2008 11:38:02 AM
(...)
> thanks for the input. this works fine for the current cross tab result, 
> but
> when I change the cross tab to different varaibles I get errors because 
> the
> field and row names are no longer the same. See below.
>
> How can i make this process dynami so it updates the form with the proper
> header and row names?

No, no... I'm afraid, that oneself we don't understand. In my solution, 
subform control does not contain form object, but query only.

Put in to form empty subform control named SbForm1 and then set its 
SourceObject property. In VBA code would look it like this:

Me!SbForm1.SourceObject = "Query.YourCrossTabQuery1"
Me!SbForm1.SourceObject = "Query.YourCrossTabQuery2"

Or you can build Your crosstab query dynamically:
CurrentDb.QueryDefs("YourCrossTabQuery").SQL = "TRANSFORM ..."
Me!SbForm1.SourceObject = "Query.YourCrossTabQuery"

K.P.
www.access.vis.pl 


0
Krzysztof
1/4/2008 12:34:48 PM
Hi K.P.,

Now i understand!

Workd like a charm in the Form. Will now build the report.
many thanks for your assistance.

Regards,

John

"Krzysztof Pozorek [MVP]" wrote:

> (...)
> > thanks for the input. this works fine for the current cross tab result, 
> > but
> > when I change the cross tab to different varaibles I get errors because 
> > the
> > field and row names are no longer the same. See below.
> >
> > How can i make this process dynami so it updates the form with the proper
> > header and row names?
> 
> No, no... I'm afraid, that oneself we don't understand. In my solution, 
> subform control does not contain form object, but query only.
> 
> Put in to form empty subform control named SbForm1 and then set its 
> SourceObject property. In VBA code would look it like this:
> 
> Me!SbForm1.SourceObject = "Query.YourCrossTabQuery1"
> Me!SbForm1.SourceObject = "Query.YourCrossTabQuery2"
> 
> Or you can build Your crosstab query dynamically:
> CurrentDb.QueryDefs("YourCrossTabQuery").SQL = "TRANSFORM ..."
> Me!SbForm1.SourceObject = "Query.YourCrossTabQuery"
> 
> K.P.
> www.access.vis.pl 
> 
> 
> 
0
Utf
1/4/2008 6:32:00 PM
hey
"Krzysztof Pozorek [MVP]" <access@vis.pl> wrote in message 
news:O4LmgJrTIHA.3916@TK2MSFTNGP02.phx.gbl...
> (...)
>> I now want to display the result as a datasheet on my form and in a 
>> similar
>> fashion as a report.
>
> You can choose simple way or difficult...
>
> The simple one is such:
> 1. Put subform on Your form
> 2. As SourceObject in Your subform set:
> Query.YourCrossTabQuery
> 3. That's all ;-)
>
> The difficult way... Hmmm...
> This simple one will suffice You maybe? ;-)
>
> K.P.
> www.access.vis.pl
> 

0
abraham
1/5/2008 1:32:40 AM
Reply:

Similar Artilces:

Displaying gif images in a report
I have an Access 2003 database and I would like to display images in a report based on the value in a datafield. For example, I have a field in a table called "photo" that contains values like "photo01.gif." These names tie to gif files located in a folder called "C:\images." I would like to set the report to point to the image folder and retrieve and display the image for each record as the report is processed. Is this possible? I tried to work with the image control and OLEbound control but there does not seem to be a way to configure the...

Creating form in Outlook 2003
Hi, I designed a form in outlook. It is a form to send orders to different providers and I would like to put a check box that when checked the appropriate email is automatically entered in the "to" field. Also that same check box should enable a text box. I am not too sure how to do that, I guess it would take the use of VBA but I haven't use it since about 7 years and I've forgotten how to do it. Alittle help would be aprreciated. Thanks "archon" <archonsoft@gmail.com> wrote in message news:e75a1aee-d2d3-464b-9674-1bc0d342e0c2@t42g2000hsg.googlegroups.com...

Webbot Form question
I have scaled through the questions and still i am no wiser to solving my problem. I am using the forms and have a submit button which i require to go back to my email account. etc Now i do not plan to publish this website i have developed i have used the save as option to convert it to a web page. the file is stored on our server and appears on our desktop, so really it is functioning like a website just not publish via HTTP. Every item works as it should like a web apge however i get this webbot porblem like others. how do i solve the problem if i am not technically pulshling the we...

Outlook Forms Admin
I have a custom contacts form in a Public Folder and I want it to be the default for items like Palm and vCard sync'd contacts. I have the form published for the folder and all new contacts created within Outlook use the form, but these 2 examples do not. The existing items converter does not work (the Word Macro version does, but is a pain to explain to anyone) in Exchange. Any thoughts to reset the default form for these items? Thanks! -- Remove 'spam' from email address to contact me directly Does this work? http://www.outlookstore.com/download/DocMessageClass.zip -- ...

Query parameter 07-12-07
Hi I have a query (qry_CourseEvalutaionByTeam) that shows ratings (1-10) that training courses have been given by different teams. I have a form frm_CourseList that has a combo box that lets me choose the course that i want to see the rating for by team, this combo box is the parameter for the query. The query works fine. What i'm trying to do is have a very basic bar chart in a report that shows the course along the bottom and x bars for the teams showing the rating. But when i create a chart using the wizard and run a command button from the form to open the report i get the mes...

Dynamic report printing
I have a report that is based on a query. The report is sorted on the field "Type". I'd like for the user to be able to print only seleted sections of the report based on which "Type" sections they need to print. I was thinking of a form where they could select which types to print. However, the user can add new types, so I'm I can't put it together how to create the form for printing. Any suggestions? -- Thanks, Jason If they only need to select one "type", you can put a combo on the form, with a command button to OpenReport. It would be like...

How do I... On opening file from hard disk, open a form
Hello, Microsoft Access 2003. I would like my Main Menu form to open when a user opens my database. So far I am in no way networked or web based, I'll probably get there but right now I have a file (Inventoryv4.mdb) that resides on the user's hard drive. when she opens this file, I would like my main menu form to open, instead of her having to go through the little task pane and dbl clk on frm_MAINMENU. How might one do this? ***Note: I need the task pane with all the tables queries, forms etc to still be there, just in the background, preferably minimized, and the main men...

reports #6
when i command REPORTS->INCOME EXPENSES->ACCOUNT TRANSATIONS, i receive the error message There has been an error reading or writing the file "." , and if i say OK , then the msmoney abort. Sometimes i receive the message RUNTIME ERROR! i use MSMONEY 2005 SMALL BUSINESS In microsoft.public.money, jose wrote: >when i command REPORTS->INCOME EXPENSES->ACCOUNT TRANSATIONS, i receive the >error message There has been an error reading or writing the file "." , and >if i say OK , then the msmoney abort. >Sometimes i receive the message RUNTIME ...

Modify Forms
I have logged into the system and modified the values of the Leads form. I have changed the "Topic" field to no constraints and also changed the "Industry" fields pick list. I have previewed the form and all looks ok. The issue is when I open an existing lead or create a new lead the chnages have not been applied. You need to publish the changes via Deployment Manager and then issue an IISRESET. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Thu, 2 Dec 2004 09:03:04 -0800, "Modify CRM Forms&...

How do I create a query field in Excel
I have a sheet with 9000 part numbers on it. Is there a way of say at the top of the sheet having some sort of search box, so I can type in the part# and it goes straight to that part? Thanks Hi, Method1: Using the Filter feature. -select a cell in the data -goto menu Data>Filter>AutoFilter - now your data header has dopdown boxes. From the part# header select the part#. This will make all show only rows with this part# Method2: -Select column Part# - menu Edit >Find Method 3: with code - In a code module, paste the following sub FindAndGo Also change the 'CHANGE HERE...

Form Variable no longer passes to new form
Button Command39 on main form, when clicked opens the SWLicensesForm. Button WinXP_Click() on the SWLicensesform, when clicked adds a new record and fills in some fields for me. My problem is in the POTEMP and NuaNumTmp variables being passed from the main form to th SwLicensesform.. These are no longer being passed from the main form to the SWLicensesform, and I can not fiure out why. I have a backup of the database that is about 8 days old that works fine. The only thing I have done between the backup and the variables not working is to compact the database. There are no error messages, ...

command button to a new form in same window
I need help in creating a command button that will open a different form in the SAME window. I know how to create a button that will open a form in a DIFFERENT window, but not in the same window. Is that possible? help would be much appreciated! thanks! That's not practical, Andy. If it's absolutely crucial, you could create a form that has a subform occuping its whole height and width. Then change the SourceObject of the subform control in code, e.g.: Me.[MySub].SourceObject = "Form12" -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access u...

Add contact phone number to case form
Is there a way to add the Contact Business Phone number to the Case form? The Responsible Client field shows, but not the phone number. There are a couple of ways to achieve this depending upon your requirements. You can use a JavaScript web service query from the Responsible Contact's OnChange event to populate a custom telephone number field on the case form with the appropriate field from the contact record. But if the contact's telephone number changes, the corresponding field on the case form won't get updated. Instead of displaying just the contact's full name in...

Parameterize this crosstab query
The simple query below serves my purpose, but I would like to improve upon it by having it prompt the user for the years. In otherwords, replace 2002 and 2006 with YEAR1 and YEAR2. Thanks for the help. I really appreciate it. Mike TRANSFORM Avg(Vehicles.vehicles) AS AvgOfvehicles SELECT Vehicles.COUNTY FROM Vehicles WHERE (([year] Between 2002 And 2006)) GROUP BY Vehicles.COUNTY PIVOT Vehicles.YEAR; On Jul 16, 2:56 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com> wrote: > Try this --- > PARAMETERS [Enter year 1] Text ( 255 ), [Enter year 2] Text ( 255 ); > TRANSFORM...

"Form Source <StoredProcedureName> not found ... "
Hi. I have a disconnected Access 2002 project (.adp) that runs under Windows XP Pro. Every 2 seconds, the project connects to a SQL 2000 database and invokes a stored procedure in the database to look for new data. If data is found, the project invokes other stored procedure to fetch the data. The project places the data in recordsets. The project displays some of the data in a subform. The Record Source for the subform is set to the recordset returned by one of the stored procedures using VBA code. Once the data is obtained and displayed in the project (including the subform), the connection...

Query, Change Expression if True
ExceptWhere tblHorseDetails.Invoicing is True (Check Box) I am trying to stop this query reconizing any OwnerID, HorseID that is True Thanks for any Help..........Bob SELECT Format(tblHorseDetails.OwnerPercent,"0.0%") AS Expr1, IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ' ') & IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & ' ') AS OwnerName, tblOwnerInfo.OwnerID, tblHorseDetails.Invoicing FROM tblOwnerInfo, tblHorseDetails WHERE (((tblOwnerInfo.OwnerID)=[tblHorseDetails].[OwnerID]) A...

BUG REPORT: Detailed Sales Report Calculations
On my Detailed Sales reports, when showing the columns: Qty Sold, Sold Price, Total Sales, Cost and Profit; the Cost & Profit columns do not calculate properly. It appears that the Cost and Profit columns are not Extended by Qty Sold, resulting in inaccuracies wherever Qty Sold <>1. This applies to both returns (negative Qty Sold) and sales where Qty Sold is 2 or more. Total Sales does calculate properly (Qty Sold * Sold Price). The end result is a report that has practically no value other than showing Total Sales. Yes, the Department Cost/Sales report does provide an accu...

formatting reports
I've gone forward and customized a report from the standard options-"spending by category". When viewing on the screen-I can see General Categories in bold font, with the Sub-Category in regular font. Yet when I print the report out-nothing is bold. It makes it difficult to read. It would be real nice if I could customize how the report looks, with indenting, and font adjustments-so that it would be easier to read. Any suggestions on ways to do this...other than exporting to another program and then formatting that. ...

Counting in a Query
I trying to calculate the number of groups with 2 to 3 employees, the number of groups with 4 to 5 employees, the number of groups with 6 to 24 employees and the number of groups with 25 or more employees. I would like to do this in one query rather than build a query to count each group. How can I accomplish this? On Mon, 4 Feb 2008 13:05:01 -0800, JeffH <JeffH@discussions.microsoft.com> wrote: Air code follows. I would first build a query (named Query1) to get the number of employees in each group. Something like: select count(employeeID) as CountInGroup from Employees group by...

Automatic Date Updating in Open Form
I have a form used to enter data into a table. I have a date textbox that I would like to code to automatically have enter the current date even when the form is left open through to the next day. Also, this date textbox must allow the user to make changes to the date. Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201001/1 The question then becomes "current to what?" Do you want the date/time that the form opens? closes? performs a certain task? Each of those things have events that can be used to put a ...

Ability to leave parts of form blank, thereby selecting all.
I want to give my users a form where they will give some information and a query will be run based on that. However, I want this to be a bit more dynamic than just a simply query. Let me try to explain in more detail. One of the forms in question will run a query that pulls all training classes an employee has had. The user would select the employee name and search by that. However, I also want to give the user several other options that they either can choose to use or not. For example, I would want to give them a place to also enter the instructor name. So, they could choose the emp...

Create multiple reports on the one form
Hello all, I have a form [Orders Retail] and sub-form [Orders Retail Calulated] which calulates totals by a query and shows an order with wholesale prices. On that form I have a command button that creates a report called wholesale order. I wish to produce another report with the same order information yet changing the wholesale price to manufacture price. Both these prices are stored in the same table. Prices are subject to a combo box called model and type. Thankyou On Tue, 1 Dec 2009 18:25:01 -0800, PeterC <PeterC@discussions.microsoft.com> wrote: >Hello all...

Microsoft Forms 2.0 Label(FM20.dll) in VC2005
I use VS2005 standard edition to create MFC dialog based app, put MSForm2.0Label activeX control on the dialog, it is ok to show it. After I move "CPaintDC dc(this);" to the first line of OnPaint function, the label is not showed when I run it. CPaintDC dc(this); //this will cause the label not be showed. if (IsIconic()) { // CPaintDC dc(this); // device context for painting ..... This is worked in VC6. Thank you very much in advance. ...

Update Query Parameters
I have an update query, that displays a prompt for a user to enter a quantity. At the moment when the query is run, it looks shows this: [Stock]![QtyOnHand]+[Forms]![frmOrder]![fromOrderItems].[Form]![Qty] Is there anyway to make this a bit nicer, by displaying something like 'enter quantity' instead? G Glenn Peters wrote: > I have an update query, that displays a prompt for a user to enter a > quantity. At the moment when the query is run, it looks shows this: > > [Stock]![QtyOnHand]+[Forms]![frmOrder]![fromOrderItems].[Form]![Qty] > > Is there anyway to make t...

Query about passes of OPNAVINST 5239. 1A Wipe algorithm ?
Hi All, I am developing a file/data wipe application and I want to implement OPNAVINST 5239. 1A algoritham. It is a 3 pass overwrite algorithm, but I don't know what are those passes. Can any one help me in this implementation? Thanks in advance.. Rajeev wrote: > Hi All, > > I am developing a file/data wipe application and I want to implement > OPNAVINST 5239. 1A algoritham. > > It is a 3 pass overwrite algorithm, but I don't know what are those passes. > > Can any one help me in this implementation? > > > Thanks in advance.. 3 passes m...