I have created a Union Query of Three Tables A,B,C. Now My problem is that I would like the Result to display only Records from the three tables falling in the date Criteria that I specify. The Statement I used looks like this "TABLE[stock_track] UNION ALL TABLE[tblktzstocks]UNION ALL Select * from tblExpenseDetails where companycode=can;
This Union displays all Records. But I only want data between the dates I specify
Iam Stuck


Bmulenga

__________________
Bmulenga

select * from tab1 where date='???'
union
select * from tab2 where date='???'
union
select * from tab3 where date='???'



urt

Which table has the date field, and how are you passing this? Are you using a parameter entry in the Criteria line of the date field like:

Between #[Enter a Start Date:]# And #[Enter an End Date:]#

Or are you using some combo boxes on a form? With combos you can either do this in the Criteria line of the Date field:

Between #[Forms]![frmMyForm].[cboStartDate]# And #[Forms]![frmMyForm].[cboEndDate]#

Or you can pass it in code like:

Dim dtStart As Date
Dim dtEnd As Date
Dim sLink As String

dtStart = Me.cboStartdate
dtEnd = Me.cboEndDate

sLink = "[DateField] Between #" & dtStart & "# And #" & dtEnd & "#"

DoCmd.OpenReport sDocName, acPreview, , sLink

Did any of that help?


mmcdonal

Look it up at: http://wrox.books24x7.com

Thank you mmcdonal. All the three tables have a date field. They all have the same fields. What it is, is that data is being entered independently for different sites, and I am using a union to have a consolidated list. However, I want to view the data in the Union query based on the date criteria I specify. The Tables in the Union statement below are the ones I have used.
"I have created a Union Query of Three Tables A,B,C. Now My problem is that I would like the Result to display only Records from the three tables falling in the date Criteria that I specify. The Statement I used looks like this "TABLE[stock_track] UNION ALL TABLE[tblktzstocks]UNION ALL Select * from tblExpenseDetails where companycode=can;"This Union displays all Records. But I only want data between the dates I specify
Iam Stuck


Bmulenga

Thank you Urt for your response
I am failing to use the statements below as you sent them. Could you elaborate more on how I can use them. Everytime I tried them with the table names, the system was giving a syntax error!
select * from tab1 where date='???'
union
select * from tab2 where date='???'
union
select * from tab3 where date='???'


Bmulenga

I used urtivedi's solution and came up with this query string:

select * from table1 where date=#1/23/2008# union all select * from table2 where date=#1/23/2008# UNION ALL select * from table3 where date=#1/23/2008#;

This worked to show me all the records from 3 tables where the date matched the criteria. Column names are an issue. I would be inclined to add aliases since it gave me the PK from table1 for all three tables in one column called Table1ID, for example. You can add additional criteria of course.

How do you want to pass the date criteria?



mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks mmcdonal.
So far the query statement is executing without a syntax error. However, How do I make the query to pick records between two date ranges i.e [Beginning Date]and[Ending Date]

Bmulenga

Dear mmcdonal
I used the statement below to execute the query, However, I noticed that it only managed to pick records from the first Table 'Stock_track'in the statement and left out records from the other two tables where the date matched the criteria.
select * from Stock_track where date=#1/1/2008# union all select * from tblKTZstocks where date=#1/1/2008#union all select * from tblExpenseDetails where date=#1/1/2008#;

Going by this type of statement, I see that any time one wants to change the date criteria they have to go to the statement to change. Is there another way that could be used to enter the date criteria

Bmulenga

That's why I asked how you were submitting the date criteria. You can do this if you are using a form to select a date:

select * from Stock_track where date=#[Forms]![frmMyForm].[cboDateCombo]# union all select * from tblKTZstocks where date=#[Forms]![frmMyForm].[cboDateCombo]# union all select * from tblExpenseDetails where date=#[Forms]![frmMyForm].[cboDateCombo]#;

I was able to get records from each table using this. The problem was that all the date from Table2 and Table3 comes in under the column name from table1, that's why I suggested using an alias on some columns.

I think a better way to do this would be to create a table, then empty the table each time you want to use it, then dump the data from each other table one at a time as you run this, and then display data from the table, not the query.


mmcdonal

Look it up at: http://wrox.books24x7.com