Kamis, 31 Mei 2012

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