Selasa, 18 Oktober 2016

Re: [MS_AccessPros] Create Multiple tables and manage them



She added the two fields and when I ran the macro the first query came up with a prompt for F25 and F26. That is why I added the two fields. But when I added the Select * to the query it failed with an error of cannot define field more than once. the new sql is below. I changed the names to xxxxx to protect privacy.

SELECT [FDM Directory].*, [FDM Directory].Name, [FDM Directory].[Dept Id], [FDM Directory].[Sub-dept#] INTO [D0801-Contract Administration/Classroom Maintenance & Support]
FROM [FDM Directory]
WHERE ((([FDM Directory].[Dept Id])="D0801") AND (([FDM Directory].[Sub-dept#])="Contract Administration/Classroom Maintenance & Support")) OR ((([FDM Directory].Name)="xxxxxxx" Or ([FDM Directory].Name)="xxxxxxx" Or ([FDM Directory].Name)="xxxxxx"));

 I failed to mention that the query is a make table query.

Jim Wagner

On Tuesday, October 18, 2016 9:28 AM, "John Viescas [MS_Access_Professionals]" <> wrote:


Adding fields shouldn't have mattered - as long as the specific fields requested by the queries still existed.  It sounds like the manager renamed or deleted some fields, and that for sure would screw things up.  SELECT * will always return all the current fields, but if you have code or a report or form that depends on certain fields to be there, those will fail if expected fields have been removed or renamed.

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications

On Oct 18, 2016, at 18:05, Jim Wagner [MS_Access_Professionals] <> wrote:

I think I answered my question. I need to change the sql to  Select * and then the criteria fields. Would that be a good way?

Jim Wagner

On Tuesday, October 18, 2016 8:44 AM, " [MS_Access_Professionals]" <> wrote:

Hello all,

We maintain an employee spreadsheet that has all of the Reports to for the division. We use that as the source for Visio org chart. I talked the management in to using Access to do some of the heavy lifting. They wanted to create individual visio pages a certain way by sub departments. So I created a database and 60 queries that make the tables to be the source for the visio files from the linked spreadsheet. So because the project is in the infancy stage, I have come across a big issue.

The manager added two fields to the spreadsheet and therefore all 60 queries when run in the macro gives errors of missing fields.

My question is how would I create the tables and manage the tables with the certain criteria for each query and if a new field or two is added to the spreadsheet, update the 60 queries.

I thought of moving the macro to vba and do a replace all for the missing fields in vba. But that sounds kind of clunky and not very efficient. Unless the group confirms it is the best idea.

I also thought of somehow creating a table with the criteria for each table in a criteria table and using that to assist in the table creation.

What are the groups thoughts.

Thank You Very Much.

Jim Wagner


Posted by: Jim Wagner <>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (6)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.



Tidak ada komentar:

Posting Komentar