Cross-tab query - dynamic row headings for subform

I am just learning about Cross-tab queires.

I have a ct query which has time-worked as the row headings and labor
code as the column headings.  I want to view this in a form, so I used
the wizard to create a datasheet form (sfm_ctab1), then made sfm_ctab1
be a subform on my main form.

It looks okay with column headings such as
000   001   003   009        etc.

Now say an employee work a few hours on labor code 002.  When I run
the query there is a new column for 002.  But when I view it on the
form it is not there.

What is the best way to make it so that the column headings on the
form always match the column headings in the query?

Thanks.

- Paul Schrum

0
paul
2/28/2007 5:52:20 PM
access.formscoding 7494 articles. 0 followers. Follow

1 Replies
1135 Views

Similar Articles

[PageSpeed] 28

1. Open the query in design view.

2. Open the Properties box (View menu.)

3. Beside the Column Headings property, list all the possible headings you 
could expect, e.g.:
    "000", "001", "002", "003", ...

4. Save the query.

The crosstab will now generate a field for each of these, regardless of 
whether there is any data to put in the column or not. Since the columns are 
all present, you can go ahead and build your subform knowing that the 
crosstab will supply all the possible fields.

You may also benefit from these ideas:
    Crosstab query techniques
at:
    http://allenbrowne.com/ser-67.html

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<paul.schrum@gmail.com> wrote in message
news:1172685140.802836.230050@a75g2000cwd.googlegroups.com...
>I am just learning about Cross-tab queires.
>
> I have a ct query which has time-worked as the row headings and labor
> code as the column headings.  I want to view this in a form, so I used
> the wizard to create a datasheet form (sfm_ctab1), then made sfm_ctab1
> be a subform on my main form.
>
> It looks okay with column headings such as
> 000   001   003   009        etc.
>
> Now say an employee work a few hours on labor code 002.  When I run
> the query there is a new column for 002.  But when I view it on the
> form it is not there.
>
> What is the best way to make it so that the column headings on the
> form always match the column headings in the query?
>
> Thanks.
>
> - Paul Schrum
> 

0
Allen
3/1/2007 10:29:44 AM
Reply:

Similar Artilces:

Text writign in merged rows
\sometimes, I merge several rows and write my comments. Let's say. three rows are merged to form a big box. When I write texts, it starts from the bottom of the merged box (i.e., row). I could not figure out how to write texts from the top of the merged box. Regards, Craig <Format> <Cells> <Alignment> tab, Expand the "Vertical" box and choose "Top". -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------...

Querying a dataset filled from XML file
I have a simple application that uses an XML file to store it's data. That means I don't have a seperate database. In order to read the data I created a DataSet that reads my XML file with the .ReadXml() method. 1. I want to manipulate the resulting dataset, preferably with SQL queries. But the documentation states that I need to have a data connection to do this. But if I am only using an XML file, how do I do this without a data connection? 2. When I fill the dataset I have no trouble reading the column names, but how do I read the attributes? For instance, this is a portion...

Microsoft Query rejects "nz" function in Access Query
Since MS Query can't cope with the nz() function used in Access Queries, is there any workround I can use (hopefully without rewriting all my database queries)? ...

Can a form linked to an autoLookup query update another table
I created a form that is updated with an AutoLookup query from another table once a certain linked field is filled in. Can this form be used to add a record to the source table with the updated information? When that record is saved, the record is added to the form's source table - assuming the form is bound to the source table. How does your form save the record? Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "oterosuz" <oterosuz@discussions.microsoft.com> wrote in message news:3608D905-8769-435E-B5E5-7B0F566AFD63@microsoft.com......

