Creating query from other queries- dates column

I'm trying to create a query from other queries, all of which have a 'date' 
column.  However not all the queries have the same dates in e.g one query may 
only have entries for 01/01/2006 and then for 05/01/2006 but not for any 
dates inbetween these dates, although other queries may have.  But the new 
query seems to ignore the fact that other queries have data for these dates 
inbetween and only display data for the dates that appear in the first query. 
 I want ALL dates that have data entries to appear, even if some columns will 
have no entries (as some queries have no data for that date)- how do i stop 
this? Please help- deeply distressed!
0
Utf
7/19/2007 9:04:10 AM
access 16762 articles. 3 followers. Follow

6 Replies
798 Views

Similar Articles

[PageSpeed] 25

Two immediate questions:

1) Are you creating a union query
2) What is the table design?





"kim" wrote:

> I'm trying to create a query from other queries, all of which have a 'date' 
> column.  However not all the queries have the same dates in e.g one query may 
> only have entries for 01/01/2006 and then for 05/01/2006 but not for any 
> dates inbetween these dates, although other queries may have.  But the new 
> query seems to ignore the fact that other queries have data for these dates 
> inbetween and only display data for the dates that appear in the first query. 
>  I want ALL dates that have data entries to appear, even if some columns will 
> have no entries (as some queries have no data for that date)- how do i stop 
> this? Please help- deeply distressed!
0
Utf
7/19/2007 10:46:03 AM
It is difficult to understand what you are trying to do.  What is the 
real-world situation behind your efforts?  Are both queries based on the 
same table?  You can add several tables to a query, but they won't work 
together unless there is some sort of relationship between them (or if you 
create a union query, as scubadiver asked about).  However, since for a 
union query you need to write SQL directly (rather than creating it through 
design view), you would probably know if you had created one.
If the queries are both from the same table, you may need to change the join 
type to get the results you want.  In query design view, click the line that 
links the two queries, then click View > Join Properties.  The descriptions 
for the join types may guide you in the right direction.

"kim" <kim@discussions.microsoft.com> wrote in message 
news:D29CDAF7-7765-43E4-A207-F25FF732052A@microsoft.com...
> Firstly I'm not sure what a union query is- i am creating this 'linking'
> query to link 2 previous queries in order to create a report from the 
> final
> query.  In query 1 there are 4 fields i want to add- one of them being the
> 'date' field'.  From the second query there is one field I want to add. 
> For
> some dates, the field from the second query dosn't contain any data- 
> although
> i still want this date to appear in the query- apologies if this doesn't 
> make
> sense.  Do you understand what i mean?
>
> "scubadiver" wrote:
>
>> Two immediate questions:
>>
>> 1) Are you creating a union query
>> 2) What is the table design?
>>
>>
>>
>>
>>
>> "kim" wrote:
>>
>> > I'm trying to create a query from other queries, all of which have a 
>> > 'date'
>> > column.  However not all the queries have the same dates in e.g one 
>> > query may
>> > only have entries for 01/01/2006 and then for 05/01/2006 but not for 
>> > any
>> > dates inbetween these dates, although other queries may have.  But the 
>> > new
>> > query seems to ignore the fact that other queries have data for these 
>> > dates
>> > inbetween and only display data for the dates that appear in the first 
>> > query.
>> >  I want ALL dates that have data entries to appear, even if some 
>> > columns will
>> > have no entries (as some queries have no data for that date)- how do i 
>> > stop
>> > this? Please help- deeply distressed! 


0
BruceM
7/19/2007 11:58:57 AM
Ok- I've followed those instructions and when I go to open the new query I 
get the message 'syntax error in from clause' - this is is exactly what I 
typed in:

SELECT [Date], [Total Income]
From Income Daily Totals 2007 Query

UNION SELECT [Date], [Expr1]
From Figures 2007;

 - do you have any idea where I could be going wrong? thanks so much for 
your help so far!

"scubadiver" wrote:

