Grouping 2 Tables in a Report. (1 Viewer)

[t_bard]

Hello,
I have been told what I want to do cannot be done with Tables. Now I am wondering if it can be done in a report. I am trying to combine 2 Tables along a common field ID, but there are several issues. Each ID represents a city block and on each block there are many traffic signs. Table_OLD lists an ID (identifying the block) with a row for each sign at, say, a year ago. Table_NEW lists an ID (identifying the block) with a row for each sign as they are currently. I want to see them side-by-side, but sometimes a particular ID (block) will have more signs in Table_OLD. Other times it will have more signs in Table_NEW. So with some IDs (blocks) there will have to be blanks in the Sign Description field on the Table_OLD side and at other IDs, there will have be blanks in the Sign Description field on the Table_NEW side.
I tried a UNION (full) join of the Tables on the ID field, but it creates many duplicate rows because each particular ID in each Table has many entries. When I tried to put both Tables in a report side-by-side, Access insisted I link them and then created the same duplicate fields.
So in a perfect world my report would look like the attached screenshot (Tables.png).


The idea being that the list of signs align (are grouped by?) along the same ID number and leave blanks where there are fewer signs in one table or the other.
I can physically put the two Tables next to each other so it seesm logical that Access could so it in a much neater way.
Thanks for looking at this.

 

[vbaInet]

Hard to visualise without seeing a screenshot of the two tables.

 

[plog]

You've described a full outer join--all records from all tables in a join. Access does not support the SQL that allows these types of queries as far as I know. However, you can fake it with subqueries that get rolled up together in a Union query.

I found a couple links which describes the process:

http://www.alvechurchdata.co.uk/company/accouterjoin.html

http://www.databasejournal.com/feat...-of-a-FULL-OUTER-JOIN-in-Microsoft-Access.htm

 

[t_bard]

Thanks for your reply. I have been able to do the Full Join with the SQL UNION query, but Access doesn't seem able to leave the Table_OLD or Table_NEW Sign Description fields blank when there are fewer entries on the OLD or NEW fields. For example, when there are more on the OLD side it duplicates other Sign Descriptions on the NEW side to fill in the blanks. So one sign can look like it occurs several times. That's why I started looking at a report and maybe Groupings to get the effect I wanted.

 

[vbaInet]

Upload a db containing some sample records of both tables and the union query and I'll show how it can be done.

Access isn't going to pad the unrelated values with Null automatically. It's either you insert it into a temp table or you construct it in SQL.

 

[t_bard]

vbaInet
If you would have a look, that would be great. I am attaching a sample to this post.
Here is the UNION Query I created that doesn't quite work:
SELECT [Q_W-O_Match_Desc_ID].UID, [Q_W-O_Match_Desc_ID].SGNH_Hra_Main_Street, [Q_W-O_Match_Desc_ID].SGNH_Hra_From_Street, [Q_W-O_Match_Desc_ID].SGNH_Hra_To_Street, [Q_W-O_Match_Desc_ID].SGNH_Hrap_Sos, [Q_W-O_Match_Desc_ID].SR_Distx, [Q_W-O_Match_Desc_ID].SR_Arrow, [Q_W-O_Match_Desc_ID].SRP_Seq, [Q_W-O_Match_Desc_ID].NIR_Desc, [Q_W-O_Match_Desc_ID].Desc_ID_B, [Q_W-O_Match_Desc_ID_OPPOSITE].SGNH_Hr_Order, [Q_W-O_Match_Desc_ID_OPPOSITE].SR_Distx, [Q_W-O_Match_Desc_ID_OPPOSITE].NIR_Desc, [Q_W-O_Match_Desc_ID_OPPOSITE].Desc_ID_A
FROM [Q_W-O_Match_Desc_ID] LEFT JOIN [Q_W-O_Match_Desc_ID_OPPOSITE] ON [Q_W-O_Match_Desc_ID].UID = [Q_W-O_Match_Desc_ID_OPPOSITE].UID
UNION SELECT [Q_W-O_Match_Desc_ID].UID,[Q_W-O_Match_Desc_ID].SGNH_Hra_Main_Street, [Q_W-O_Match_Desc_ID].SGNH_Hra_From_Street, [Q_W-O_Match_Desc_ID].SGNH_Hra_To_Street, [Q_W-O_Match_Desc_ID].SGNH_Hrap_Sos, [Q_W-O_Match_Desc_ID].SR_Distx, [Q_W-O_Match_Desc_ID].SR_Arrow, [Q_W-O_Match_Desc_ID].SRP_Seq, [Q_W-O_Match_Desc_ID].NIR_Desc, [Q_W-O_Match_Desc_ID].Desc_ID_B, [Q_W-O_Match_Desc_ID_OPPOSITE].SGNH_Hr_Order, [Q_W-O_Match_Desc_ID_OPPOSITE].SR_Distx, [Q_W-O_Match_Desc_ID_OPPOSITE].NIR_Desc, [Q_W-O_Match_Desc_ID_OPPOSITE].Desc_ID_A
FROM [Q_W-O_Match_Desc_ID] RIGHT JOIN [Q_W-O_Match_Desc_ID_OPPOSITE] ON [Q_W-O_Match_Desc_ID].UID = [Q_W-O_Match_Desc_ID_OPPOSITE].UID
WHERE [Q_W-O_Match_Desc_ID].UID IS Null;
Thanks!

 