Creating Cross-Project Links
Hi 1). What is the usage of Creating Cross-Project Links ? As I understand, "Cross-project links are useful when two or more projects have only a few tasks that affect one another. For example, a milestone within a development project might trigger a task in an advertising campaign project." 2). How it is implemented in Project 2007 ? When I copied the task from the source project into my project ( target project ) using "Paste Special", the new task I created become another task in my target project. My observation is both task ( ie. task in the source...

Remoew row highlighting when workbook is closed
Hi, Found a bit of code on the web (below) to highlight the active row but need something that will remove the highlight (not the data) when the workbook is closed so that the next time you open it nothing is highlighted. At present when you re-open it a row is still highlighted and then when you select a cell you end up with two rows highlighted! Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Static OldRng As Range On Error Resume Next Target.EntireRow.Interior.ColorIndex = 6 OldRng.EntireRow.Interior.ColorIndex = xlColorIndexNone...

Customer Cross Reference
2nd Request - Posted a few Months Ago, No Reply --------------------------------------------------------------------------- Has anyone out there used the Customer Part Number Cross Ref v8.0? I loaded a customer list the other day. When I entered the Customer Number on the Sales Order, it was instantly converted over to our number. Worked Great... I'm wondering if there is a way to automatically reference the original Customer Number on the Packing List and/Or Invoice or is that something that needs to be manually typed in. I tried adding notes, but I don't know if they carry down...

No "Move Mailbox" cross site migration
Hi everybody: We have a forest with subdomains, the main domain is AD 2000 and has 1 Cluster Exchange 2003 SP1, 1 Server Exchange 2003 SP1 with ADC SP1 and SRS, and 3 Exchange 5.5 in the same Exchange 5.5 site. We have 8 subdomains in AD2000 with Exchange 5.5 SP4 server with hotfix applied for cross-site in each subdomain, and they had an own site in Exchange 5.5. We are trying to consolidate and move all mailbox and public folders to the Exchange 2003 cluster, we did the domainprep in each subdomain and create a RUS in the Exchange 2003 cluster to each subdomain. When we tried to move cros...

Tab names to a list?
Is there a way to get a printout of just the names on the tabs of each worksheet in a workbook? I have one that has a ton of them and it would be just so much easier to get a printout than try to copy each name manually. Is there any way? Thanks. :oD This sub will make a list in col F Sub listshtnames() For i = 1 To Sheets.Count Cells(i, "f") = Sheets(i).Name Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "StargateFanNotAtHome" <IDon'tAcceptSpam@NoJunkMail.com> wrote in message news:hh95d49s4c8egbeuanjul52c...

Pass through query speed
I have written a pretty convoluted pass through query to a SQL Server database. A parameter is collected using a simple form that uses code to modify the query definition before running it. The first time I run the query with a revised parameter it takes about a minute to return the required data. If I then run it again without changing the parameter it takes around 3 seconds. Obviously I prefer the 3 second response time. Can anyone explain why it takes so long the first time? Is the data being cached by SQL Server or Access? Is there some kind of compilation process going on invisib...

keep tab order after protection
I used the Insert, Name, Define, OK function in EXCEL to create a tab order, which works great. However, after protecting the sheet, the tab order doesn't work. Is there anway to maintain this order and protect the worksheet. Any help would be appreciated! Thanks! IF I understand you, the cells desired must be unlocked. -- Don Guillett SalesAid Software donaldb@281.com "Rawley" <Rawley@discussions.microsoft.com> wrote in message news:BB0D352F-031F-41F2-9E08-1B023791CEC6@microsoft.com... > I used the Insert, Name, Define, OK function in EXCEL to create a tab order,...

Query re Microsoft CRM-Exchange E-Mail Router
We're thinking about purchasing Microsoft CRM. Question: if an email is directed to an address that shows up in Microsoft CRM, is the email a) no longer stored on the Exchange Server but only on the SQL server pointed at by CRM b) on both c) just on the Exchange Server but incorporated from there by the CRM system. Where can I find more information or documentation on this feature of Microsoft CRM? In general product description documentation seems very weak. All inbound e-mail goes to Exchange just as it would if you didn't have the CRM Router installed. When installed and...

update query with DMax?
Hi All I have to tables and in the first one I have a key (SzemelyId) and in the other table Salary: SzemelyId, Salary, Date, Active) is Szemely Id is foreign key. Each SzemelyId has one or more "Salary" and I would like to make an Update query which select every person last (max) salary and make the "Active" checkbox is TRUE. Just for the max salary. Thanks for your help Janos Remove the Active field! It sounds like it is completely dependent on the foreign key + Date field, i.e. if it were True for a record that was not the latest date, that would be an error. It th...

Gridlines Will Not Print on Row 12
Hi guys, hoping you can help me out. We have some spreadsheets that will not print the gridlines between rows 12 and 13. We have tried using different printers, turning off gridlines then turning them back on, etc. We are using Excel XP, but the problem first surfaced with Excel 2000. This problem effects all spreadsheets, unless there are borders on that row. In print preview, it shows the gridlines, but when it is printed....No dice! Any ideas would be greatly appreciated! Zack ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View an...

Cross table
Hi everybody !! i come again here with a problem i can seem to solve :s i hope someone will be able to give me a hand on this... here it is : i have a sheet with 2 rows: location and activity. several activities can be found in one location, even several of the same activities. schematic exemple location activity 1 A 1 B 2 A 3 A 3 B 3 C 4 B 4 C I would like to create something that give me as a result a sheet where we can see the spatial relations between the activities. so i would have all...

Combining an update query with a parameter query
I wanted to know how you can update data in a table using the update query and a parameter query. How did you want to run this update query? In query design, you can just put your parameter in the query. To run it programmatically, you can then use RunSQL in a macro or code. A better programmatic approach might be to build the SQL statement in code, concatenating the parameter value into the string. Then Execute the string itself: you don't need a saved query. The advantage is that you know whether it worked or not. Here's an example: Action queries: suppressing dialogs, whi...

