How to combine 2 similar fields from 2 separate tables into 1 query ?

[KingOf206]

I have attached an image of the example I am trying to explain.



Ok so I already have 2 tables set up, lets call them table 1 and table 2. I have different records that have 1 Primary key that are in relationship. But I have another Field "type" (see image) that I would like to find and count up each record in the 2 tables that have the similar field and do a count so I can see the count totals from each table (of how many types there are).

I mainly work out of design view, not very familiar with SQL. This seemed easy to do but when I began doing it I was unsuccessful.

I hope someone can assist me with this. Again I am access intermediate-ish, so an explanation that is simply would be greatly appreciated. Looking for finish this up for a report out to work this coming friday.

I hope the image attached helps out!!

[June7]

Build a UNION query then use that query in an aggregate GROUP BY (Totals) query.

[KingOf206]

Build a UNION query then use that query in an aggregate GROUP BY (Totals) query.

When I click on "Union" it brings up an sql blank sheet. I am not sure where to go from there....?

[June7]

Have to type or copy/paste the SQL. Example UNION:

SELECT ID, [Name], [Type], 1 AS Source FROM Table1
______ SELECT ID, [Name], [Type], 2 FROM Table2;


For some odd reason, forum no longer allows me to type UNION in the example query. Put UNION in placed of the line.

[KingOf206]

Have to type or copy/paste the SQL. Example UNION:

SELECT ID, [Name], [Type], 1 AS Source FROM Table1
______ SELECT ID, [Name], [Type], 2 FROM Table2;


For some odd reason, forum no longer allows me to type UNION in the example query. Put UNION in placed of the line.

Ok I have used that SQl code, but I dont know how to group the totals. it doesnt do the group by function at this moment. (let me know if pics would help for a better understanding) Like I said, I ma not familar with SQL code i work primaryly out of desgin view, so anything outside of that isnt as clear to me.

More help on this would be appreciated... I am still stuck and trying to get this done by tomorrow.

[June7]

You have the UNION query correctly showing the data?

Now use that query just like a table (except can't edit data) in another query or as the RecordSource of a report.

Use the query builder if you want to do an aggregate query. Or build a report and use its Sorting & Grouping features with aggregate calcs in footers.

[KingOf206]

You have the UNION query correctly showing the data?

Now use that query just like a table (except can't edit data) in another query or as the RecordSource of a report.

Use the query builder if you want to do an aggregate query. Or build a report and use its Sorting & Grouping features with aggregate calcs in footers.

YES it worked perfect!!!

Thank you so much!!! #SLOVED

[Skincb]

first create a union query from two tables




then with another query to count and summation type name

SELECT ID, NOME, TIPODa Tabela 1 UNION ALL SELECT ID, NOME, TIPO DE TABLE2 ORDER BY TYPE;

UNION.TYPE SELECIONE, Contagem (UNION.NOME) AS ContarDeNOMEFROM [UNIÃO] GROUP BY UNION.TYPE;