Kamis, 31 Mei 2012

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;






__._,_.___
Recent Activity:

Please zip all files prior to uploading to Files section.
.

__,_._,___

Tidak ada komentar:

Posting Komentar