Using Left and Right in an Expression

[amanda.redetzke]

I know I can do this by using the left and right in access but I can't
get the expression correct.

I have various activities in my table that I need to roll up into one
activity

10120101 -- main activity
10120496 -- cost share activity of main activity
10129100 -- admin activity of main activity

I would like to create a new activity that will roll all of those up
into 10120101.

I would need the expression to be generic b/c all my activities are set
up this way such as 1013, 1014 etc.

I should be able to do that with the left and right functions but I
cannot seem to get the express correct.

Please help!

Amanda

 

[John Vinson]

I know I can do this by using the left and right in access but I can't
get the expression correct.

I have various activities in my table that I need to roll up into one
activity

10120101 -- main activity
10120496 -- cost share activity of main activity
10129100 -- admin activity of main activity

I would like to create a new activity that will roll all of those up
into 10120101.

I would need the expression to be generic b/c all my activities are set
up this way such as 1013, 1014 etc.

I should be able to do that with the left and right functions but I
cannot seem to get the express correct.

Please help!

Amanda

Could you explain what you mean by "roll up"? What do Left and Right
have to do with it?

And more critically why are you apparently violating the basic
principle that fields should be "atomic" - contain only one value? It
sounds like you need a completely different table structure such as:

ActivityID
ActivityName
ParentActivity

with records like
10120101; "Main activity"; NULL
10120496; "Cost share activity of main activity"; 10120101
10129100; "Admin activity of main activity; 10120101


John W. Vinson[MVP]

 

[amanda.redetzke]

That is just the way the database is set up. I have no control over
that.

What I mean by roll up is that I want any costs that are under the
10120496 or 10129100 be added to the 10120101.

I thought maybe if i did a left function that looked at the 96 and
added to the 0101 or something it would work.

I really don't have a clue how to do it but I thought maybe someone
here could give me an idea.

Also thanks for the date earlier that worked great.

 

[amanda.redetzke]

That is just the way the database is set up. I have no control over
that.

What I mean by roll up is that I want any costs that are under the
10120496 or 10129100 be added to the 10120101.

I thought maybe if i did a left function that looked at the 96 and
added to the 0101 or something it would work.

I really don't have a clue how to do it but I thought maybe someone
here could give me an idea.

Also thanks for the date earlier that worked great.

 

[John Vinson]

That is just the way the database is set up. I have no control over
that.

What I mean by roll up is that I want any costs that are under the
10120496 or 10129100 be added to the 10120101.

I thought maybe if i did a left function that looked at the 96 and
added to the 0101 or something it would work.

I don't understand the logic. What do you mean by "looked at the 96
and added to the 101"? If I (as a human being) look at the numbers
10120496 and 10129100, I would not have any inkling that they had
anything to do with one another.

I really don't have a clue how to do it but I thought maybe someone
here could give me an idea.

Not without some understanding of the logic of your data... which I
haven't seen posted here.

Also thanks for the date earlier that worked great.

Good thing my wife doesn't frequent this newsgroup... ! ! ! <g>

(seriously, glad to have been of assistance).

John W. Vinson[MVP]

 

[amanda.redetzke]

Okay what I really want to do is replace the activities with 10120101

activity: Replace([budget
query--pourquie!activity2],"10120496","10120101")

I used that above expression and it worked great...however I want to do
this for 3 other activities how do I get this to work. I thought I
could just put and between each new replace but it doesn't work.

 

[John Vinson]

Okay what I really want to do is replace the activities with 10120101

activity: Replace([budget
query--pourquie!activity2],"10120496","10120101")

Ok. Fine; I answered a different question, the one you asked. Sorry my
telepathy was ineffective.

I used that above expression and it worked great...however I want to do
this for 3 other activities how do I get this to work. I thought I
could just put and between each new replace but it doesn't work.

There is a syntax error above; you have the tablename and fieldname
enclosed in one set of brackets, whereas each should be in ITS OWN
brackets. And if it's an Update query you're running, you don't want
the alias fieldname.