> 
> I think I get what you mean. When you create the query do you show your two 
> queries and then drag a field from one query to the other to create a line? 
> 
> The problem with doing this is that only records with information in each 
> query will be shown especially if the link you are using is the date! Ah ha? 
> So only those rows with a common date will show. That is why rows with no 
> dates are not showing.
> 
> FYI:
> 
> A union query joins queries together with common fields so you have rows 
> from all queries in one list. That may solve it for you. The idea for the SQL 
> is the following.
> 
> SELECT [field1], [field2]
> From query1
> 
> UNION SELECT [field1], [field2]
> From query2;
> 
> What you need to do is open a blank query. Close the "show table" box, go to 
> the top left hand corner and select "SQL". Then you will get a white screen 
> in which to paste the code.
> 
> Let me know...
> 
> 
> "kim" wrote:
> 
> > Firstly I'm not sure what a union query is- i am creating this 'linking' 
> > query to link 2 previous queries in order to create a report from the final 
> > query.  In query 1 there are 4 fields i want to add- one of them being the 
> > 'date' field'.  From the second query there is one field I want to add.  For 
> > some dates, the field from the second query dosn't contain any data- although 
> > i still want this date to appear in the query- apologies if this doesn't make 
> > sense.  Do you understand what i mean? 
> > 
> > "scubadiver" wrote:
> > 
> > > Two immediate questions:
> > > 
> > > 1) Are you creating a union query
> > > 2) What is the table design?
> > > 
> > > 
> > > 
> > > 
> > > 
> > > "kim" wrote:
> > > 
> > > > I'm trying to create a query from other queries, all of which have a 'date' 
> > > > column.  However not all the queries have the same dates in e.g one query may 
> > > > only have entries for 01/01/2006 and then for 05/01/2006 but not for any 
> > > > dates inbetween these dates, although other queries may have.  But the new 
> > > > query seems to ignore the fact that other queries have data for these dates 
> > > > inbetween and only display data for the dates that appear in the first query. 
> > > >  I want ALL dates that have data entries to appear, even if some columns will 
> > > > have no entries (as some queries have no data for that date)- how do i stop 
> > > > this? Please help- deeply distressed!
0
Utf
7/19/2007 2:38:02 PM
....also, is it possible to create a union query based on another union query? 
thanks again 

"scubadiver" wrote:

> 
> I think I get what you mean. When you create the query do you show your two 
> queries and then drag a field from one query to the other to create a line? 
> 
> The problem with doing this is that only records with information in each 
> query will be shown especially if the link you are using is the date! Ah ha? 
> So only those rows with a common date will show. That is why rows with no 
> dates are not showing.
> 
> FYI:
> 
> A union query joins queries together with common fields so you have rows 
> from all queries in one list. That may solve it for you. The idea for the SQL 
> is the following.
> 
> SELECT [field1], [field2]
> From query1
> 
> UNION SELECT [field1], [field2]
> From query2;
> 
> What you need to do is open a blank query. Close the "show table" box, go to 
> the top left hand corner and select "SQL". Then you will get a white screen 
> in which to paste the code.
> 
> Let me know...
> 
> 
> "kim" wrote:
> 
> > Firstly I'm not sure what a union query is- i am creating this 'linking' 
> > query to link 2 previous queries in order to create a report from the final 
> > query.  In query 1 there are 4 fields i want to add- one of them being the 
> > 'date' field'.  From the second query there is one field I want to add.  For 
> > some dates, the field from the second query dosn't contain any data- although 
> > i still want this date to appear in the query- apologies if this doesn't make 
> > sense.  Do you understand what i mean? 
> > 
> > "scubadiver" wrote:
> > 
> > > Two immediate questions:
> > > 
> > > 1) Are you creating a union query
> > > 2) What is the table design?
> > > 
> > > 
> > > 
> > > 
> > > 
> > > "kim" wrote:
> > > 
> > > > I'm trying to create a query from other queries, all of which have a 'date' 
> > > > column.  However not all the queries have the same dates in e.g one query may 
> > > > only have entries for 01/01/2006 and then for 05/01/2006 but not for any 
> > > > dates inbetween these dates, although other queries may have.  But the new 
> > > > query seems to ignore the fact that other queries have data for these dates 
> > > > inbetween and only display data for the dates that appear in the first query. 
> > > >  I want ALL dates that have data entries to appear, even if some columns will 
> > > > have no entries (as some queries have no data for that date)- how do i stop 
> > > > this? Please help- deeply distressed!
0
Utf
7/19/2007 2:40:01 PM
Add square brackets round the query names.

SELECT [Date], [Total Income]
From [Income Daily Totals 2007 Query]

UNION SELECT [Date], [Expr1]
From [Figures 2007];

I assume you can create union queries from other union queries but I don't 
see why!




"kim" wrote:

