Query Using Variable Table Sources

I've got a reasonably simple application that I want to dumb down further for
a novice user.  We recieve quarterly data in three Excel files of which we
combine into a single tab and inport and keep as a separate table, "just in
case" we want to look at history.

Currently we name the tables "FYnnQn" and then the former user created
separate reports from each quarterly table.  

I'd like to have a query ask for user input of "FYnnQn" and then report off
of that, instead of growing the number of reports and forcing the new user to
copy and edit reports.

Each quarter's data has the same fields, but does not have a period marker to
distinguish it from another period, except for the title of the table as we
currently are working.  If there were an easy to append a single table and
automagically place a period marker, then the query/report would be easy.

So I'm looking some way to do either
1) a way to query variable table names 
or
2) automagically adding the quarter period marker to the records during the
inport/append table process.

Or if there is an even smarter way, I'd welcome that input.


Thanks in advance.

Mike

0
McCarthy_MF
8/21/2007 4:52:28 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
921 Views

Similar Articles

[PageSpeed] 1

-- 
Dave Hargis, Microsoft Access MVP


"McCarthy_MF" wrote:

> I've got a reasonably simple application that I want to dumb down further for
> a novice user.  We recieve quarterly data in three Excel files of which we
> combine into a single tab and inport and keep as a separate table, "just in
> case" we want to look at history.
> 
> Currently we name the tables "FYnnQn" and then the former user created
> separate reports from each quarterly table.  
> 
> I'd like to have a query ask for user input of "FYnnQn" and then report off
> of that, instead of growing the number of reports and forcing the new user to
> copy and edit reports.
> 
> Each quarter's data has the same fields, but does not have a period marker to
> distinguish it from another period, except for the title of the table as we
> currently are working.  If there were an easy to append a single table and
> automagically place a period marker, then the query/report would be easy.
> 
> So I'm looking some way to do either
> 1) a way to query variable table names 
> or
> 2) automagically adding the quarter period marker to the records during the
> inport/append table process.
> 
> Or if there is an even smarter way, I'd welcome that input.
> 
> 
> Thanks in advance.
> 
> Mike
> 
> 
0
Utf
8/21/2007 9:54:01 PM
Oppps , hit the post button by mistake on the previous post, sorry.

One table with period identifier is definitly the correct way to do it.

You can create a form that allows the user to enter the period information 
in a text box.  Then add a calcuated field to your query that pulls that 
value from the form and populates a field in your table:

Quarter: [Forms]![SomeFormName]![txtQuarter]

Or, if you are not using any forms and just need a way for the user to enter 
it, do it like this:

Quarter: [Enter Quarter]

Then when you run the query, it will prompt for the value.
-- 
Dave Hargis, Microsoft Access MVP


"McCarthy_MF" wrote:

> I've got a reasonably simple application that I want to dumb down further for
> a novice user.  We recieve quarterly data in three Excel files of which we
> combine into a single tab and inport and keep as a separate table, "just in
> case" we want to look at history.
> 
> Currently we name the tables "FYnnQn" and then the former user created
> separate reports from each quarterly table.  
> 
> I'd like to have a query ask for user input of "FYnnQn" and then report off
> of that, instead of growing the number of reports and forcing the new user to
> copy and edit reports.
> 
> Each quarter's data has the same fields, but does not have a period marker to
> distinguish it from another period, except for the title of the table as we
> currently are working.  If there were an easy to append a single table and
> automagically place a period marker, then the query/report would be easy.
> 
> So I'm looking some way to do either
> 1) a way to query variable table names 
> or
> 2) automagically adding the quarter period marker to the records during the
> inport/append table process.
> 
> Or if there is an even smarter way, I'd welcome that input.
> 
> 
> Thanks in advance.
> 
> Mike
> 
> 
0
Utf
8/21/2007 9:58:02 PM
Thanks for your help.  Buliding a single table is best, but it sure is messy
since they were inconsistant in the begining.

Mike

Klatuu wrote:
>Oppps , hit the post button by mistake on the previous post, sorry.
>
>One table with period identifier is definitly the correct way to do it.
>
>You can create a form that allows the user to enter the period information 
>in a text box.  Then add a calcuated field to your query that pulls that 
>value from the form and populates a field in your table:
>
>Quarter: [Forms]![SomeFormName]![txtQuarter]
>
>Or, if you are not using any forms and just need a way for the user to enter 
>it, do it like this:
>
>Quarter: [Enter Quarter]
>
>Then when you run the query, it will prompt for the value.
>> I've got a reasonably simple application that I want to dumb down further for
>> a novice user.  We recieve quarterly data in three Excel files of which we
>[quoted text clipped - 24 lines]
>> 
>> Mike

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1

