dynamically choose columns in query

I'm trying to create a query such that when the user runs it, the proper 
columns (fields appear).  We have 12 columns, one for each month of our 
fiscal year (which goes from July to June).  Basically, we run the query once 
a month.  When we run it, for example in April, the months prior to April & 
including April (this would be July thru April), will have $0.  The months 
after April (May and June) will have dollars.  When we run this query in 
April, we'd rather not see each of the columns for July thru April because 
all the records have all $0 in those columns.

Searching the web, I've seen things regarding crosstab queries and union 
queries, but I don't think I've figured them out.  One way I think I could do 
this would be to have 12 queries (each one including/excluding the 
appropriate months).  Then, on a form the user selects the appropriate month 
(or maybe the month would be discerned from the run date if I could figure 
that out).  Then, the appropriate query would run based on the month selected 
(or from the run date).  But I’d rather not create 12 queries if I didn’t 
have to.

In reality, we have several groups of fields which have a field/column for 
each month, so if there’s a way to eliminate $0 fields/columns from the query 
(which we later export to Excel for manipulation), that would be helpful.

The sources for my query are 2 joined queries, which are the result of many 
queries.  I'm using MS Access 2003.

Thanks,

~Mark

0
Utf
4/14/2010 4:47:01 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
768 Views

Similar Articles

[PageSpeed] 28

>>We have 12 columns, one for each month of our fiscal year (which goes from 
July to June).
You need to fix your data structure.  What you have now is a spreadsheet and 
not a relational database.  Do not have a field per month, have a date field 
and a data field.

Use a union query to correct your data like this --
SELECT #1/1/2010# AS MyDate, [January] AS MyData
FROM MyTable
UNION ALL SELECT #2/1/2010# AS MyDate, [February] AS MyData
FROM MyTable
UNION ALL SELECT #3/1/2010# AS MyDate, [March] AS MyData
FROM MyTable
    ....
UNION ALL SELECT #12/1/2010# AS MyDate, [December] AS MyData
FROM MyTable;


-- 
Build a little, test a little.


"mark" wrote:

> I'm trying to create a query such that when the user runs it, the proper 
> columns (fields appear).  We have 12 columns, one for each month of our 
> fiscal year (which goes from July to June).  Basically, we run the query once 
> a month.  When we run it, for example in April, the months prior to April & 
> including April (this would be July thru April), will have $0.  The months 
> after April (May and June) will have dollars.  When we run this query in 
> April, we'd rather not see each of the columns for July thru April because 
> all the records have all $0 in those columns.
> 
> Searching the web, I've seen things regarding crosstab queries and union 
> queries, but I don't think I've figured them out.  One way I think I could do 
> this would be to have 12 queries (each one including/excluding the 
> appropriate months).  Then, on a form the user selects the appropriate month 
> (or maybe the month would be discerned from the run date if I could figure 
> that out).  Then, the appropriate query would run based on the month selected 
> (or from the run date).  But I’d rather not create 12 queries if I didn’t 
> have to.
> 
> In reality, we have several groups of fields which have a field/column for 
> each month, so if there’s a way to eliminate $0 fields/columns from the query 
> (which we later export to Excel for manipulation), that would be helpful.
> 
> The sources for my query are 2 joined queries, which are the result of many 
> queries.  I'm using MS Access 2003.
> 
> Thanks,
> 
> ~Mark
> 
0
Utf
4/14/2010 5:58:01 PM
Reply:

Similar Artilces:

BCP Computed Column
Hi. I have a table with a computed column. When I try to import data into table through BCP it generates error due to computed column. So how to handle a computed column while importing data into table through bcp from a text file. Regards, Muhammad Bilal You need to use a format file that tells BCP to skip that column. See BOL for info on bcp format files. Tom "Muhammad Bilal" <MuhammadBilal@discussions.microsoft.com> wrote in message news:5AD65421-C599-4773-B56F-24841FE05D5A@microsoft.com... > Hi. > > I have a table with a computed column...