Could you post the actual SQL of your query, and explain what you are
*ACTUALLY* trying to do, without the obfuscation?

John W. Vinson[MVP]

 

[amanda.redetzke]

SELECT [budget query--pourquie].[accounting Unit], [budget
query--pourquie].DEPT, [budget query--pourquie].activity2,
Replace([budget query--pourquie!activity2],"10120496","10120101") AS
activity, [budget query--pourquie].Name, [budget
query--pourquie].CMonth, [budget query--pourquie].CDate, Sum([budget
query--pourquie].[Salary Budget]) AS [SumOfSalary Budget], Sum([budget
query--pourquie].[Travel budget]) AS [SumOfTravel budget], Sum([budget
query--pourquie].[Operations Budget]) AS [SumOfOperations Budget],
Sum([budget query--pourquie].[Core Budget]) AS [SumOfCore Budget],
Sum([budget query--pourquie].[Total Discretionary]) AS [SumOfTotal
Discretionary], Sum([budget query--pourquie].[Total Budget]) AS
[SumOfTotal Budget]
FROM [budget query--pourquie]
GROUP BY [budget query--pourquie].[accounting Unit], [budget
query--pourquie].DEPT, [budget query--pourquie].activity2,
Replace([budget query--pourquie!activity2],"10120496","10120101"),
[budget query--pourquie].Name, [budget query--pourquie].CMonth, [budget
query--pourquie].CDate;

Above is the SQL to my query.

Okay what I really want to do is replace the activities with 10120101

activity: Replace([budget
query--pourquie!activity2],"10120496","10120101")

Ok. Fine; I answered a different question, the one you asked. Sorry my
telepathy was ineffective.

I used that above expression and it worked great...however I want to do
this for 3 other activities how do I get this to work. I thought I
could just put and between each new replace but it doesn't work.

There is a syntax error above; you have the tablename and fieldname
enclosed in one set of brackets, whereas each should be in ITS OWN
brackets. And if it's an Update query you're running, you don't want
the alias fieldname.

Could you post the actual SQL of your query, and explain what you are
*ACTUALLY* trying to do, without the obfuscation?

John W. Vinson[MVP]

 

[John Vinson]

SELECT [budget query--pourquie].[accounting Unit], [budget
query--pourquie].DEPT, [budget query--pourquie].activity2,
Replace([budget query--pourquie!activity2],"10120496","10120101") AS
activity, [budget query--pourquie].Name, [budget
query--pourquie].CMonth, [budget query--pourquie].CDate, Sum([budget
query--pourquie].[Salary Budget]) AS [SumOfSalary Budget], Sum([budget
query--pourquie].[Travel budget]) AS [SumOfTravel budget], Sum([budget
query--pourquie].[Operations Budget]) AS [SumOfOperations Budget],
Sum([budget query--pourquie].[Core Budget]) AS [SumOfCore Budget],
Sum([budget query--pourquie].[Total Discretionary]) AS [SumOfTotal
Discretionary], Sum([budget query--pourquie].[Total Budget]) AS
[SumOfTotal Budget]
FROM [budget query--pourquie]
GROUP BY [budget query--pourquie].[accounting Unit], [budget
query--pourquie].DEPT, [budget query--pourquie].activity2,
Replace([budget query--pourquie!activity2],"10120496","10120101"),
[budget query--pourquie].Name, [budget query--pourquie].CMonth, [budget
query--pourquie].CDate;

Above is the SQL to my query.

I think you'll need to use multiple nested Replace() calls to do this
- and it would become a real pain to manage!

Perhaps you could add an auxiliary table Activities to define the
desired rollups. This could have fields Activity and ParentActivity
with values like:

10120101 10120101
10120496 10120101
10120512 10120101

with all your activities in the first field and their corresponding
"major activity" in the right. You could then use

DLookUp("[ParentActivity]", "[Actifities]", "[Activity] = " &
Left([Activity2], 8)

to isolate the parent activity for any record in the table.

John W. Vinson[MVP]