Problem with Natural Join

[Kaisa]

Please help!!!

I am not a database developer although I've always thought I was pretty good for a non-specialist. I guess, I was wrong.


I am trying to join two queries.

I am not sure what I am doing wrong, seems like such a trivial command but I keep getting a syntax error message.

My query is:

SELECT *
FROM 023_Option_Total_2018 NATURAL JOIN 023_Option_Total_2025;


These are basically two queries with absolutely similar structure, except one goes from 2011 to 2018 and the other from 2019 to 2025




Why is my "From" wrong? what am I missing?

[jzwp11]

If you are using Access "NATURAL JOIN" is not valid syntax. If the tables are identical in structure that tells me that there should be only 1 table. You can add an additional field to distinguish records of the two time periods. You would then use a query to pull the records you want.

[Kaisa]

Yes, they are identical but I had reason for separating them into two.

These queries are based on few other queries with excessive calculations and since each of them have around 100 columns, Access refused to calculate stuff in one query (too complex).

I am sure there is a much better way to deal with this issue but like I said, I hope I am pretty skilled for an accountant

Could you please let me know what the correct syntax in Access is?

[jzwp11]

Technically speaking having two tables with the same structure violates normalization rules, so I would recommend merging the two tables at some point.

But to answer your immediate question, the only other way to do it is to bring the data from the two tables/queries together using a UNION query. This has to be done in SQL view. The order and number of fields in the two SELECT clauses must be the same.

SELECT field1, field2 ...
FROM table/query1

UNION

Select field1, field2...
FROM table/query2

[Kaisa]

This is actually the point when I try to merge them - all the calclulations are done.

Because I was not able to solve the merging problem, what I do is copy the data into excel but I thought it would be nice to be able to join them if I could.

I've used union query but only to add the rows, let me see if this will work with the columns as well.

[Kaisa]

It did not work

I have two tables (queries)

1) ID 2011 2012 (10 rows)
2) ID 2013 2014 (10 rows)

and I want to get

ID 2011 2012 2013 2014 (10 rows)

What I got was

ID 2011 2012 (20 rows)



Thanks anyways

[jzwp11]

So you want to keep the fields distinct? Ok, sorry I misinterpreted what you were after. It sounds like you need a result that is the Cartesian Product between the two tables, if so try this

SELECT query1.*, query2.*
FROM query1, query2

[Kaisa]

I tried this (in design view) too but then I got problems with duplications becuase technically the two queries dont have any ID's that the records cound be identified with - I just have three fields that I the same in both. but when I join those, I get more combinations or something.

I guess, I better think of a way to introduce some kind of unique id for each record, right?

[jzwp11]

It sounds like you need a foreign key in each record that relates back to something else. Unfortunately, I do not know what that something else is. Typically, you might have something like this:

tblCustomer
-pkCustID primary key, autonumber
-txtCustomerName

table1
-ID
-2011
-2012
-fkCustomerID foreign key to tblCustomer

table2
-ID
-2013
-2014
-fkCustomerID foreign key to tblCustomer

You would then construct the query with the three tables joining via the customerID field

[Kaisa]

Thank you very much for everything, I think I got an idea how to fix everything.

[jzwp11]

Sorry that I could not be of more help.

[Kaisa]

Sorry that I could not be of more help.

You helped a lot!

Thank you very much.