data labels from second column
Hi Column A is list of names (Bob, Sue, etc); column B is how much they collected (58, 12, etc); Column C is the date they did it on - so record 1 says Bob collected 58 on 10/07/07, for instance. I want to create a chart with the date for the x axis, amount collected as the y axis and data labels at each point within the graph giving the collector's name. So at X=12/07/07, y=58 I want it to say Bob within the chart area. Any help much appreciated. Regards Chandler On Mon, 3 Sep 2007, in microsoft.public.excel.charting, Chandler <Chandler@discussions.microsoft.com> said: &...

Dynamics Security Console and Dynamics Web Services
Hi All I need help I have created a server with windows server 2008 32 bit and installed MOSS I have done all the standard setups and created all the websites ect I have installed Dynamics Webservice witth SP3 When I run Dynamics Security Console and select applications and then SecuritySerivices. I get the following error "Could not access the SecurityService service. Unable to connect to the remote server" Please help Regards Laurie ...

Comparing Values In 2 Columns
Hi, i have 2 list of students names both of which are not up to date with each other and because of this one list has more students than the other. I want to search for students names and see if there is a match, if a match is found i need to copy the email address and paste it into the cell by the other name in the other list. See Below My spreadsheet has columns titled, (A)Display Name, (B)Display Email, (C)Sims Name and (D)Sims Email. I basically want to write a script that takes each individual display name in column A and searches in column C for an identical match, if a match is found i...

dynamic field content of parameter combobox
Greetings, I have to filter my Crystal 9.0 report with a parameter combobox field in Microsoft CRM. The data for this parameter field has to be loaded out of a database field dynamically. I have found the possibility to load database field values in a parameter field but this will not be dynamically. If there is a change in the database content the parameter field content will not be updated. I hope to create a combobox control is no problem. Is there a way to turn this into reality? Thanks for your help Thomas Ott (ITVT germany) ...

Calling VBA subroutine from a query?
Can I call a VBA subroutine from within an Access query? I wrote some English to Metric conversion routines in the Access VBA code and would like to run a query on the data that will return coverted values. I need to be able to execute this from outside the database (run the query from another program). Yes. The function must be a Public Function in a standard module. You use a calculated control to return the results of the query. ConvertedValue: MyFunction([SomeField]) -- Dave Hargis, Microsoft Access MVP "DavidY" wrote: > Can I call a VBA subroutine from within an ...

Button on Form to Run Report based on Query with Parameters
Hi Guys. I am busy working on a stock control database (and job control). When I have a Form Open, it shows several fields, including JobID (Which is the PK for that job) (frm_Invoicing) I also have a Query that when Run, it prompts for the JobIDNo. (qry_CustJobsInvStock) I have a report based on that query. (rpt_CustJobsInvStock) I have added a button onto the form (onclick -> DoCmd.OpenReport) to open and automatically print the report, But I just cannot figure out how to get it to get my JobID from the form, and to Automatically add it as a Parameter for the que...

How can I compare the column names from 2 tables and output them?
I have a requirement to place an alert if the field does not exist in my table, tblStaging. Can any one guide me to reframe this query with error handling messages? I know it is not good practice to use select * but I need to do this as my columns\field names change each time. INSERT INTO tblStaging SELECT * FROM tbl_XL,tblDetails; Thank you -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1 Access is a relational database. If your "table design" has the fields in your table(s) changing frequently,...

Pivot Table and adding a % column, that is not in original data
Hi, Is it possible to add a column for % calculations when the % column is not in original data? To clarify, my original data is as follows: Produt Sales Returns Date A 5 June B 6 June A 1 July A 1 September B 1 November When I run the pivot table, one of the columns I'm then looking to get is a total % of returns over sales , but I cant see how to include in a Pivot table. I can add it outside of the table, but that has problems ...

