Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 2 / July 2007

Tip: Looking for answers? Try searching our database.

Creating query from other queries- dates column

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
kim - 19 Jul 2007 10:04 GMT
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!
scubadiver - 19 Jul 2007 11:46 GMT
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
[quoted text clipped - 5 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 11:58 GMT
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!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.