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

Kamis, 31 Mei 2012

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;