Combining Tables as "OR"

[FogLog]

I feel like this is probably going to turn out to be an extremely simple question, but I can't seem to figure it out.

I have two tables, structured the same, with three fields: ID, CriteriaA, CriteriaB. CriteriaA and CriteriaB are both booleans. There are some IDs that are only in table1, some that are only in table2, and some that are in both.

I want to combine the tables such that if one or both are true for any given ID, Criteria, the result is true in the resulting table, otherwise it is false.

Is there a simple way to do this? Also if it makes any difference, table1 and table2 are already SQL queries, and I'd prefer to do this all in one query.

Thanks in advance for any help!

 

[plog]

Good news, its not going to be a simple question. You've got 2 things working against you: 1 your table structure and 2 Access doesn't support full outer joins (http://www.w3schools.com/sql/sql_join_full.asp).

1. You shouldn't have tables with the exact same structure. All that data should be in one table. My guess is you differentiate the data by the name of the table--that is incorrect. Table nor field names should store data relevant to the data. Instead, you should combine the data in those tables into one table and then add a new field to do the differentiation between the two.

2. A full outer join combines all data in two data sources, no matter if matching data is in either one. Like I said, Access doesn't support those, so you can't use them.

My suggestions is to get your data into the 1 table structure I explained in #1. Once you do that, all your data is in one place and you can run the query you need off of it.

 

[FogLog]

Thanks for the response.

I don't have tables with the same data structure, I simply queried my underlying tables into the form of two tables with the same data structure since I assumed it would then be easy to combine them in the way I needed to. Bad assumption I guess!

Assuming I simply had all of the data in one table, though, I still don't understand how I would then query it into the form that I need. That is, combining all instances of the same ID with an OR operation on each field.

I basically have two tables, one with data that defines a recurring event, and another table for one-time overrides of the recurring schedule. I don't want the one-time overrides to completely replace the recurring event though, but rather just add to it anything that the recurring event doesn't already include. I could combine these into one table, but that seems wrong as there would be a bunch of useless fields for the one-time overrides.

Given a date, I can query my recurring event table to show what is scheduled for that day, and I can do the same for the one time additions, I just don't know how to combine the two. (These are the two "tables" that I initially presented.)

I will also be adding recurring and one-time cancellations, though this seems easier as I can just EXCEPT any occurrences. Or I guess use a LEFT JOIN where the ID is null, since I don't think Access has EXCEPT either?

 

[Minty]

You need a Union query I think. Air Code;

Select ID, CritA, CritB 
From
Table_A
Union
Select ID, CritA, CritB
From Table_B

 

[plog]

Could you post sample data to demonstrate what you are talking about? Provide table level data (not the 2 queries) and then what you expect as results from that sample data. Be sure to include all relevant field and table names.

 

[FogLog]

tbl_Recurring Deliveries:

ID, ClientID, Deliver, StartDate, EndDate, DayOfWeek, Frequency, ReferenceDate, DeliverA, DeliverB
1, 1, True, 9/23/14, null, 3, 1, 9/23/14, True, False
2, 2, True, 9/23/14, null, 3, 1, 9/23/14, True, False
2, 3, True, 9/23/14, null, 3, 1, 9/23/14, True, True

This says that starting on 9/23, every Tuesday client1 and client2 should receive A and not B. Client3 should receive A and B. The ReferenceDate is for someone on a 2, 3, etc. week schedule, so I know when to measure the date from. The deliver field is for when I add delivery exceptions instead of additions later.

ID, ClientID, Deliver, ModifyDate, DeliverA, DeliverB
1, 1, True, 9/23/14, True, True
2, 2, True, 9/23/14, False, True
3, 4, True, 9/23/14, True, True

I think this is self-explanatory.

The result I would want, given an input date of 9/23/14, would be:

ClientID, DeliverA, DeliverB
1, True, True
2, True, True
3, True, True
4, True, True

Given an input date of 9/30/14, I would want:

ClientID, DeliverA, DeliverB
1, True, False
2, True, False
3, True, True

Hopefully that makes sense and I formatted so it's actually readable. And yes, I realize that having the DeliverA and DeliverB fields violates some rule, but it just seems easier at the moment.

 

[plog]

The main issue you have is you are trying to create data where there is none--no where in your data do you have 9/30 data, but you want a query to produce it. That's really difficult.

It may be best to just create a table that contains all the deliveries you need to do on a date. That would get rid of your exceptions table and put all data in one table and then you could see date by date what was delivered where.

With that said, I have a solution for you. You told me 'tbl_Recurring' was the table name of the recurring data, but you didn't give me the table name for the exception table, so I used 'Exceptions'. You will need to change all references to that table in my code to the correct name if that's not it.

To get the data you want, first you will need a function. Paste this into a module:

Public Function MakeDelivery(in_Exception, in_Recurring, in_ExceptionDate, in_RecStartDate, in_RecEndDate, in_DeliveryDate)
    ' determines if a delivery will be made on in_DeliveryDate
    
ret = False                     ' return value, by default is false
bool_checkRecurring = True      ' determines if should check recurring date to see if delivery is handled by that data

If in_ExceptionDate = in_DeliveryDate Then
    ' if delivery date has an exception uses that data, no need to look in recurringdata
    ret = in_Exception
    bool_checkRecurring = False
End If

   
If IsNull(in_RecStartDate) Then bool_checkRecurring = False
    ' if exception delivery is true or no recurring start date, no need to check recurring value
    
If bool_checkRecurring = True Then If in_Delivery < in_StartDate Or DateDiff("d", in_RecStartDate, in_DeliveryDate) Mod 7 <> 0 Then bool_checkRecurring = False
    ' if recurring start date is after delivery date or not on same weekday, no need to check recurring value

If bool_checkRecurring Then
    ' usinging recurring date to determined if will get delivered--if end date is null or after delivery date will use
    If (IsNull(in_RecEndDate)) Then ret = in_Recurring
    Else: If (in_RecEndDate >= in_DelieveryDate) Then ret = in_Recurring
    
End If

MakeDelivery = ret
End Function

It determines if a delivery is to be made on a specific date. It first looks at the exception table and uses that data, if no exception exists it sees if a recurring delivery is applicable.

I mentioned a FULL OUTER JOIN earlier (get all data from 2 tables) that's what you need in this instance. Just because it's not natively supported doesn't mean we can't hack one together.

SELECT Exceptions.ClientID, MakeDelivery([Exceptions].[DeliverA],[tbl_Recurring].[DeliverA],[ModifyDate],[StartDate],[EndDate],CDate("9/30/2014")) AS A, MakeDelivery([Exceptions].[DeliverB],[tbl_Recurring].[DeliverB],[ModifyDate],[StartDate],[EndDate],CDate("9/30/2014")) AS B
FROM Exceptions LEFT JOIN tbl_Recurring ON Exceptions.ClientID=tbl_Recurring.ClientID
WHERE (((Exceptions.ModifyDate)=#9/30/2014#));

Paste that in a query and name it 'sub_Deliveries_1'. It gets all the applicable data in exceptions.

SELECT tbl_Recurring.ClientID, tbl_Recurring.DeliverA AS A, tbl_Recurring.DeliverB AS B
FROM tbl_Recurring LEFT JOIN sub_Deliveries_1 ON tbl_Recurring.ClientID=sub_Deliveries_1.ClientID
WHERE (((sub_Deliveries_1.ClientID) Is Null) AND ((MakeDelivery(False,True,Date(),[StartDate],[EndDate],CDate("9/30/2014")))=True));

Paste that in another query and name it 'sub_Deliveries_2'. It gets all applicable date from the tbl_Recurring that's not in Exceptions. Now you combine the 2 together with this:

SELECT * FROM sub_Deliveries_1
UNION ALL SELECT * FROM sub_Deliveries_2;

That will produce the results you want. A few problems:

1. you have to manually change the dates in the sub-queries to get results for a new date--there's like 4 different spots you have to change.

2. It only works on a weekly frequency. If you make 2 or 3 deliveries a week, this will not work, you will have to change the function to incorporate that logic.

 

[FogLog]

Wow, thanks! Didn't expect someone to go through that much effort. I'm not sure if it's doing exactly what I want or not, and it will take me awhile to go through and figure out what all that is actually doing, but I think I will be able to take it from here.