ACCESS FEED: RE: [AccessDevelopers] Unable to figure out query

Jim,
Are you using "GROUP BY" to remove duplicates?

You are still using "Like" without wildcards which can only decrease your performance. This SQL would be more efficient and easier to maintain (blank lines added for readability):

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 (qryConcatenatedAccountCodes INNER JOIN ACCOUNTS ON qryConcatenatedAccountCodes.Account = ACCOUNTS.ACCOUNT) INNER JOIN (BUDGET_YTD_LABEL INNER JOIN dbo_APPROPRIATION ON BUDGET_YTD_LABEL.APPROPRIATION_UNIT_CODE = dbo_APPROPRIATION.APPROPRIATION_UNIT_CODE) ON qryConcatenatedAccountCodes.FullAccount = dbo_APPROPRIATION.APPROPRIATION_UNIT

WHERE dbo_APPROPRIATION.APPROPRIATION_UNIT_CODE IN ("s","b","i","c") AND
dbo_APPROPRIATION.BUDGET_YEAR = "MYMY"

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

ORDER BY dbo_APPROPRIATION.APPROPRIATION_UNIT;

The final step I would take is to place "s","b","i","c" into a table for ease of maintenance.

Duane Hookom
MS Access MVP

To: AccessDevelopers@yahoogroups.com
From: luvmymelody@yahoo.com
Date: Thu, 31 May 2012 14:08:21 -0700
Subject: Re: [AccessDevelopers] Unable to figure out query

I created a query with the concatenated fields without the last character put together from the dbo table and then added the accounts table and the new query in the original query and I get a great data set that works. Thank you for the idea

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 (qryConcatenatedAccountCodes INNER JOIN ACCOUNTS ON qryConcatenatedAccountCodes.Account = ACCOUNTS.ACCOUNT) INNER JOIN (BUDGET_YTD_LABEL INNER JOIN dbo_APPROPRIATION ON BUDGET_YTD_LABEL.APPROPRIATION_UNIT_CODE = dbo_APPROPRIATION.APPROPRIATION_UNIT_CODE) ON qryConcatenatedAccountCodes.FullAccount = dbo_APPROPRIATION.APPROPRIATION_UNIT

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_CODE) Like "s") AND ((dbo_APPROPRIATION.BUDGET_YEAR) Like "MYMY")) OR (((dbo_APPROPRIATION.APPROPRIATION_UNIT_CODE) Like "b") AND ((dbo_APPROPRIATION.BUDGET_YEAR) Like "MYMY")) OR (((dbo_APPROPRIATION.APPROPRIATION_UNIT_CODE) Like "i") AND ((dbo_APPROPRIATION.BUDGET_YEAR) Like "MYMY")) OR (((dbo_APPROPRIATION.APPROPRIATION_UNIT_CODE) Like "c") AND ((dbo_APPROPRIATION.BUDGET_YEAR) Like "MYMY"))

ORDER BY dbo_APPROPRIATION.APPROPRIATION_UNIT;

Jim Wagner

From: David Shannon <dshannon1351@yahoo.com>
To: "AccessDevelopers@yahoogroups.com" <AccessDevelopers@yahoogroups.com>
Sent: Thursday, May 31, 2012 1:58 PM
Subject: Re: [AccessDevelopers] Unable to figure out query

It seems to me you can continue to use your same approach. Assuming you create a table (e.g. ACCOUNTS_LIST) into which you will enter all the account prefixes (i.e. UJ63006, UJ63007, UJ64000, etc.), then you should be able to modify your SQL something like this.

WHERE (((  dbo_APPROPRIATION.APPROPRIATION_UNIT ) Like [ACCOUNTS_LIST].[ACCOUNT_NUM] & "*"))

One benefit of this approach is, you are not limited to six character account prefixes. It will work regardless of the length.

    From: Toby Bierly <toby@stahlbush.com>
 To: "AccessDevelopers@yahoogroups.com" <AccessDevelopers@yahoogroups.com> 
 Sent: Thursday, May 31, 2012 3:45 PM
 Subject: RE: [AccessDevelopers] Unable to figure out query

If you   have an table that list all the accounts that you want to include in this query   that includes the last character (A or C or whatever), then you should be able   to do something like this:

HAVING(((dbo.APPROPRIATION.APPROPRIATION_UNIT) In   (SELECT app_unit_field FROM table_with_accounts) ...

If the table just has the Account codes without   the last character, if the Account codes are always 6 characters (7 if you   included the last character, 6 without), then:

HAVING(((LEFT(dbo.APPROPRIATION.APPROPRIATION_UNIT,6))   In (SELECT app_unit_field FROM table_with_accounts)   ...

Hope   that helps,
Toby

-----Original Message-----
From:     AccessDevelopers@yahoogroups.com [mailto:AccessDevelopers@yahoogroups.com]     On Behalf Of luvmymelody
Sent: Thursday, May 31, 2012 12:04     PM
To: AccessDevelopers@yahoogroups.com
Subject:     [AccessDevelopers] Unable to figure out query

 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.APPROPRIAT ION_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 ( d     bo_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) Lik e "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_APPROPR I     ATION.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 "PL6 3 012*" 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. B UDGET_YEAR) Like "MYMY"))
ORDER BY     dbo_APPROPRIATION.APPROPRIATION_UNIT;