Access Projects - Query Designer - Update Queries with Joins cannot display in GUI (1 Viewer)

[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

 

[Pat Hartman]

but one of our tables will exceed the maximum fields allotted this month.

Rather than just patching this, why not actually fix the problem? The table is not normalized. Relational tables NEVER grow horizontally. They only grow vertically. You have created a spreadsheet and simply stored it in a table. Do some reading on normalization and then we'll help you get going.

I don't recomment converting to an .adp for a lot of reasons. The conversion to SQL for the problem table won't solve the problem because I'm guessing that Access will still not allow a query to contain more than 255 columns. Since I have no unnormalized tables, I don't have any way to test this theory so you'll have to test it. Create the table in an SQL database, add the additional columns, link it to your Access .mdb and see what happens.

If the error you are talking about is coming from the .adp, just chalk it up to yet another reason to NOT use .adp's.

 

[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

 

[Pat Hartman]

Clearly your boss doesn't understand the difference between a table and a spreadsheet and it sounds like he doesn't want to. Does he know the difference between a boat and a train? They're both modes of transportation so why can't the train travel on water?

Poor you having to work for him.