Access Projects - Query Designer - Update Queries with Joins cannot display in GUI

[BigJimSlade]

Hi, Big Jim here:

I am really not sure where to ask this one.....

My boss and I are in a jam. We have been using Access to run a reporting process, but one of our tables will exceed the maximum fields allotted this month. Our thought, dump the table into SQL Server and use the GUI interface provided in Access Projects.

Unfortunately, the query designer seems to have a few drawbacks. The one that effects us the most is in using UPDATE queries where more than one table is used to determine records to be updated. In attempting it, we get the message: "The designer does not graphically support the Optional FROM clause SQL construct".

Now I know we can manually create Update Queries, but we often need 1,000+ in a short period of time. Manually punching in all the fields involved and other code just isn't timely.

Question: Is there some alternative, service pack or anything else that would allow us to graphically create these Update Queries using Access Projects or even SQL Server 7.0? I would hate to have to scrap all the work we did over something that seems so minor.

Thanks in advance!

Big Jim


Set Up:
Windows XP
Office XP
SQL Server 7.0

 

[BigJimSlade]

Thank you Pat for the very informative answer. I have to stick up for myself and say that along with MIS degree, I got the 411 on normalization. However, my boss is not normalized on many levels.

Your logic makes sense, and I will share this with my boss and hope we can fix the table structure by breaking it down into a more atomic design (multiple tables).

Thanks for putting in words which I could not.

Big Jim

 

[BigJimSlade]

Pat,

My boss went on for 20 minutes about how you must be a programmer and how he should be able to do anything he wants with Access. (my boss is a financial analyst / auditor)

After a cool down session, he told me to try just linking the SQL Server table into Access and run my updates to the table from Access. If this doesn't work, he said he would give in and rewrite his table.

That was fun. Man I hope I can just get it running in Access so I don't have to go through that again.

Oh and THANK YOU for helping me get back on track with this.

Big Jim