Creating query from other queries- dates column

[Guest]

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!

 

[Guest]

Two immediate questions:

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

 

[Guest]

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?

 

[BruceM]

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.

 

[Guest]

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...

 

[Guest]

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?

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
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?

 

[Guest]

....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?

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
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?

 

[Guest]

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

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
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:

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





:

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!

 

[John W. Vinson]

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]