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

[rustynails]

Hello AccessForums. I am new to the site.

I'm in bit of a pickle and anyone who can solve this for me I will be greatly indebted to. I have two tables: Actual and Schedule. My job is to compare these two tables, look for gaps, and fill them in a new table (append). Important point: tbnum corresponds to timeband. It's something I came up with to identify gaps easily. It's not binding though and I'll remove it later. I have created these sample tables:





End Result:



There are 2 scenarios

1) JFKATL: there is a match in Actual table, so bring the original record for JFKATL from Schedule as well as from Actual for the missing JFKATL record into NewTable

2) ORDSLC: there is no match in Actual table, but there is an apparent "gap" in timeband. So extend the timeband for the unmatched record and paste it in NewTable

Thank you AccessForums community.

[ssanfu]

I've been looking at this for a while. Very confusing, to say the least. Can't understand how "New Table" [tbnum] 4 is a gap.

What fields determine a gap? Do you group by odpair/variation, then by [timeband] to determine a gap?

[rustynails]

You are absolutely correct ssanfu there appears to be a mistake. That's what I get for creating a sample out of scratch. I sincerely apologize. Here's a new sample:



Scenario 1 (highlighted in yellow): The idea is to look for timebands that are there in Actual, but are missing in Schedule. Sorry if I'm being confusing. So in this case, JFKATL has two schedules, 500-559 and 600-659. But in Actual table, it is supposed to have another record: 700-759. So that's the missing gap which we take and put into our NewTable, along with records from Schedule table.

Scenario 2 (highlighted in Green): In this case, for ORDSLC there is a timeband of 800-859 in Schedule table. But in Actual table, we only have timeband of 900-945. So there is no match between schedule in actual when it comes to timeband, yet variation tells us that same flight (suppose the flight numbers also match) will depart at 900-945. So what we do in this case is we extend the original timeband from 800-859 to 800-945, and put it in the newtable. But timeband has changed, so change it to something else like 400.

I hope I'm clear but please let me know if there's anything else.

[ssanfu]

What are the fields you are looking at for comparison? Only timeband (tbnum) and odpair?? What about flight numbers, dates,...??

I think I've got JFKATL:
Attachment 13182

I'm still (more?? ) confused on ORDSLC:
Attachment 13183
Why wasn't tbnum 5 added to the New Table?
Why was the timeband expanded to cover 2 hours and the tbnum changed to 400?

I don't understand the rules yet.....
I think this will take a lot of code..

[June7]

Did you mean Schedule is supposed to have another record 700-759 because it is in Actual?

1. Did you try the Find Unmatched query wizard?

2. Yes, looks like a complex VBA procedure will be needed.

Why a NewTable and not just adding records to Schedule?

[rustynails]

What are the fields you are looking at for comparison? Only timeband (tbnum) and odpair?? What about flight numbers, dates,...??

I think I've got JFKATL:
Attachment 13182

I'm still (more?? ) confused on ORDSLC:
Attachment 13183
Why wasn't tbnum 5 added to the New Table?
Why was the timeband expanded to cover 2 hours and the tbnum changed to 400?

I don't understand the rules yet.....
I think this will take a lot of code..

Yes the business logic is confusing

I didn't add flight num, departure date/time and all extra fields...didn't want to make it anymore confusing! In scenario number 2, the timeband was expanded to cover 2 hours because ORDSLC schedule had 800-859, and ORDSLC actual had 900-959. There is no match so we will expand the original timeband to cover the next timeband instead of directly bringing in the next timeband. It's needlessly confusing. If there is no next timeband, we will leave it.

[rustynails]

Did you mean Schedule is supposed to have another record 700-759 because it is in Actual?

1. Did you try the Find Unmatched query wizard?

2. Yes, looks like a complex VBA procedure will be needed.

Why a NewTable and not just adding records to Schedule?

Yes schedule is supposed to have another record 700-759 because it is in Actual, but we are not supposed to alter original flight schedule data so we create new table. I did try working with unmatch query wizard, but wasn't able to go far. I will revisit it again.

[ssanfu]

Can you provide a dB with sample data? It will be easier to generate the code.

Can you list the rules? How you make decisions when you manually compare the records?

[June7]

I am also confused by ORDSLC. How is that situation different from JFKATL? They both have record in Actual not in Schedule. The example in post 3 shows two records in NewTable with overlapping timeband - why?

The example in post 1 almost made sense to me because ORDSLC has a record in each table with the same tbnum but different timebands. I can see that this would need to be reconciled. But then you assigned new tbnum and lose any connection to the original data.

Is that second image in post 3 unnecessary?

[rustynails]

June7, I assigned a new tbnum because there is no tbnum for the timeband 800-945. The second image, I just noticed. Wtf is it doing there. It's unnecessary. Let me clean up.

I think I know where the confusion is stemming from. The match has to be both for odpair+tbnum (or TimeBand...lets use tbnum because it's easier and reason why I made it); ORDSLC4, not just ORDSLC. So in Scenario 1, there is a match for JFKATL+tbnum (JFKATL1, JFKATL2) in Actual, so we can bring the third value JFKATL3 into newtable. In Scenario 2, there is no match for ORDSLC+tbnum (ORDSLC4). But, there is a ORDSLC5, so in that case, we need to extend ORDSLC4's timeband from 800-859 to 800-945, a new timeband we just made up. Don't worry about modifying the timeband itself like adding 46 to 859 to make the timeband 800-945 (will require weird calculations that VBA can better deal with). I will take care of it manually. Just changing the timeband to 400 will do. But what if there is instead ORDSLC6 in Actual? Do nothing, because it's too far out to extend the timeband (can only be extended + 1 hour, or 46 mins in scenario 2's case. I picked 945 because that's the breakfast cutoff time for airlines )

Sorry guys I didn't point this out earlier. I really appreciate your help. If there is still confusion let me know and I will try to squeeze the dev db into a sample size from work I tried to replicate the scenario I'm working with but apparently I'm incredibly terrible at it. It looks like scenario 2 is the problem child. But if I can get away with scenario 1 as well, I will be happy

[June7]

As Steve suggested, provide sample db, save us having to build, so can test code.

[rustynails]

As Steve suggested, provide sample db, save us having to build, so can test code.

Ok, I'm attaching the sample db I created.

[June7]

I might not be able to deal with this until this weekend. If you want to expand the pool of analysts, might want to provide mdb version.

[rustynails]

I might not be able to deal with this until this weekend. If you want to expand the pool of analysts, might want to provide mdb version.

No problem. Here is MDB version.

[ssanfu]

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