Selasa, 15 November 2016

Re: [MS_AccessPros] Re: Advice on object management



Thank You, Thank You, Thank You. I have been working on this for a week. I added the query to the row source for the combo box and it worked great. I will apply this technique to the other combo boxes with the similar action queries.

Thank You.
Jim Wagner

On Tuesday, November 15, 2016 12:19 AM, "John Viescas [MS_Access_Professionals]" <> wrote:


The first query appears to get all the distinct currently assigned job codes.  The second query gets all the "vacant" job codes whose description does not begin with "stu".  The third query then adds any of these "vacant" job codes that aren't already in the lookup table.

Does this application not have a master valid job code table?  Those three queries appear to be generating a list of all the job codes that have been used in either the assigned or the vacant job codes tables.  You could generate the list in a SELECT query like this:

SELECT [R&D-Vacant Positions STAR].Jobcode
FROM [R&D-Vacant Positions STAR]
WHERE ((([R&D-Vacant Positions STAR].Descr) Not Like "stu*"));

The UNION gets rid of any duplicates.

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 Nov 14, 2016, at 23:44, Jim Wagner [MS_Access_Professionals] <> wrote:


I have been working on this most of the afternoon and I am stuck on how to get the data I need. I have  the following queries below. I know that there is a way to get the data. Just not sure how to do it. Looking at creating a sub query but that gives me weird data set.

1st query






2nd query


SELECT [R&D-Vacant Positions STAR].Jobcode INTO VacantPositionJobCodes

FROM [R&D-Vacant Positions STAR]

GROUP BY [R&D-Vacant Positions STAR].Jobcode, [R&D-Vacant Positions STAR].Descr

HAVING ((([R&D-Vacant Positions STAR].Descr) Not Like "stu*"))

ORDER BY [R&D-Vacant Positions STAR].Descr;


3rd query

INSERT INTO LU_JobCodes ( [Jobcode Id] )

SELECT VacantPositionJobCodes.Jobcode

FROM VacantPositionJobCodes LEFT JOIN LU_JobCodes ON VacantPositionJobCodes.Jobcode = LU_JobCodes.[Jobcode Id]

WHERE (((LU_JobCodes.[Jobcode Id]) Is Null))

ORDER BY VacantPositionJobCodes.Jobcode;

Jim Wagner

On Friday, November 11, 2016 8:58 AM, " [MS_Access_Professionals]" <> wrote:

Hi Jim

Creating a table that is used "later on" is asking for trouble. Unless you check for the table's existence before using it and verify its data you could run into some timing issues.

I only use temp tables in rare occasions. Mostly for complex reports that would otherwise take forever to load. It sounds like you don't really need the ones you described. A saved query should be good enough and you will be certain to have the current data.

Bill Mosca, Founder - MS_Access_Professionals
My nothing-to-do-with-Access blog

---In, <> wrote :

Hello all,

I am looking at a new database that I inherited and I am exploring options to reduce the overhead.

There is a macro that runs every time that the form closes. This is a waste. I moved it to a button. But what I discovered is that the macro behind the button has some objects that I may be able to remove and create and use in other ways.

For example, there is a query that creates a table named LU_Name which is being used a source for a combo box. then later in the macro the LU_Name table is being used in a query.

For my question;
I can use a select statement for the combo box to get the needed data for the combo boxes and use the main table that created the LU_Name to be used in the query later on.

What do the pros suggest is the most efficient in this case? The macro makes about 6 tables and appends some of them with other queries.

Do developers avoid creating objects, or are they a necessary evil?

Thank You
Jim Wagner


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

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