Query to search two tables

[MCCDOM]

Hi There,

I am trying to generate a report where it shows me data up to a specified date. Currently I have a query that searches a table (tblBreakdown in this case) and produces a report with all data entry's that are equal or before the specified date. Now to the part I can't get working. I've got another table called tblDeleted which contains the information that was once from tblBreakdown but has been deleted from there and moved to tblDeleted where it now has a date of deletion. What I would like to achieve is that I can generate a report up to a certain date that contains data from both tables which matches the criteria.

Thanks in advance,

Dom

 

[Ranman256]

Union query,
Select * from tBreakdown between dates
Union
Select * from tDeleted between dates

 

[MCCDOM]

Thanks Ranman256,

I have created a union query that pulls all the data through but when I add the criteria from the original query that sorted the tblBreakdown by date order I get an error "Syntax error (missing operator) in query (((tblBreakdown.BreakdownID AS ID) Is Not Null) AND ((tblBreakdown.DateAdded AS [Date])<=[Forms]![frmReportLookup]![txtMonthEnd]))"
It worked perfectly on the original query just not as a union query.

SELECT tblBreakdown.BreakdownID AS ID, tblBreakdown.MailboxID, tblBreakdown.OfficeID, tblBreakdown.StaffID, tblOffices.DeptNominalCode, tblStaff.FirstName, tblStaff.LastName, tblBreakdown.DateAdded AS [Date], tblMailbox.Cost, tblMailbox.[Mailbox Type]
FROM tblStaff INNER JOIN (tblOffices INNER JOIN (tblMailbox INNER JOIN tblBreakdown ON tblMailbox.MailboxID = tblBreakdown.MailboxID) ON tblOffices.OfficeID = tblBreakdown.OfficeID) ON tblStaff.StaffID = tblBreakdown.StaffID
UNION
SELECT tblDeleted.DeletedID AS ID, tblMailbox.MailboxID, tblOffices.OfficeID, tblStaff.StaffID, tblOffices.DeptNominalCode, tblStaff.FirstName, tblStaff.LastName, tblDeleted.DateDeleted AS [Date], tblMailbox.Cost, tblMailbox.[Mailbox Type]
FROM tblStaff INNER JOIN (tblOffices INNER JOIN (tblMailbox INNER JOIN tblDeleted ON tblMailbox.MailboxID = tblDeleted.MailboxID) ON tblOffices.OfficeID = tblDeleted.OfficeID) ON tblStaff.StaffID = tblDeleted.StaffID
WHERE (((tblBreakdown.BreakdownID AS ID) Is Not Null) AND ((tblBreakdown.DateAdded AS [Date])<=[Forms]![frmReportLookup]![txtMonthEnd]));

 

[plog]

I've got another table called tblDeleted which contains the information that was once from tblBreakdown

That's not how databases are to work. You don't move data around--instead you mark it. What you are doing is storing an attribute of the data in the table name--instead you should be storing that attribute in the record itself.

Simply put, you need a [Deleted] field to designate records as deleted instead of moving them to a whole new table. You do that, you're query becomes trivial and you avoid situations like this in the future.

 

[MCCDOM]

Hi Plog,

I have taken your advice and added another column to my tblBreakdown called DateDeleted. In my report query it checks to see if there is a date in DateDeleted and if so if it falls within a month from the date specified on the form. If it does it gets reported.

The SQL code I have used if people are interested is:

SELECT tblBreakdown.BreakdownID, tblBreakdown.MailboxID, tblBreakdown.OfficeID, tblBreakdown.StaffID, tblOffices.DeptNominalCode, tblStaff.FirstName, tblStaff.LastName, tblBreakdown.DateAdded, tblMailbox.Cost, tblMailbox.[Mailbox Type], tblBreakdown.DateDeleted
FROM tblStaff INNER JOIN (tblOffices INNER JOIN (tblMailbox INNER JOIN tblBreakdown ON tblMailbox.MailboxID = tblBreakdown.MailboxID) ON tblOffices.OfficeID = tblBreakdown.OfficeID) ON tblStaff.StaffID = tblBreakdown.StaffID
WHERE (((tblBreakdown.BreakdownID) Is Not Null) AND ((tblBreakdown.DateAdded)<=[Forms]![frmReportLookup]![txtMonthEnd]) AND ((tblBreakdown.DateDeleted) Is Null)) OR (((tblBreakdown.DateDeleted) Between [Forms]![frmReportLookup]![txtMonthEnd] And DateAdd("m",-1,[Forms]![frmReportLookup]![txtMonthEnd])));

Thanks again plog and Ranman256 for your help.

Kind regards,

Dom