Customize global adress list column in a new mail
Hi! (I have Outlook 2007 and Exchange 2007). when i create a new mail message, hit the "To" button, my global adress list window appear when column by default: Name, Title, business Phone, Location, E-mail address, Company and Alias. Those column from Active directory of course. My question is: can I add/remove column in this window? For example adding "department" from Active directory? thx Since this is customized by your Exchange Admin, I would start by asking him/her if it is possible. This question can most likely be answered in an Exchange or W...

Two lines and pne column
I am trying to modify one of the custom charts to show two line and one column rather than two columns and one line. can anyone help. Ian - Forget the custom type. Make a chart with all three series as lines, select the one series, choose Chart Type from the Chart menu, and select the Column style you want. Voila, your first custom combination chart. There's more about Combination Charts on my web site: http://peltiertech.com/Excel/Charts/ComboCharts.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech....

How to choose an account to send e-mail
Hello all. I have Office 2003 / Windows XP SP2. I have three accounts (for working POP, hotmail and personal POP) When I send an e-mail Outlook takes by default the first one account. I always choose manualy the account I need but sometimes I forget or I choose badly. My question is: There is a way to select from which account the e-mail will be send? Plug-in or Option or other??? Thanks a lot. S There's a dropdown list to help you choose the account on the toolbar in = the mail message.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://w...

Dynamically change pick lists?
Has anyone found a workaround for the following : dynamically change the choices in one pick list based on the selection in a second, associated pick list. I know this question has been asked before, just wondering if someone found a resolution for it. I haven't tried it yet, but it is my understanding that you can write JavaScript code in the OnChange event for the first picklist that will modify the valid values in the second picklist. HTH, Dave >-----Original Message----- >Has anyone found a workaround for the following : > >dynamically change the choices in one pick...

One column into three
We have a table with about 40 columns and 120 Rows and we need to split each column into 3 columns only for certain rows - I'm guessing that we can't spilt a column only for certain rows as this would mess-up the entire column/row naming convention - so is there a way to automate the spliting of a column into 3 rows (i.e column A becomes A,B & C) and then merging each trio of cells (i.e. A1, B1, & C1) into one cell in the row and retaining the data that was in A1 and moving the data that was in B1 into D1 - for all 120 rows and then repeating the process for each colum...

Matching Zip coded in a Query
I have query that joins two tables using the Zip Codes (only show the records where the Zip codes match). The problem is that in one table, the listing of zip codes, it is 5 digit zip codes. In the main table some of the zip codes are 5+4 zip codes. My current query leave out the 5+4 Zip codes. I want all the records as long as the 5 digit zip codes matches. I quess I could delete the - and the last 4 digits on the main table, but it would be nice to retain the whole zip codes. Is there a way to do this? Thanks!!!!! Use Left(MainTableZipCodeField,5) to compare to the original ...

Queries and reports in access Need help please
Hello everyone , I have a little issue to setup a database. I created a table with approximately 20 columns. The columns have an entry of Yes or no base on a questionnaire from survey. What can I do to get a result of my table I would like to know how many "Yes" and "no" I have. I don't know how to add them or get this information. Thanks in advance GABRIEL Gabriel, The first answer to your question is that your table structure is not really the best for this situation, and it will be a lot easier if you can set it up differently. Is your database design set in ...

FRx
Is there any way to import values to a column of FRx 6.7 report from excel/doc file? Thanks in Advance, Arun. You can't "import" data but you can include data from a spreadsheet in an FRx report. See Chapter 4 of the FRx Report Designer User's Guide. -- Charles Allen, MVP "Arun" wrote: > Is there any way to import values to a column of FRx 6.7 report from > excel/doc file? > > Thanks in Advance, > Arun. ...

