Compare Tables: Required vs Provided (1 Viewer)

lordi

New member
Local time
Tomorrow, 07:54
Joined
Jan 25, 2019
Messages
3
Hi to all,

Need help to compare 2 tables, REQUIRED vs PROVIDED.
Please refer to the attached image, the scenario is SOA NO from REQUIRED ex. 1.1.01.1.01 has been split into many in the PROVIDED ex. 1.1.01.1.01(1) , 1.1.01.1.01(2) , 1.1.01.1.01(3) etc. as indicated by the numbers inside the bracket, furthermore, there have been variations like ex. 1.1.01.1.01(1).CIR and 1.1.01.1.01(1).A

If you will look at the image, the excel on top has 2 parts, left as required & right side as provided, we where able to compare how many split rooms of a given required SOA NO and also the variations using a script, we would like to do that in access.

Hope somebody in these community can shed light.

Thanks to all.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:54
Joined
Feb 19, 2013
Messages
16,553
image not attached, if you cannot attach, zip the image up. At the moment your post is not clear without the image
 

plog

Banishment Pending
Local time
Today, 18:54
Joined
May 11, 2011
Messages
11,613
Before you do, make sure you are providing us 2 sets of data:

A. Starting sample data. This sounds like what you are giving us. Be sure it includes table and field names from all tables and enough data to cover all cases.

B. Expected results of A. Show us what data you hope to end up with based on the data in A. Don't tell us, show us with data what the end result should be.
 

lordi

New member
Local time
Tomorrow, 07:54
Joined
Jan 25, 2019
Messages
3
First of all, thanks to plog & CJ_London for the response.

I have attached a ZIP file that contains the the access database & the screen cap of excel report.

The result I need is the excel report.
 

Attachments

  • ACCESS.zip
    965.8 KB · Views: 81
Last edited:

plog

Banishment Pending
Local time
Today, 18:54
Joined
May 11, 2011
Messages
11,613
Lots of explaining to do.

1. Your screenshot is of 2 datasets. Are both the expected results? Or just the top?

2. You have 2 tables, Revit_import and Batch_15..., which is the required data, which is provided?

3. Revit has no soaNum=1.1.01.1.01 records: it has a bunch of records that have a string that starts with that but then contains other characters. Do all those records go to soaNum=1.1.01.1.01?
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:54
Joined
Jan 23, 2006
Messages
15,364
lordi,

Step back and describe what you are trying to do in simple, plain English. Just as you would tell an 8 yr old or your 80 y old granny.
 

lordi

New member
Local time
Tomorrow, 07:54
Joined
Jan 25, 2019
Messages
3
lordi,

Step back and describe what you are trying to do in simple, plain English. Just as you would tell an 8 yr old or your 80 y old granny.

Lots of explaining to do.

1. Your screenshot is of 2 datasets. Are both the expected results? Or just the top?

2. You have 2 tables, Revit_import and Batch_15..., which is the required data, which is provided?

3. Revit has no soaNum=1.1.01.1.01 records: it has a bunch of records that have a string that starts with that but then contains other characters. Do all those records go to soaNum=1.1.01.1.01?

Answer to No.1 & 3 question,

Top of image shows the report that we've done in excel (the report that I need, could be represented in other format), basically it compares the REQUIRED & the PROVIDED, the REQUIRED part contains the original SOA No. which is ex. 1.1.01.1.01 and so on, however if you will look at "the no. of rooms" column it says there are 5 instances of that same SOA No. Moving on to the PROVIDED part, it reports that there 5 rooms PROVIDED and it shows the sum of Total Area of that 5 rooms.

The provided part is getting all that information from the bottom part of the image, which lists out all the instances of the rooms PROVIDED, now to make the numbers unique to each instance, we added another tier which is the brackets, now the 1.1.01.1.01 becomes 1.1.01.1.01(1) and so on.

I need to be able to count 1.1.01.1.01(1), 1.1.01.1.01(2), 1.1.01.1.01(3), 1.1.01.1.01(4), 1.1.01.1.01(5) as 5 instances of 1.1.01.1.01.

Furthermore I want to be able to filter other variations such as 1.1.01.1.01(1).CIR and so on, and also x.x.xx.x.xx.A which is not shown in the image but is in the database.

Answer to No.2 question,

There are 2 table in the database, 1 is the Batch_15_unexpanded (Original SOA No. without brackets/unexpanded) which is the REQUIRED and the second is Revit_Import which is the PROVIDED (SOA No. with brackets/expanded).

Thanks very much.
 
Last edited:

plog

Banishment Pending
Local time
Today, 18:54
Joined
May 11, 2011
Messages
11,613
Comparing this data is made very difficult because your soaNum in Revit_import is not normalized. Because you have suffixed extra data onto the part you want to use (e.g. .CIR, (1), .A, (2) etc.) in a non standard way its going to require a lot of work just to clean up the soaNum to get it to resolve to the value you want.

Where is this data coming from? Why is it stored as such? And how familiar with VBA are you?
 

Users who are viewing this thread

Top Bottom