Senin, 14 November 2016

Re: [MS_AccessPros] Re: Advice on object management

 

Bill,

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

SELECT [R&D-CURRENTEMPLOYEES].[Jobcode Id], [R&D-CURRENTEMPLOYEES].[Jobcode Ld] INTO LU_JobCodes

FROM [R&D-CURRENTEMPLOYEES]

GROUP BY [R&D-CURRENTEMPLOYEES].[Jobcode Id], [R&D-CURRENTEMPLOYEES].[Jobcode Ld]

ORDER BY [R&D-CURRENTEMPLOYEES].[Jobcode Id];

 

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, "wrmosca@comcast.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> 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.

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com



---In MS_Access_Professionals@yahoogroups.com, <luvmymelody@yahoo.com> 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 <luvmymelody@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (3)

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