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?
> Two immediate questions:
>
[quoted text clipped - 10 lines]
> > have no entries (as some queries have no data for that date)- how do i stop
> > this? Please help- deeply distressed!
BruceM - 19 Jul 2007 12:58 GMT
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.
> 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
[quoted text clipped - 30 lines]
>> > stop
>> > this? Please help- deeply distressed!
scubadiver - 19 Jul 2007 13:04 GMT
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...
> 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
[quoted text clipped - 18 lines]
> > > have no entries (as some queries have no data for that date)- how do i stop
> > > this? Please help- deeply distressed!
kim - 19 Jul 2007 15:38 GMT
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!
> 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?
[quoted text clipped - 44 lines]
> > > > have no entries (as some queries have no data for that date)- how do i stop
> > > > this? Please help- deeply distressed!
John W. Vinson - 19 Jul 2007 16:55 GMT
>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
[quoted text clipped - 8 lines]
> - 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]
kim - 19 Jul 2007 15:40 GMT
...also, is it possible to create a union query based on another union query?
thanks again
> 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?
[quoted text clipped - 44 lines]
> > > > have no entries (as some queries have no data for that date)- how do i stop
> > > > this? Please help- deeply distressed!
scubadiver - 19 Jul 2007 15:44 GMT
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!
> ...also, is it possible to create a union query based on another union query?
> thanks again
[quoted text clipped - 47 lines]
> > > > > have no entries (as some queries have no data for that date)- how do i stop
> > > > > this? Please help- deeply distressed!