> ...also, is it possible to create a union query based on another union query? 
> thanks again 
> 
> "scubadiver" wrote:
> 
> > 
> > I think I get what you mean. When you create the query do you show your two 
> > queries and then drag a field from one query to the other to create a line? 
> > 
> > The problem with doing this is that only records with information in each 
> > query will be shown especially if the link you are using is the date! Ah ha? 
> > So only those rows with a common date will show. That is why rows with no 
> > dates are not showing.
> > 
> > FYI:
> > 
> > A union query joins queries together with common fields so you have rows 
> > from all queries in one list. That may solve it for you. The idea for the SQL 
> > is the following.
> > 
> > SELECT [field1], [field2]
> > From query1
> > 
> > UNION SELECT [field1], [field2]
> > From query2;
> > 
> > What you need to do is open a blank query. Close the "show table" box, go to 
> > the top left hand corner and select "SQL". Then you will get a white screen 
> > in which to paste the code.
> > 
> > Let me know...
> > 
> > 
> > "kim" wrote:
> > 
> > > Firstly I'm not sure what a union query is- i am creating this 'linking' 
> > > query to link 2 previous queries in order to create a report from the final 
> > > query.  In query 1 there are 4 fields i want to add- one of them being the 
> > > 'date' field'.  From the second query there is one field I want to add.  For 
> > > some dates, the field from the second query dosn't contain any data- although 
> > > i still want this date to appear in the query- apologies if this doesn't make 
> > > sense.  Do you understand what i mean? 
> > > 
> > > "scubadiver" wrote:
> > > 
> > > > Two immediate questions:
> > > > 
> > > > 1) Are you creating a union query
> > > > 2) What is the table design?
> > > > 
> > > > 
> > > > 
> > > > 
> > > > 
> > > > "kim" wrote:
> > > > 
> > > > > I'm trying to create a query from other queries, all of which have a 'date' 
> > > > > column.  However not all the queries have the same dates in e.g one query may 
> > > > > only have entries for 01/01/2006 and then for 05/01/2006 but not for any 
> > > > > dates inbetween these dates, although other queries may have.  But the new 
> > > > > query seems to ignore the fact that other queries have data for these dates 
> > > > > inbetween and only display data for the dates that appear in the first query. 
> > > > >  I want ALL dates that have data entries to appear, even if some columns will 
> > > > > have no entries (as some queries have no data for that date)- how do i stop 
> > > > > this? Please help- deeply distressed!
0
Utf
7/19/2007 2:44:01 PM
On Thu, 19 Jul 2007 07:38:02 -0700, kim <kim@discussions.microsoft.com> wrote:

>Ok- I've followed those instructions and when I go to open the new query I 
>get the message 'syntax error in from clause' - this is is exactly what I 
>typed in:
>
>SELECT [Date], [Total Income]
>From Income Daily Totals 2007 Query
>
>UNION SELECT [Date], [Expr1]
>From Figures 2007;
>
> - do you have any idea where I could be going wrong? thanks so much for 
>your help so far!

Blanks are meaningful delimiters. Access sees "Income" and "Daily" and
"Totals" and "2007" and "Query" as separate things, and doesn't know what to
do with all of them!

If you have something - table, field, query - with blanks or special
characters in its name, you must enclose that name in square brackets so
Access knows to treat it as a unit:

SELECT [Date], [Total Income]
From [Income Daily Totals 2007 Query]

UNION SELECT [Date], [Expr1]
From [Figures 2007];

Also, if you want the result sorted, you can include an ORDER BY clause in the
last SELECT clause:

UNION SELECT [Date], [Expr1]
From [Figures 2007]
ORDER BY [Date];