Expression in Query 01-11-08
How to create Expr in Query Field1/Field2 if Field 2 is zero return Field1? SomeName: IIf ( [TableName]![Field2] = 0 , [TableName]![Field1] / [TableName]![Field2] , [TableName]![Field1] ) -- Wayne Manchester, England. "Ty" wrote: > How to create Expr in Query Field1/Field2 if Field 2 is zero return Field1? Maybe like this --- SomeName: IIf ( [TableName]![Field2] = 0 , [TableName]![Field1], [TableName]![Field1] / [TableName]![Field2] ) -- KARL DEWEY Build a little - Test a little "Wayne-I-M" wrote: > SomeName: IIf ( [TableName]![Field2] = 0 , [Tab...

modify macro to include more tabs
Hi, The macro below allows users to group/ungroup rows in a protected sheet. How do I modify this macro to either incorporate more worksheets or apply it to the entire workbook? Thanks, Charlie Sub workbook_open() With Worksheets("Sheet1") .Protect Password:="password", userinterfaceonly:=True .EnableOutlining = True End With End Sub Try something like this, not tested Sub workbook_open() Dim wks as worksheet For each wks in Activeworkbook With wks) .Protect Password:="password", userinterfaceo...

Refreshing filter on subforms on pages
I have a search form with 2 pages and 2 subforms. When I set the filter it does not work unless I click on the page or hit the search twice. I put a msg in the code and it shows the filter being set. I tried to do a requiry on the subforms but got the same results. Below is a portion of the code. Me.sbfrmTownCalls.Form.Filter = strWhereC Me.sbfrmTownIssues.Form.Filter = strWhereI Me.sbfrmTownCalls.Form.FilterOn = True Me.sbfrmTownIssues.Form.FilterOn = True MsgBox "Filter Set" 'DoCmd.Requery...

Other symbols for delimiter other than TAB
Dear all, Hope you can assist me on this matter: How do I program the Excel so that if I save it as a text file the ! symbol (or any other symbol) is the delimiter instead of the TAB? Currently what I do is: 1. Copy all the cells and an additional blank cell after the last. 2. Paste into Wordpad. 3. Copy the TAB; run the Replace All command and replace the TAB with the symbol of my choice. Is there a way to automate so that when I save the spreadsheet into text format, it will automatically use my symbol instead of the TAB? If there is a way, can you show me how to program it? Samp...

IE8 dead tabs
Windows 7, 32 bit. All required updates applied. IE 8.0.7600.16385 If I right click on a link and select "open in new tab", a new tab opens but it doesn't open linked web page, "connecting" message appears only. These actions don't fix this problem : -Removed antivirus software. -Tried at safe mode with networking -Cleared Internet Options>Connections>Lan settings>Automatically detect settings -Cleared Internet Options>Advanced>Enable 3rd party browser extensions -Tried with IE8 with "no add-on" Thank you. Hi, Try the r...

SQL Server 2005: a complex query
Hi I want to read a product structure from an external table into SQL Server database. The source table structure is like: row_id parent_article child_article parent_row_no .... I.e. when there is some product with article number 1000 and it is composed from articles 1100 and 1200, then there exists 2 rows in source table: 991, 1000, 1100, 1, ... 992, 1000, 1200, 2, ... When article 1100 again is composed from 3 different articles 1110, 1120 and 1130, then there are additional 3 rows in source table: 123, 1100, 1110, 1, ... 124, 1100, 1120, 2, ... 125, 1100, 1130, ...

cross-reference
I created a cross reference in word to a section of my document that has a heading of 'Heading 3' I used Heading for the reference type and for Insert reference to I used Heading Number (Full context). I was expecting to get something like this 4.8.2 but instead I just got 0. is there some setting somewhere that needs to be tweaked? Make sure that numbering is correctly applied to your headings. If you are using Word 2007, you can choose the "1 Heading 1 1.1 Heading 2 1.1.1 Heading 3" format at Home tab | Multilevel List. If you are using Word 2003, see http://www...

Sorting the result of an XPath query?
Is it possible to sort the result of an XPath "select" query? I need to return a subset of a large xml file, and XPath is the way to go. I'd like to sort this subset afterwards? This seems like a common task so should be quite trivial. I've seen the same thing done but by first pumping the subset into an XmlDocument then applying XSLT. Is there an easier way, possibly working directly on the XPathNodeIterator? Thanks Ben Fidge Ben Fidge wrote: > Is it possible to sort the result of an XPath "select" > query? Sure it's possible, here is MSDN s...

Column Heading Query
I am using Outlook 2003. I have created an extra folder to move sent items into. There is no 'To' column displayed. I have been able to create a 'To' column. But, the 'To' address of each entry is not displayed in this column. How do I achieve this objective? TIA "KiwiBrian" <briantoz@ihug.co.nz> wrote in message news:h9bkks$kfh$1@lust.ihug.co.nz... >I am using Outlook 2003. > I have created an extra folder to move sent items into. > There is no 'To' column displayed. > I have been able to create a 'To' column. > But,...