Query repeated values
Hello everyone. Sorry for such a newbie doubt, but since my Access and SQL experience is a bit “trial and error” I don’t even know what to search for here in the forums that so I can help myself. I have two tables A and B. First table has people’s NAMES field. Marc Wilson Andrea Smart Francis Junior Second table has a field with people’s ABILITIES but some are repeated: WORD – Marc Wilson WORD – Andrea Smart EXCEL – Francis Junior COREL – Andrea Smart COREL – Andrea Smart I’m trying to build a query to point out the duplicated lines, in this case: COREL – Andrea Smart ...

union queries
I need to build a query which combine records from a number of similar tables. Building a union query works great with "normal" records. Problem is, these tables contain attachments in some fields and MS ACCESS 2007 treat these fields as multi-value fields and does not allow building APPEND or UNION queries with multi-value fields. I am so frustrated with this, because I would like to base my searches and some other queries on this. PLEEEESe help Cheers Dawie Theron On Fri, 26 Feb 2010 05:54:01 -0800, Dawie Theron <DawieTheron@discussions.microsoft.com&g...

macro to search column
We're pretty good at writing macros but not that good. We a have columns containing duplicate information - for example: A A A C C F F F F and so on We're trying to write macro that will "simply" say; if a value in the column exists then copy it to the next column, but only copy it once (we only need to know if it appears once not x number of times in a column. OR goto the first cell; its value is "A" ; search the rest of the column for "A" , delete all OTHER instances of "A" (but keep the first instance); then go to cell below that and do the ...

Weird rounding in Sums of columns
I have a simple worksheet in which one cell should be the sum of a few others. It looks something like this: A2=A1*.133, A3=A1*.12, A4=(SUM A1:A3). My problem is that sometimes the sum in cell A4 is .01 less than it should be. I assume it has something to do with rounding to two decimal points. Is there a way to get A4 to equal the sum of the actual numbers shown in the cells and not a sum based on hidden strings of decimals? Sorry if this is more confusing than it should be. Thanks for any help. Yes, Go to Tools-Options-Calculation tab and turn on Precision as displayed. -...

Row, Column measurement?
Excel 2000. What are the units? Why aren't they the same? If the row is 20 tall & the column 20 wide, why don't I have a square cell? How do I get one? -- Thanks in advance... Bob Try Format,raw height 11.25 Format,column width 1.43 "Bob Newman" wrote: > Excel 2000. > What are the units? Why aren't they the same? If the row is 20 tall & the > column 20 wide, why don't I have a square cell? How do I get one? > > -- > Thanks in advance... Bob > > > Nope. Bob "Sakai" <Sakai@discussions.microsoft.com...

Some queries of User Interface Thread
Hello All Recently I started working on User Interface thread , But certain things are quite confusing. Hope I will get some clarification from all of you. 1: The thread does not receive user message sent by PostThreadMessage when I am doing some lengthy operation in Run() method (overrided Run() function ) , Can you tell me why it is so ? I have written a handler for user defined message, But it's of no use when I am doing lengthy operation in Run(). 2: I read that the Run() function provides a default message loop for user interface thread. How Run() function works internally ? How it...

Wretched Queries!!!!!!
Hi,In my database each entry has several scores assigned to it (fordifferent attributes). Each score is in the 1 -4 range.I would like to construct a query that will select those entries thathave even so much as a single attribute that scores below a 4 and, ifpossible, only return the attributes that fell below standard (4).The database will have each employee (entry) assessed on a dozen or sodifferent attributes, each of which will be rated with a score of 1 -4. I would like to be able to find out which employees had a score inany attribute below 4 and which attributes those were, and only th...

Combine data in two columns into one and sort the combined column alphabetically..how??
I have two columns, A and B. Each column contains a list of data in text format e.g. 01TI518A.PV . It is possible that duplicates of this text may appear within either column. Also, the number of data values in each column is variable as they are imported from an external source into the worksheet. I would like to be able to combine the text from both columns into a single list in a single column, say column C. I would also like this column to be alphanumerically sorted. How do I go about doing this? I would do this. Copy the data from column A to column C (include a single header row) cop...