So you were right. I had the fields with criteria selected. I took the selector off and it works.
But I would like to take the query a little further.
One of the directors is retiring in December. Obviously she is going to have a lot of queries with her name in the criteria. So I thought that I could do a dlookup in the criteria to a table that could manage the names for the Name field of people are reporting to. But that is not working very well. I do not get the same results if I leave the names in the criteria field or put the dlookup in the criteria spot.
I have seen dlookup used before but I must be doing something wrong
Thank You
Jim
Jim Wagner
On Tuesday, October 18, 2016 10:29 AM, "Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Doyce,
The culture here is Excel first. Because they wanted the 60 tables, the link to the spreadsheet to the database was the best I could do for now.
Jim Wagner
On Tuesday, October 18, 2016 9:51 AM, "Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
John,
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"));
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 JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Jim-
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
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 luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> 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, "luvmymelody@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> 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 <luvmymelody@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (8) |
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