Note that Date is a reserved word (for the Date() function which returns
today's date from your computer clock) and that it's best not to use it as a
fieldname.

             John W. Vinson [MVP]
0
John
7/19/2007 3:55:25 PM
Reply:

Similar Artilces:

How do I create a sample in Excel?
I am trying to create a sample, needing every 20th record out of around 3000 records. Mary, You need a VBA procedure. Sub AAA() Dim RowNdx As Long Dim DestRng As Range Set DestRng = Worksheets("Sheet2").Range("A1") For RowNdx = 1 To 3000 Step 20 Rows(RowNdx).Copy Destination:=DestRng Set DestRng = DestRng(2, 1) Next RowNdx End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mary" <Mary@discussions.microsoft.com> wrote in message news:ADE0AD7F-B459-41AE-8B2...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

100% stacked column chart shows wrong information
I wonder if someone can help me. I have created a table that tracks the profitability of a particular project and I’m using the table as a source for a 100% Stacked Column graph. The source data for the graph is in three rows: • B21:M21 - contain the names of the months from Nov – Oct • B22:M22 - money taken in • B24:M24 - profit B23:M23 contains the money paid to supplier each month. I need this in the table but I don’t need to show it in the graph. I just want to show profit as a proportion of what we bill to customers on that project. My problem is that the graph seems to show in...

Date display in Excel
Format column of cells as Date, display as mm/dd/yy. Date entered into cell, shows up correctly in the text entry field at the top of the screen, but the data on the worksheet displays as "33747", or similar number. Only happening on one workbook. Try tools|options|View tab|uncheck Formulas. Clark wrote: > > Format column of cells as Date, display as mm/dd/yy. Date > entered into cell, shows up correctly in the text entry > field at the top of the screen, but the data on the > worksheet displays as "33747", or similar number. Only > happening on one...

Can SUMPRODUCT be used for entire column?
This formula results in a numeric result: =SUMPRODUCT((A1:A20)*(B1:B20="b")) but this formula results in a #NUM! result: =SUMPRODUCT((A:A)*(B:B="b")) Which means I need to specify the length of the columns, which may grow over time. Any way to do this for the entire column, without having to specify the length of the column? XL07 removed the limitation on array formulas (which SUMPRODUCT is, even though it doesn't require CTRL-SHIFT-ENTER) and entire columns. For pre-XL07, one can use =SUMPRODUCT(A1:A65535, --(B1:B65536="b")) to get all but on...

Can't create Organizational Forms Library in Exchange 2003 with SP
Hello, I cannot create an Organizational Form in EFORMS REGISTRY folder (from First Administrative Group->Folders->Public Folders->EFORMS REGISTRY in ESM). When I right-click the EFORMS REGISTRY folder and select New, there is no Organization Form. Instead, I only see Public Folder in the popup menu. Do you have any idea why Organizational Form menu does not show? My Exchange Server is Exchange 2003 with SP2. The login user is Administrator. Could you please help me? Thank you very much. Yang Is that account member of "Enterprise Admins" group? Yang Zhang wrote: &...

Queries and Charts
Does anyone know why the expressions in queries work fine for reports but not charts? ...

Pass parameter from FORM to QUERY
I have an append query that I trying to call from a cmd btn...but it prompts me for the parameter(ie QuoteID)... How do I tye it into my call: Dim stQueName As String stQueName = "Quote Query" DoCmd.OpenQuery stQueName, acNormal, acEdit On Apr 13, 12:56 pm, jlt...@hotmail.com wrote: > I have an append query that I trying to call from a cmd btn...but it > prompts me for the parameter(ie QuoteID)... > How do I tye it into my call: > > Dim stQueName As String > stQueName = "Quote Query" > DoCmd.OpenQuery stQueName, acNormal, acEd...

C# Adding Days to a Date
Hello, I have 2 objects: objContract.activeon and objContract.expireson. I am trying to add 364 days to objContract.activeon and assign it to the value of objContractexpireson. ---------------------------------------------------------- // Contract Start Date DateTime dt = DateTime.Now; objContract.activeon = new CrmDateTime(); int iFound = 0; string sTemp = ""; if (objAccount.paymenttermscode.Value == 1) // due on receipt - use Todays Date { objContract.activeon = objInvoice.CFDinvoicedate; } else // ...

How do I create a click on + symbol to open a root and click on -.
I'm looking to create an excel file with drop down menus. I'd like to have a category. Click on the "+" symbol and the category opens up and shows all of the subcategories. Each category can further be opened if I so choose. Each category can be have a number total associated with it. When you click the "-" symbol. The subcategories close and the sum total of all subcategories is shown in the category total. example. creating a budget. Category is utilities sub categories are: phone, cable, electric, gas, etc... Monthly utility total ...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

Changing query execution sequence
Hi all, I got a spreadsheet which would execute a bunch of queries. It's noted that the queries are executing in the sequence of when it was added to the spreadsheet. Does anyone out there know of a way to switch the order without deleting and recreating them? Thanks! Wing ...

Sorting on Column headers
I have a spreedsheet that someone else created and I need to take the Column headers, which are in date form, and rearrange them in chronological order. Can this be done without cutting and pasting? I really need to save time with this project if possible. Thanks!:) --- Message posted from http://www.ExcelForum.com/ > I have a spreedsheet that someone else created and I need to take the > Column headers, which are in date form, and rearrange them in > chronological order. Can this be done without cutting and pasting? I > really need to save time with this project if possib...

how create Quota filter in WIndows 2003 R2 using Script
Hi all I need create quota filter in Server 2003 R2 using vbscript. quota filter should be applied to directories and not by users. I searched information about it on google without success. thank's in advance Daniel Hi Daniel. First you need open your FSRM (File Server Resource Manager), then you create a quota template, you must specify if your quota is "software" (just monitoring, but never deny the user) or "hardaware" (deny users when they use 100% of the quota), you must specify if you want send e-mail to user when this user use...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