0
McCarthy_MF
8/22/2007 5:25:39 PM
Reply:

Similar Artilces:

Text in pivot table
I'm aware that summary functions will not work for text data in Excel 2000 (eg something like 'first' in Access crosstabs doesn't exist). Two questions: - Is this functionality available in later versions? - What are the alternatives (eg anyone got some VBA they've used to do it)? Thanks GB Pivottables are still quantitative summaries. You may want to give more info about what your data looks like and what you want. I might lead to better alternatives. green biro wrote: > > I'm aware that summary functions will not work for text data in Excel 2000 > (...

Save operation on table MOP_Order_MSTR after upgrading to V10
I am getting the above error in V10 (after upgrading from 9.00.0314) I click OK on the message, and it seems like I can keep going, so it doesnt stop me from continuing. I am assuming it is a security issue with the MFG series and sql but do not know how to correct it. thanks! -- Doug Under the More information buttons: [Microsoft][SQL Native Client][SQL Server] Subquery returned more than 1 value. This is not permitted when the subquery follows =,!=,<,<=,>,>= or when the subquery is used as an expression. -- Doug "Doug" wrote: > I am getting the above ...

pivot table sort entries that don't yet appear in table
I have a pivot table that's accessing an ODBC data source. There's a field for career level in the table that's currently sorted alphabetically. I'd like to sort it in order of career level, instead. I'd need to do this manually, because there is no simple alphabetic progression i can use. The problem is, the table must be filtered on a single location (a page filter) because there's so much data and while filtered not all career levels appear. One location may show 2 career levels, another country may show 3, another may show only 1. How can i set this up to c...

"Sum of" in column heading in pivot table
When I move a column titled January into a column area of a pivottable the title says "sum of January". I cannot change it to "January" without an error "pivottable field name already exists". Yet if apply an autoformat to it the lable says "January". How can I format manually so the label does not includ "sum of" Include a space at the end, or a period, i.e. January. On Tue, 19 Oct 2004 06:25:02 -0700, Gary Rowe <GaryRowe@discussions.microsoft.com> wrote: >When I move a column titled January into a column area of a pivottabl...

Cannot use Accounts Funtion In Outlook 2000
I'm running XP Home and Outlook 2000 updated. Suddenly I can't access Accounts under Tool, or the Mail function in Control Panel or the Account funtion under Option. Click and nothing happens. This is awkward because something (?) has disrupted my main email account. Help. Thanks, Jim- Jim, I had a similar problem with Windows 2000 / Outlook 2002. Tools > E-mail Accounts doesn't function, just it happens nothing. With a help from a friend, I got the solution. I hope it helps you as well, though the environment is not really the same. Try it! http://support.microsoft.com/...

subform data needs to be saved in main form's table
I have 2 tables. One table will store student names temporalily and the other will store several hundred company names - that are not temporary. I have an unbound combo box in my main form which queries the company table. When a company is selected, it's details appear in the subform. So, if I have a company selected, and several student names typed in the main form, it all looks perfect. Now, I want to save all of the information in the main table (the student table) so I can do mail merge in MS Word...How would I get that sub form information from the company table to save in...

Mapping dataset tables to XML output
Hi, I'm really stuck with this one. I have a dataset with two tables. One table is company data, and the other is contacts. I populate these by using a SP in SQL Server which returns the two tables in one call. I'm using Microsoft EnterpriseLibrary for Data to get the data from SP. It works fine and I get two tables in the dataset, "Table1" and "Table2". I then have an XML-file, which will serve as a template to generate the XML-schema, which I load into the dataset with the InferXmlSchema method. This gives me a perfectly good looking XML-Schema of how I w...

How do I make an update query to update fields in one table with fields from another table?
Hi All I have two tables: my main table and a cut-down version of the main table with newly updated values: Main ------- RefNo Document_Type Version Title ModMain ------------- RefNo Document_Type_UPDATED Version_UPDATED Title_UPDATED I cant work how to use Access's query builder to do the simple task of updating the fields in "Main" with the modified values in "ModMain" where the RefNos match. Can anyone give me a pointer or web link? I cant find any information in Access Help about performing update queries with more than one table. This seems a straight forward ...

Can't move or delete Word file in Win Explorer: file in use
I have Win XP Pro SP3. When using Win Explorer, I sometimes cannot move or delete a file. I get the message that the file is being used by another person or program. Here's how to reproduce the problem: - Open a file in Word. - Make a change. Save and close the file. - Try to move or delete the file in Win Explorer. Sometimes I can release the file simply by closing Word. At other times, I need to reboot. Any advice? You will not be able to move or delete a Word document until its lock file has been released. All lock files should be released when Word is closed (o...

getting the name of the query?
Hello, I have a created field in a query using, invoice_number: "invoice_reminder_" & Format(Date(),"mmm-yy") & "_" & [DCount] & DCount("*","[renewal-end-jan-08]","[ID] <=" & [ID]) and would like to generalise the query. Is it possible to reference the name of the query (renewal-end-jan-08 in this case)? Cheers Geoff No, it's not, unless you're generating the SQL for the query dynamically using VBA. However, having queries named "renewal-end-jan-08" doesn't sound like a good idea t...

Populating Excel Web Query across workbook
Hi, I have a workbook with around 350 sheets. I have an Web Query which I wish to populate in cell A2 of all of those sheets? The web query is exactly the same for each sheet, just picking up a different input from cell A1 in each sheet. How can I duplicate the web queries automatically? Thanks, Bertie. -- claytorm ------------------------------------------------------------------------ claytorm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11610 View this thread: http://www.excelforum.com/showthread.php?threadid=479279 ...

Changing Values from a form based on a union query
I have created four separate input forms for sales reps to enter information for an admin person. I want to keep the forms (and the tables associated with them separate). Each of these individual forms is based on a query such as LetterRequestJoeQuery which is then based on the table LetterRequestJoe. I then created a union query that combines all four individual queries and then a form based on this union query. The idea was to give an admin person a view of all entries and to be able to edit them. But I have discovered that you cannot change the information on a form based on a uni...

Business Portal should let me to change the query layout in Links
Entering this on behalf of a customer. (see CR 2760 - Mbs Great Plains SE) Can we modify the results displayed when you click on a Links so it's always the default for everyone? Example. Go to Sales>Queries. Select Customer under Center queries Now click on Addresses under Links Would like to change the reult viewer to display different column name. So I changed the column names and removed "Address." When I save the query it goes to My Folder....I would like this modifed query to be the default when someone click on Address under links (for customer data entity) J...

ACT! migration using CDF
So has anyone succesfully migrated ACT! records (including NOTES) over to MS CRM without using SCRIBE? Just the CDF - if so how did you get the records out of ACT! into the CDF database? Could anyone share... Thanks. Nick Hi Nick... I did just that - and not just 1 ACT! database - but I merged 13!! What I did is use a product called ExportPro - it was about $300? and worth EVERY PENNY! It exported things from act to an Access database. I was then able to clean up the data - and get it ready to go using the migration worksheets (tells you what datatypes, etc) I then uploaded this data ...

How do I make a tessellation using Microsoft Digital Imaging?
I would like to use photographs to make a tessellation. How do I do using Microsoft Publishing or Digital Imaging? Maybe with Digital Imaging but you'd be better off posting in that newsgroup. Publisher is not a photo editing program. -- JoAnn Paules MVP Microsoft [Publisher] "skg47" <skg47@discussions.microsoft.com> wrote in message news:1FE02B85-B219-4B18-80C8-AA47DA228566@microsoft.com... >I would like to use photographs to make a tessellation. How do I do using > Microsoft Publishing or Digital Imaging? "JoAnn Paules [MVP]" <jl_paule...

Purging using Outlook 2003
I'm using Outlook 2003 and just changed to start using IMAP. I had a few questions on purging. 1. When I purge, it seems I can only purge one folder at a time. There is no option to purge multiple folders at once. Is that correct? 2. There is no auto purge such as purge all items when I close Outlook 2003. Correct? 3. Is there an option to move a deleted email to the Trash folder rather than just keeping it in the same folder and having it struck out? 4. If there is no option to move a deleted email to the Trash folder, is there a filter / setting / grouping, etc. that allows you to hi...

using dates pre-1900?
I need to have Excel and Access recognise dates between 1800 and 1910, in order to perform time lapse calculations and to sort entries in date order. Is there a way to force these programmes to recognise such dates? http://j-walk.com/ss/excel/files/xdate.htm -- Kind Regards, Niek Otten Microsoft MVP - Excel "Historysue" <Historysue@discussions.microsoft.com> wrote in message news:0200C317-673F-45A3-A896-8DA97E1718FB@microsoft.com... >I need to have Excel and Access recognise dates between 1800 and 1910, in > order to perform time lapse calculations and to sort e...

Importing across Excel files, and using MSQuery?
I have one master file, which I first update every month with who' showed up (column A on sample roster file) & add new names, from whic I then filter, copy, and paste data from about 1600 kids into monthl sign in sheets for 24 different classrooms. It is quite tedious and ca take an 8 hour day for the whole thing. I am wondering why I can' import the updated data from one file (roster) directly to the othe (sign in sheets). When I tried to create a query, the column with th X's and single numerals did not show up as valid options to import fro and I don't know why -- Mess...

chart options show data table
Currently using 2 workbooks to produce 1 chart - the source data in workbook 1 and the chart in workbook 2. We also are using the "show data table" option to most easily view all values in the chart. We would like this data to keep it's currency format to best reflect the values in the chart; however, the data table loses this formatting when we close workbook 1. Is there any way to hang onto this formatting after the workbook is closed? Data tables in charts are not particularly flexible or capable. You combine the workbooks. Alternatively, put a table in the chart's...

Variables in Message Notifications?
All, I have set up some rules for Outlook that give me specific message notifications when messages come in from particular users. I would like to include the subject of the incoming message in the notification. Is such a thing possible? The only way that I can imagine it is through a variable like $subject or some such, but I figured someone here might know. Thanks much, Todd ...

Inserting XML Data Into a New Table FROM VB.NET
Can anyone help me figure out how to insert data into SQL Server when a table to hold the data does not yet exist. I am able to insert data into a existing table in the following fashion. But what if the table does not exist? Appreciate any help I can get. Private Sub InsertData() Dim objSQLConn As New SqlConnection("SERVER=.;UID=sa;PWD=;DATABASE=MyTest;") Dim objAdapter As SqlDataAdapter Dim objDataRow As DataRow 'Dataset row Dim objDBRow As DataRow 'SQL Server table row Dim objDSXML As New DataSet Dim objDSDBTable As...

Refreshing pivot table
I have a data base on one XP Pro pc connected to a network of XP Pro and Win 98 SE pcs. All other pcs are linked to the tables on this pc. one of the forms have a Excel pivot table. When I try to refresh the pivot table I get a error message saying Problems obtaining data. We use Office 2000 Any ideas Can you refresh the pivot table on the computer that contains the database? What is the source of the pivot table? If it's a query, try running the query and check the results. Maybe it's not working correctly, so there's no data to feed to the pivot table. Benn Voslo0 wrote...

How to create a table which calculates the angles in a Pie Chart?
I have a tally chart, a bar chart and a pie chart. How do I construct a table which will calculate the angles in the pie chart ? Hi Lynne, Values of Slices 3 5 6 4 9 === 27 (total) =3/27*360 and so on. The value of a slice divided by the total multiplied by 360. -- Cheers, Shane Devenshire "Lynne" wrote: > I have a tally chart, a bar chart and a pie chart. How do I construct a > table which will calculate the angles in the pie chart ? Thanks, yes I realised a little later that all I need do was show the percentages and do the mathematics. Your help is appreciated...

NOT expression in a Query
I have a database that I need to query. In one column I have the field name ROUTE and the data entries are in colours [Red, Yellow, Orange etc] I want to query the database and exclude the Red and the Orange routes. I have tried typing in: Not "Red" Not "Orange" However this does not seem to exclude the colurs in the query. Where am I going wrong? Please! -- Try: Not IN ("Red", "Orange") Note that this excludes nulls also. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrow...

Lookup table form data ERROR
Ok, here's a new bug I can't find anywhere... It's MSPS2007 SP2 and a new install. When accessing the Lookup tables from PWA, the screen loads the boarder and trim as normal, but the grid for the actual values fails to load. The lower left of the window indicates 'Done', but with errors, clicking on the error for the details indicates something like "problems with this page might have prevented it from displaying properly..." the errors listed in the details are Line: 1330 Char: 8 Error: 'ExcelBtn' in undefined Code: 0 I have seen a vast nu...