query with inline dummy table - 13338

Is there a way in Access to create a query that contains the table
records within the query itself?  I want to avoid creating a dummy
table and just use values within the query definition.  I was thinking
about using syntax similar to the insert into statement I would use to
populate the dummy table, but I'm not sure if I have a syntax problem
or I'm trying to solve an impossible problem.

The query I'm thinking of might look something like this:

select *
from values("test");

-or-

select *
from (
("1/1/07","2/1/07","3/1/07","4/1/07","5/1/07","6/1/07")
);

0
jasontferrell
7/18/2007 5:06:05 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1826 Views

Similar Articles

[PageSpeed] 4

On Wed, 18 Jul 2007 10:06:05 -0700, jasontferrell <jasontferrell@gmail.com>
wrote:

>Is there a way in Access to create a query that contains the table
>records within the query itself? 

Not that I've ever heard of. What are you trying to accomplish with this?

             John W. Vinson [MVP]
0
John
7/18/2007 9:36:23 PM
Reply:

Similar Artilces:

Pivot Table with Define name
Hello: Is it possible to use define name in Pivot Table that the define name is link to another woeksheet? Thanks! You can use a named range in another workbook, if that workbook is open. Or, in step 1 of the pivot table wizard, select external data source, and connect to the other workbook. Alan Tang wrote: > Hello: > > Is it possible to use define name in Pivot Table that the define name is > link to another woeksheet? > > Thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Multiple Table Join Issue
In a payroll system there are 4 tables One to many relationship Check Summary (cs) - Master Hours & Earnings (h&e) Taxes (t) Deductions (d) Query = Select cs.lastname, cs. firstname, cs.middlename, cs.clockno, cs.paydate, cs.payperiod, he.paycodedescript, he.paycode, he.currentearnings, t.taxdescript, t.currenttax, d.deductdescript, d.currentdeduct right join cs on cs.csid = he.linktocsid right join t.linktocsid = cs.csid right join d.linkto csid = cs.csid where cs.paydate >=(start of date range) and cs.paydate <=(end of date range) The result looks like this regpay...

How do I create a one variable data table?
Look in Excel's help files for 'one variable data table'. And there's information in the following MSKB article: XL: How to Create and Use One-Input Data Tables in Microsoft Excel http://support.microsoft.com/kb/282855 Prelll91 wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Case insensitive XPath query?
Hi, I'm using MS SQL Server 2005. I have this clause in a query that looks at an XML column ... (aaa.category_xml.exist('/categories/info_type/category/subcategory/ @name[fn:contains(.,'Mountain Biking')]') = 1) Does anyone know a way to make the "fn:contains" clause do a case insensitive search? Right now the search is based on case but I would like to write one in which case doesn't matter. Thanks for your feedback, - Dave laredotornado (laredotornado@zipmail.com) writes: > I'm using MS SQL Server 2005. I have this clause in...

pivot tables formatting
I have a sales pivot with the months in columns and products by category in the rows. I simply want to have "normal" lines, which will make for easy reading like any other report at print time. Also I would like the product headers left aligned down in their column Whatever formatting I apply in whatever way I can select those items, my formatting is undone on a refresh, though the auto-format option is off in the table setup. It does keep seetings like the font and bold, etc, but lines and alignment instructions are rudely overwritten. Anybody with an answer to that one? I w...

Total in Pivot Table
Dear all, In my pivot table, for example, I have 20 customers with their sales amount. Then I made a sort by sales amount and for presentation I put in pivot table to show the 10 bigger customers. But my problem is that the total only sum the 10 customer selected and I would like that the total represents the sum of all customers. I have been tried to click in pivot table - options and flag the field - selected subtotal in hidden itens, but it is not working. Could you help me with this matter??? Thanks in advance!!! Andr=E9 ...

Hiding rows containing zeroes or blanks in pivot tables?
How do I hide rows containing zeroes or blanks in pivot tables? You can use programming to hide the rows with a zero total. For example: '====================================== Sub HidePivotZeroRows() 'hide worksheet rows that contain all zeros Dim rng As Range For Each rng In ActiveSheet _ .PivotTables(1).DataBodyRange.Rows If Application.Sum(rng) = 0 Then rng.EntireRow.Hidden = True Else 'unhide any previously hidden rows rng.EntireRow.Hidden = False End If Next rng End Sub '================================ Sub UnhidePivotRows() 'unhide all...