creating a spredsheet and log the info into another spredsheet
hello, I have a excel sheet that is printed out and a cashier manual enters information, invoice number, invoice amount, cash amount, check amount, amex amount.... I would like to have the cashier input this information on her PC and print a copy to go along with the deposit and at the same time log the information into a google excel document I created. Any ideas how this can be done? ...

Creating Exchange 2003 organization
Is it possible to have two Exchange Organizations in the same domain? I need to reproduce a problem and was hoping to install an Exchange server to an existing domain however I need the organization to be different. Is this possible? When I installed EX 2003 I wasn't prompted for information other than location of files. Thanks in advance On Mon, 28 Feb 2005 08:09:03 -0800, "RP" <RP@discussions.microsoft.com> wrote: >Is it possible to have two Exchange Organizations in the same domain? I need >to reproduce a problem and was hoping to install an Exchange server...

Opening Multiple Web Links in a Column
Hi, I am very new to using web links in excel. A task I do very often is open a list of different websites that are in various columns in an excel spreadsheet. I am quite sure I am doing it the slowest way possible, so I need some help please. Basically I have been clicking on one link at a time. When I do this, the first website opens and excel automatically minimizes, then I have to go re maximize excel and then click the next web link and the same thing happens, etc... very time consuming. I am wondering if there is a way, either through Excel or whatever means necessary, to open all...

Customize columns in 'Marketing List Members'
I can't figure out where one can customize the columns used within the "marketing list" entitry when you click the 'marketing list members' on the left side to show the members. I want to add some columns, like Email. Screenshot: http://i355.photobucket.com/albums/r469/canadaka_bucket/marketing_list_members.jpg Just read the Posting on the Microsoft CRM Team Blog. <canadaka@gmail.com> schrieb im Newsbeitrag news:306584c6-2043-4962-b12a-d0b9287684bb@b31g2000prb.googlegroups.com... > I can't figure out where one can customize the columns used within the >...

date function #3
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C73005.0FA093A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a cell A1 with date 5/2/07. If in cell A2 I do month(A1), I get = 5. Is there a way to return May instead? either through a command or = formatting? I can reference cell A2 with the 5 with a if(cell =3D 5, = "May", ) in cell A3, but the date in cell A1 varies from Jan to Dec and = the 12 embedded if's in cell A3 are too long (that is the error I get = when trying to do so), plus i...

multiple iterations of same query
Running Access 2003 on Vista. I have a query that chooses 15 records at random. This works fine. What I want to do is run the same query for approx 110 different users, each with their own randomly selected 15 records. Obviously, I want to avoid manually running the same query that many times. How can I get around this? Post your query SQL. -- Build a little, test a little. "Nathan" wrote: > Running Access 2003 on Vista. > > I have a query that chooses 15 records at random. This works fine. > > What I want to do is run the same query fo...

Character Length in a column
I have a column where every cell must have 14 characters. Lets say have the name Angel. I need 9 more charcters. Next cell down have the word cake. I need 10 more charcters.How do I automatciall insert the spaces I need to have 14 charcters. HELP PLEASE!!!!! GOIN NUTS ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements One way: Do you care about what characters are added? I'l...

Freeze the side column/top row & scroll others
what is the function to set (lock in or freeze) the first column and / or top row of a spreadsheet, so the words and numbers remain in the same place as you scroll the other columns and rows. (so you can add more columns..yet keep the main information in the first column/row) Freeze Panes..... In older versions of Excel, it is under Window. In 2007 version of Excel, it is under View. You first select a cell, then activate the command. Excel uses the selected cell's upper left corner to define the freeze point. Play with it. You can also Unfreeze panes that were fro...

Can't create the item #2
I use Outlook from MS Office 2003 Business Edition. Everything has worked fine until the past few days. Now, whenever I click on an E-mail address link inside a webpage, I get a message from Outlook that says "Can't create the item." One person on this group gave me the following advice: >Close Outlook, find and rename the frmscache.dat to .old I did searches on all my drives and the file named above was not found. I doubled checked the search to confirm that it included hidden files and system folders in the search. Still no luck. More thoughts? ...

creating template for multiple emails
How or can one create an email template (same subject line and message)for multiple emails (approx 100's)? Needing to shorten the time it takes sending 100's of individual emails (with the same subject line and same message) to sales staff on a weekly (some times daily) basis. Company does not want sales force using other sales persons email addresses (they are contractors). Try using a Distriburtion List, send one message, it goes to all of them, or get the message ready, put your address in the TO: field, and put everyone else in the BCC: field?? That way no one can see wher...