Help on Append Query

[Pettor]

Hi All,

Since I am a newbie in databases I would like a help so as to amend an existing append query in MS Access.

I have an already made MS Access program that does some actions and calculations and returns some data to a final table.

One of the steps is to execute an append query called "APPEND_PRO_TBL" that updates a blank table called "PRO_TBL". The append query doesn't uses the data from a table but from another simple query called "PRO3".

Since the MS Access has restrictions to the returned data of a query and since the columns that I need are more than 100, I have created a new query called "PRO4" where it selects the additional data that the "PRO3" can not return due to the size restriction.

So I have the "PRO3" query that returns let's say 50 columns of data and a new "PRO4" query that returns another 50 columns of data and I would like to combine them both on the table "PRO_TBL" through the append query "APPEND_PRO_TBL".

What I have done so far, that doesn't seem to be working, is to add to the table "PRO_TBL" the columns that are related with the query "PRO4" (i.e. DATE) in order to be ready to accept the data of the new query and through the environment of Access I add on the append query "APPEND_PRO_TBL" the new "PRO4" query so as to be combined with the "PRO3" but it doesn't seem to be working. MS Access returns as an SQL code like the below and I receive the message "Query to complex":

INSERT INTO PRO_TBL ( ID, MDATE )
SELECT PRO3.ID, PRO4.MDATE
FROM PRO3 INNER JOIN PRO4 ON PRO3.ID = PRO4.ID;

Could anyone be able to identify the problem and if not to suggest and alternative way so as to amend the data of the table "PRO_TBL"?

My problem is that I haven't created the database by myself and I don't want to mess the whole program in a way that it won't be operational or it will need other amendments.

My need is to update the final table "PRO_TBL" through the append query "APPEND_PRO_TBL" combining the two queries "PRO3" and "PRO4"...

Any help will be more that welcomed!

Thanks

 

[Trevor G]

You would need to use an UPDATE query for PRO4 so it would update the fields based on a field you are using in the initial append query PRO3.

My advice is to copy the tables and the queries you want to use in the master database to a temp database where you can practice the append and update queries until you have it working in the way you want, then copy the append and update query back into you master database and you should have a working solution.

 

[xenou]

Seems like if the original problem was size restrictions (?) then you will have the same size restrictions given your solution. Once you join PRO3 and PRO4, you will have the same data, right?

What restrictions are you talking about, anyway?

 

[Micron]

I gave an answer in the other thread, to which you commented in reality there are no tables "PRO3" & "PRO4".
It does not matter that I used the word table when the object in question is a query. A Select query is a form of table, albeit a temporary one. I maintain you should try the suggestion I made there. http://www.mrexcel.com/forum/microsoft-access/975621-message-query-too-complex.html#post4682069

 

[xenou]

Micron, that answer you gave has a syntax error so I'm not sure it will help - not clear what you are saying the problem is, as there is not a missing table reference in the original post.
ξ

Note: or if not a syntax error, then ... something with a cross join (?) ... but still not clear to me anyway.

 

[Micron]

I compared it to a similar query I had, where I noticed that it contained the two table references in the SELECT portion, then in the FROM portion, it referenced both of those tables, separated by a comma. That query works, although the join order is reversed in this post. That's probably not significant. If there's a syntax error, is that evident from you just reading it, or were you able to construct something and try to run it?

INSERT INTO PRO_TBL ( ID, MDATE )
SELECT PRO3.ID, PRO4.MDATE
FROM PRO3, PRO4 INNER JOIN PRO4 ON PRO3.ID = PRO4.ID;

 

[Micron]

BTW, while the design limit of a query or table is 255 fields, IMHO, anything in the area of 100 fields suggests the data is not normalized, which may be the root cause of this issue. I also do not know what can be meant by "size restrictions" other than the number of permitted fields.

 

[xenou]

The query looks incorrect to me. If it works, it's still probably not the right result. Commas in the FROM will create a cartesian join/cross join, which can't be what's needed here.

 

[Pettor]

Hi Guys,

Unfortunately the solution Micron provided doesn't work as I get the message "syntax error". Probably what Trevor says could be the best solution. I will try to update the table with a different query and I will revert.

Thanks!

 

[Micron]

The query looks incorrect to me. If it works, it's still probably not the right result. Commas in the FROM will create a cartesian join/cross join, which can't be what's needed here.

Now that you mention it, there was an unjoined table (whoops, sorry to all).
We still don't know what was meant by size restrictions, no?