[vbaInet]

First of all, there's a problem with your tables not being normalised. Those two tables could have gone into one. Is there any reason why they are separate or you aren't sure about the normalisation process?

I will try and see if it can be done using queries but I need to know what fields I should be basing the comparison on? You mentioned three fields, can you tell me which three.

 

[t_bard]

Thanks for taking the time to look at this.

These Tables are the result of queries of much larger Tables which are probably normalized because they are maintained by people much more knowledgeable than I. One is a BEFORE Table and one an AFTER Table. They are based on unmatched fields queries from Present to Past and Past to Present. I want to compare them in one report so I can see what is unmatched in the present next to what is unmatched in the past. This would reduce the amount of reading and eyeballing I have to do an incredible amount.

I really need all of the fields, but the UID, DISTX, ARROW, SIGN DESCRIPTION are the most important. The link is the UID field because each UID describes a city block. And each block has many traffic signs, only some of which change from the BEFORE Table to the AFTER Table.

Each table has about 20,000 rows and the final result should be about as many. Basically, for every block I would like to compare the list of signs for that block in the AFTER Table next to the list of signs from the BEFORE Table. Sometimes the list is longer on the AFTER side, sometimes on the BEFORE. I would like to have blanks when the rows for the same UID are uneven, but the UIDs (or Blocks) for both Tables would always be alongside each other for easy comparison.


I was told this can't really be done with Tables, so I thought maybe I could use the Grouping function to do it in a Report.


Is this possible?


Thanks

 

[vbaInet]

Each table has about 20,000 rows and the final result should be about as many. Basically, for every block I would like to compare the list of signs for that block in the AFTER Table next to the list of signs from the BEFORE Table. Sometimes the list is longer on the AFTER side, sometimes on the BEFORE. I would like to have blanks when the rows for the same UID are uneven, but the UIDs (or Blocks) for both Tables would always be alongside each other for easy comparison.

So all we're doing is comparing against the UID. I will have a look.

I was told this can't really be done with Tables, so I thought maybe I could use the Grouping function to do it in a Report.


Is this possible?

Like I mentioned in my first post, it can be done with temp tables but I only use this as a last resort. We might be able to do it in a report or a query. I will let you know which is easier.

 

[t_bard]

Hi Vba Net,
Your idea of temporary tables could work fine for me. Let me know if you think it's possible to get the effect I'm looking for.

ThanKs

 

[vbaInet]

The reason why the Full Outer Join explained in the links provided above won't work for you is because your tables are not normalized. It's a pretty tedious task getting two unnormalized tables to function properly in queries. You definitely need to normalize asap.

Attached is a solution to meet your needs but it will require a substantial amount of time to setup. If you're struggling with setting up on your db, simply copy the 25k records from both tables and paste them into the original tables in th attachment.

For your edification, I will explain what some of the queries, table and reports do (using A...Desc table as an example):

tblAPatched - Is populated by those records that exist in both tables but with multiple records to match the B table. This is the temp table but never gets deleted.
qryA - Based on the A table. Just to make the table name short because all other queries will be using this query.
qryACount - A count of records per UID
qryADiff - The difference in record count between A and B, per UID
qryNotInA - Records from B that are not in A
qryAUnionAll - A UNION ALL between qryA, tblAPatched and qryNotInA
rptA - Report based on qryAUnionAll
rptFullOuterjoin - Report displaying rptA and rptB side-by-side for comparison
mdlPrepSource - Module called from rptA's Open event via Expression Builder to populate tblAPatched before the report is displayed.

I'm sure you understand what I mean by A and B tables?

 

[t_bard]

WHOA! This looks amazing. Will take me some time to understand it though.

Thanks so much for getting back to me!!!

 

[vbaInet]

You're welcome! Let us know how it goes.

Oh by the way, I forgot to mention, the Sorting in the reports - rptA and rpt - are important. The first one is sorted in Ascending order whilst the second is in Descending order.

 

[t_bard]

V,

Have finally had a chance to go through your fine work in some detail and it is a bit over my head (for now). For example mdlPrepSource is a mystery to me. But obviously you have done what others could not do and so for my first attempt to use it I will paste my latest data into the original Tables as you suggested,

This looks like it will save me a whole lot of time and eyestrain.


Thanks again.!

 

[t_bard]

V,

I do have a question though. I want to add fields to your final report from the original Tables, but the report seems to be generated in some unique way as the Design View doesn't even show the data fields and when I add a data field it doesn't preview correctly.

Do you recommend a way to add fields to the final report?

Thanks