Compare two tables look for gaps, append them in new table

[rustynails]

Must be A2002/2003 format. I can't open it in A2000 . I'll try and look at it tonight.

I've attached Access 2000 mdb for you

[ssanfu]

I've been trying to come up with "The Rules"

This is what I have so far: (in general)
1) If there is a record in schedule and a record in actual, then add it to the NewTable
2) If there is a record in schedule but not a record in Actual, then add it to the NewTable
3) If there is not a record in schedule but there is a record in Actual and tbnum is consecutive (1, 2, 3, ....), then add it to the NewTable
4) If there is not a record in schedule but there is a record in Actual, then ignore it

	Sch	Sch		Actual	Actual		Result
row#	odpair	tbnum		variation	tbnum		newtable
1	JFKATL	1		JFKATL	1		Add
2	JFKATL	2		JFKATL	2		Add
3				JFKATL	3		Add
4	PHXLAS	5					Add
5	MSPSEA	4					Add
6				PHXORD	4		ignore
7	ORDSLC	4					Add
8				ORDSLC	5		Add, but change tbnum from 5 to 400

Can you write down more rules?

I am still unclear about row 7 & 8 - how do you decide when to change the tbnum?

[rustynails]

I've been trying to come up with "The Rules"

This is what I have so far: (in general)
1) If there is a record in schedule and a record in actual, then add it to the NewTable
2) If there is a record in schedule but not a record in Actual, then add it to the NewTable
3) If there is not a record in schedule but there is a record in Actual and tbnum is consecutive (1, 2, 3, ....), then add it to the NewTable
4) If there is not a record in schedule but there is a record in Actual, then ignore it

	Sch	Sch		Actual	Actual		Result
row#	odpair	tbnum		variation	tbnum		newtable
1	JFKATL	1		JFKATL	1		Add
2	JFKATL	2		JFKATL	2		Add
3				JFKATL	3		Add
4	PHXLAS	5					Add
5	MSPSEA	4					Add
6				PHXORD	4		ignore
7	ORDSLC	4					Add
8				ORDSLC	5		Add, but change tbnum from 5 to 400

Can you write down more rules?

I am still unclear about row 7 & 8 - how do you decide when to change the tbnum?

Whee, you got it ssanfu

For row 7 and 8 here's the deal. You change tbnum when you encounter scenario 2 and create a new timeband. You change it because tbnum gives you a specific timeband, but in scenario 2, you create a new timeband (800-945), so for that reason you assign it a new tbnum. I just multiply it by 100, so the new timeband will be 500 in our case. dont worry about writing a query to change the timeband itself...just multiply it's associated tbnum by 100 and I'll take care of the rest.

[June7]

I have this much so far.

SELECT odpair, tbnum, segment, flight_type, service, timeband, "S" AS Source FROM Schedule
UNION SELECT variation, tbnum, segment, flight_type, service, timeband, "A" FROM (SELECT Actual.segment, Actual.flight_type, Actual.service, Actual.timeband, Actual.tbnum, Actual.variation
FROM Actual LEFT JOIN Schedule ON (Actual.variation = Schedule.odpair) AND (Actual.tbnum = Schedule.tbnum)
WHERE (((Schedule.odpair) Is Null) AND (Not (DLookUp("odpair","Schedule","odpair='" & [variation] & "'")) Is Null))) AS Q;

Sub Reconciliation()
'1) If there is a record in schedule and a record in actual, then add it to the NewTable
'2) If there is a record in schedule but not a record in Actual, then add it to the NewTable
'3) If there is not a record in schedule but there is a record in Actual and tbnum is consecutive (1, 2, 3, ....), then add it to the NewTable
'4) If there is not a record in schedule but there is a record in Actual, then ignore it
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("ScheduleActualUNION")
Debug.Print rs.RecordCount
'Here it gets really tricky. Need code to loop through recordset and adjust tbnum (and timeband if you want) if needed and write record to NewTable.
End Sub

[rustynails]

I have this much so far.

SELECT odpair, tbnum, segment, flight_type, service, timeband, "S" AS Source FROM Schedule
UNION SELECT variation, tbnum, segment, flight_type, service, timeband, "A" FROM (SELECT Actual.segment, Actual.flight_type, Actual.service, Actual.timeband, Actual.tbnum, Actual.variation
FROM Actual LEFT JOIN Schedule ON (Actual.variation = Schedule.odpair) AND (Actual.tbnum = Schedule.tbnum)
WHERE (((Schedule.odpair) Is Null) AND (Not (DLookUp("odpair","Schedule","odpair='" & [variation] & "'")) Is Null))) AS Q;

Sub Reconciliation()
'1) If there is a record in schedule and a record in actual, then add it to the NewTable
'2) If there is a record in schedule but not a record in Actual, then add it to the NewTable
'3) If there is not a record in schedule but there is a record in Actual and tbnum is consecutive (1, 2, 3, ....), then add it to the NewTable
'4) If there is not a record in schedule but there is a record in Actual, then ignore it
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("ScheduleActualUNION")
Debug.Print rs.RecordCount
'Here it gets really tricky. Need code to loop through recordset and adjust tbnum (and timeband if you want) if needed and write record to NewTable.
End Sub

Awesome. Thank you. I should be able to work out the rest. Thx

[ssanfu]

I was using a more VBA approach, but June's method is better.

Good luck with your project.....

[June7]

I went in numerous circles trying a strictly VBA approach opening and looping recordsets to try and produce the dataset I eventually configured with the nested query object. At that point, I found the remaining 'rules' too fuzzy for me to continue.

[ssanfu]

@June
I opened a union query to get the distinct odpair/variations. (There would be one record for JFKATL)
Then I opened a record set on Schedule, filtered on odpair. Same with Actual.

Then tried to create the logic. Since I didn't understand the rules enough, I went into an endless loop.....


I need a lot more experience/knowledge on making queries like the one you created... <sigh> (One query to do what I tried to do with 3..)

[June7]

I use the query builder to help construct queries then copy/paste SQL into other SQL to get the nested statement.