Form for inputing criteria for a report's query
I have a report that is based on a query. The criteria for the start date changes and I want a form where I can input the date range (Between #2/2/2006# and #2/4/2006#) each time the report is run. Is there a way to set up the form so that the forms asks for the user to put in the start date and the end date and then put those results in the format above into my query to run the report correctly? Thanks, Robert ryonker wrote: >I have a report that is based on a query. The criteria for the start date >changes and I want a form where I can input the date range (Between >#2/2/2...

pivot table question #2
I have two columns of data: Date Value 1/1/05 9 1/1/05 10 1/1/05 12 .... .... 1/9/05 16 1/9/05 4 There may be a variable number of dates, and does not contain a complete list of consecutive dates (i.e. some dates have no associated data and are not in the list) I want to produce a calculation the that subtracts the MIN for each date from the MAX of each date. I do not necessarily have to display the max and min values - I am really after the result Date (max - min) 1/1/05 3 <12-9=3> 1/9/05 12 <16-4> What is the best way to do this? I have experimen...

Storing Query Into Table
I have been trying to find some information about storing query sql strings in a table and calling upon that string to run query's dependant on user selections in a form. I have a split database and I want the users of the front end to create queries that everyone can use. Can anyone point me in the right direction on where to start. Thanks Craig There is a complete query by form applet at http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This allows users to select field, enter criteria, sort, group, and run. The results are displayed in a datasheet subform...

How to automatically populate a table with query results
How do I do the following? My database is: Table Title - Main Field in Main Table [Pricingdata] - Numeric - contains Pricing data Query - Contains [Pricingdata] from Main Table Contains Expression Field: ([New Pricing Data]= [Pricingdata] + ..10) Desired Result: I want to automatically overwrite all data in the Main table [Pricingdata] field with results of the query Expression Field. Any assistance is deeply appreciated. On Sat, 24 Mar 2007 09:31:56 -0500, "Frank" <fhsmith7@bellsouth.net> wrote: >I want to automatically overwrite ...

combo box, how to limit to client table
I have the following tables: CLIENT, EMPLOYEE, OFFICE LOCATION. I have a one to many relationship with the client> employee and client>office location. Now i can associate all the clients employees as well as the clients locations. On my employee form i would like to have a drop down box that shows all the clients office locations. so i can specify which office the employee is located. think i have to run a query, then limit the list to the client ID???? am i on the right track??? help thaks. bl -- brandon Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Fo...

what does "data source reference is not valid' in Pivot table
Can someone tell me how to resolve 'data source reference is not valid' error while creating a pivot table Hi, I Googled your question and found 21,000,000 + answers. Could luck ! -- Pecoflyer Cheers - 'Firefox 3.5' (http://www.mozilla.com/en-US/firefox/all-beta.html) really IS fast ! ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=126977 ...

Opening a form by clicking on a record in a table
Is it possible to click on a field of a specific record of a table in microsoft access and open a form containing information about that record. If so how is it done? Thanks -- BULLDOG253 BULLDOG253 wrote: > Is it possible to click on a field of a specific record of a table in > microsoft access and open a form containing information about that > record. If so how is it done? > > Thanks It's possible to open a form from another form, but not from a table. Tables should never be used for anything after intitial development. -- Rick Brandt, Microsoft Access MVP Emai...

Pivot Table Help...
Hello, i am having a little problem creating a Pivot table for my poker league. Basically, there is a 15 week season, with different players playing each week (well, new people can join anytime). You accumulate points on how well you finish each with. I have an excel workbook with keeps track of the standings for each week, and points for each person for the week. I can't figure out though how to create a table to compute the total points for each person. anyone want want to take a look and help me out. My email is amsrock02@hotmail.com. Any other ways that would work? -- nevaar ...

Display Data In Form From A Query
I have a form with the Control Source as "=Query!QTYForPPMProduction!SumOfQTY_PRODUCED" and this query runs prior to the form opening. The query runs fine, and the proper data displays in the datasheet view, but my form Text Box will not show the data. Any suggestions out there? =DLookup("[SumOfQTY_PRODUCED]", "[QTYForPPMProduction]") -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "GeorgeSoares" <GeorgeSoares@discussions.microsoft.com> wrote in message news:6F2E1E06-8772-4E6F-B1A7-647E14A1A7F0@microsoft.c...

Customer Internet Information Table
Anyone Know where the Customer Internet Information table can be found? I have searched through the Resources in GP but can't seem to find any documentation on this table. How you access the form is open up a Customer Card and click the Italized I Button next to the Address ID Field It's SY01200, (Company table). The field "Master_Type" tells you which series it comes from. In your case I think the value for this field would be "CUS". "Devin" wrote: > Anyone Know where the Customer Internet Information table can be found? I > have search...

Edit Query
I am new to macros and am trying to write one which enables me to access MSQuery file for editing from my worksheet. The worksheet has to be updated each month so I only need to edit this criteria of the query. Any advice would be greatly appreciated You have 3 different methods you can use 1) Manually create a query using the Excel menus. This type of query is fix (can't change parameters) except using the Query editor from the worksheet menues. Allows only reading of a database or excel file. 2) Record a macro while performing a query (use as a template). Then mo...

Query where 3 Fields must contain same value
I would like to write a query that pulls records where the value in 3 different Columns is the same. Example: Return records where each field below contains same value Return below records Field1 | Field2 | Field3 | 150 150 150 Do not return rows like below Field1 | Field2 | Field3 | 175 26 15 -- Brian Conner Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200911/1 WHERE Col1 = Col2 AND Col2 = C...

PIVOT TABLE HELP #4
i have a spreadsheet with columns ( Vender, Month, Amount ) i'm trying to set up my table as follows: Column Fields are MONTH, Row Fields are Vender, and Data Items are AMOUNT. But when i put the AMOUNT in it changes to something like 1,2,3,4,5. How do i keep my AMOUNT as currency Please see reply to your post in excel.misc Please do not multipost. -- Regards Roger Govier "Michele" <Michele@discussions.microsoft.com> wrote in message news:18B610E1-1CB3-4E24-9B85-13F28F20246D@microsoft.com... >i have a spreadsheet with columns ( Vender, Month, Amount ) i'm...

pivot table
Hi - have just switched to XP In a pivot in old excel I use to to click in a subtotal field eg "Europe Total" and excel would automatically highlight all subtotals at that level. It just meant I could colour the row and it would apply to all of the subtotals - how can I do that it XP ? thanks To enable selection -- From the Pivot toolbar, choose PivotTable>Select Click on Enable Selection To format the subtotals -- Move the pointer to the left of a subtotal heading in the pivot table. When the black arrow appears (like the one that appears when the pointer is over a r...

Mac Excel 08 puts "#VALUE!" in all pivot table references when opening an WIN Excel 97 file.
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I recently bought a MacBook Pro 15.4, 3.06GHz, 8GB RAM running Mac Excel 2008 (v12.1.0 080409). When I use Mac Excel 2008 to open excel files that I created in Windows Excel 97 2004, all my pivot tables convert correctly but any REFERENCES to any fields within the pivot table do NOT show the valid values that Excel 97 showed. Instead all refrences have &quot;#VALUE!&quot; in the cell. There is a valid reference startement there but no valid value. How can I fix this? You are a few updates behind: make ...

Display/Pull images from web onto form/into table?
Hello: I am experimenting with using Amazon Web Services to pull information into an Access database. I want to be able to use the cover art images Amazon makes available. At this point, I have gotten as far as being able to acquire the URL that points to the image. I thought this would be easy but I am struggling: 1. Is there a way, on a form, to set an image control to display the image using a URL as a pointer? Is the web browser control the only way to do this? 2. Is it possible / really hard to do / easy to somehow actually download the image into the table using VBA? Thanks! F...

BOM Rolled Cost Report or Query
In GP 7.5 (or later versions?) is there a way to view the rolled up cost of a BOM assembly at current costs of the items? Can I view the assembled cost using the standard cost of the part items as well? To put this anouther way, I want to update the standard cost on the item master record for all items with BOMs with what it would cost today to assemble the BOM. A report(GP or Crystal) or SQL query/view, etc would all work fine. Thanks, Gregg Short MIS Cropking Inc short@cropking.com You could modify Reports-Inventory-Setup-Indented Bill of Materials to calculate a component Extende...

Query works-Export fails "...you and another user are attempting to change the same data..."
Can't figure this out. I am the only user in the database. The file (RealProperty) can be queried using a select query that includes just the fields in the table. I get the above error, however, if I try to export the resulting table/query in any of the following fashions: mdb (in a new db designed to store this one table) ..xls (get the same error) ..csv (get the same error) If it simply try to copy the results, I get the same error. I have checked: there are no dependencies that are missing; there was an export errors table, but the three records that had data issues have been fixed....