[AccessDevelopers] Re: Unable to figure out query

 

I would probably store the 7 character values in a table and use the "*" for matching.

Minimally you should learn how to use a shorter notation like:

HAVING LEFT(dbo_APPROPRIATION.APPROPRIATION_UNIT,7) IN ("UJ63006", "UJ63007", "UJ64000", "PL61636", "PL62953", "PL6300*", "PL63012" , "PL63015", "PL63017", "PL63032", "PL63057", "PL64036", "PL64143", "PL64144", "PL63052", "PM64005", "PL64111", "PL64025", "PL64101", "PL64103") AND

This should also be moved from the HAVING clause to the WHERE clause to be more efficient.

Duane Hookom
MS Access MVP

--- In AccessDevelopers@yahoogroups.com, "luvmymelody" <luvmymelody@...> wrote:
>
> Hello All,
>
> We have a query that we have Account codes listed as the criteria in the query. Yet we were told recently that the Account codes change all the time for a project and then they go away. This has been now realized that the maintenance of the query has become just plain stupid. So I suggested that we use a table or query to use as the criteria for the account codes. This way they would only have to manage the table.
> We do have an Account table that I use to filter out reports for the reports that are emailed out on a regular basis. So I thought that I could use that table. But the problem is that the dbo table does not have the Account field in the same way that we need it. I will paste the sql below. The dbo table has the Account code like UJ6400A or UJ6400C and we want to see all of the UJ6400* as you will see in the query. They also are in the dbo table as broken apart so I started to create a query with the fields concatenated but thought that I should ask experts before I go down a road that I do not want to double back on. Below is the query.
>
> Thank You so much
>
> Jim Wagner
>
> INSERT INTO BUDGET_FINANCING ( ACCOUNT, ACCOUNT_CODE, ORIG_BUDGET, ADJUSTED_BUDGET, CURRENT_MONTH_ACTUAL )
> SELECT dbo_APPROPRIATION.APPROPRIATION_UNIT, dbo_APPROPRIATION.APPROPRIATION_UNIT_CODE, dbo_APPROPRIATION.[APPR_APPROVED_AMOUNT_$], dbo_APPROPRIATION.[APPR_CURRENT_MODIFIED_AMOUNT_$], dbo_APPROPRIATION.[APPR_ENCUMBERED_AMOUNT_$]
> FROM BUDGET_YTD_LABEL INNER JOIN dbo_APPROPRIATION ON BUDGET_YTD_LABEL.APPROPRIATION_UNIT_CODE = dbo_APPROPRIATION.APPROPRIATION_UNIT_CODE
> GROUP BY dbo_APPROPRIATION.APPROPRIATION_UNIT, dbo_APPROPRIATION.APPROPRIATION_UNIT_CODE, dbo_APPROPRIATION.[APPR_APPROVED_AMOUNT_$], dbo_APPROPRIATION.[APPR_CURRENT_MODIFIED_AMOUNT_$], dbo_APPROPRIATION.[APPR_ENCUMBERED_AMOUNT_$], dbo_APPROPRIATION.BUDGET_YEAR
> HAVING (((dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "UJ63006*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "UJ63007*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "UJ64000*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL61636*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL62953*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63009*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63012*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63015*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63017*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63032*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63057*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64036*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64143*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64144*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63052*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PM64005*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64111*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64025*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64101*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64103*") AND ((dbo_APPROPRIATION.APPROPRIATION_UNIT_CODE) Like "s") AND ((dbo_APPROPRIATION.BUDGET_YEAR) Like "MYMY")) OR (((dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "UJ63006*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "UJ63007*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "UJ64000*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL61636*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL62953*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63009*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63012*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63015*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63017*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63032*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63057*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64036*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64143*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64144*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63052*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PM64005*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64111*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64025*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64101*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64103*") AND ((dbo_APPROPRIATION.APPROPRIATION_UNIT_CODE) Like "b") AND ((dbo_APPROPRIATION.BUDGET_YEAR) Like "MYMY")) OR (((dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "UJ63006*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "UJ63007*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "UJ64000*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL61636*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL62953*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63009*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63012*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63015*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63017*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63032*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63057*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64036*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64143*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64144*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63052*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PM64005*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64111*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64025*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64101*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64103*") AND ((dbo_APPROPRIATION.APPROPRIATION_UNIT_CODE) Like "i") AND ((dbo_APPROPRIATION.BUDGET_YEAR) Like "MYMY")) OR (((dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "UJ63006*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "UJ63007*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "UJ64000*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL61636*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL62953*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63009*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63012*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63015*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63017*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63032*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63057*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64036*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64143*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64144*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL63052*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PM64005*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64111*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64025*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64101*" Or (dbo_APPROPRIATION.APPROPRIATION_UNIT) Like "PL64103*") AND ((dbo_APPROPRIATION.APPROPRIATION_UNIT_CODE) Like "c") AND ((dbo_APPROPRIATION.BUDGET_YEAR) Like "MYMY"))
> ORDER BY dbo_APPROPRIATION.APPROPRIATION_UNIT;
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (2)

Recent Activity:

• New Members 5

Visit Your Group

Please zip all files prior to uploading to Files section.

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___