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.

1^st 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];

 

2^nd 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;

 

3^rd 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.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___