Referring to the data in rows below

[Mike20255]

Column A       Column B    Column C       Column D
1*010101001000000   CF        5
010101002000000   RF
010101002000000   RF
010101029000000   CU
1*010101001000000   CF         10
010101351010000   RF
2*010101354000000   CF          11
2*010101354000000   CF          13
010101358000000   CY 
1*010101001000000   CF         5
010401328100000   CF

NOTE in the actual data [*] won't be there it is just to show you what I am referring to.

Is there any way in another column in a query to say if Column A repeats itself somewhere else in Column A AND if those two that are repeated both have a "CF" code add column C (which is just an amount for each number)

So for example in this data there are 2 numbers that repeat themself that have CF codes. For these I would need to total them in column D for the first number resulting in:

Column A       Column B    Column C       Column D
1*010101001000000   CF        5              20
010101002000000   RF
010101002000000   RF
010101029000000   CU
1*010101001000000   CF         10
010101351010000   RF
2*010101354000000   CF          11             23
2*010101354000000   CF          13
010101358000000   CY 
1*010101001000000   CF         5
010401328100000   CF

Any ideas?

Thanks,
Mike

 

[MStef]

Hello Mike!

Look at "DemoCFA2000.mdb" (attachment).
Open Form1 and try.
Look at Tables, queries, Module1.
I think it is what you need.

 

[Mike20255]

I can't open it, I am using A97

Is there a way of doing this without using a macro?

How would you just say iif row1 column 1 = row 2 column 1 then add the next column?

 

[ajetrumpet]

Is there any way in another column in a query to say if Column A repeats itself somewhere else in Column A AND if those two that are repeated both have a "CF" code add column C (which is just an amount for each number)

Yes there is...

So for example in this data there are 2 numbers that repeat themself that have CF codes. For these I would need to total them in column D

Then write...

SELECT column1, column2, column3, sum(column4) as whatever
FROM table
GROUP BY [column1], [column2], [column3]
HAVING COUNT(*)>1 AND column2 = "CF";

 

[Mike20255]

Where woud I put that in column 4?

 

[ajetrumpet]

That is a query statement Mike.

In column 4?? What do you mean??

 

[Mike20255]

That is a query statement Mike.

In column 4?? What do you mean??

Ha your going to have to bear with me I have no idea what to do with that. I recently discovered how to do this:
Distributed PIL: Sum([city wide]+[cons area]+[police]+[urban fire]+[rural fire]+[urban transit]+[rural transit]+[para transit]+[education$]+[fire supply])

So is that considered a query statement as well? I don't know where to put the code you gave me.

Thanks

 

[ajetrumpet]

I don't know where to put the code you gave me.

It's a QUERY STATEMENT, put it in the SQL view of your query window Bozo.

I recently discovered how to do this: Distributed PIL: Sum([city wide]+[cons area]+[police]+[urban fire]+[rural fire]+[urban transit]+[rural transit]+[para transit]+[education$]+[fire supply])

Good for you...I posted something like that here (which incidently, can probably answer your question ), and THAT'S not really that complicated.

So is that considered a query statement as well?

What?? Yours?? Of course it is, but only part of one.

 

[Mike20255]

There is alot of code in the SQL view already how do I know where to add this to? The Select From and Group By are all there but I can't follow it not sure if the first item in Select has to match the first item in the From and Group by areas how does that work?

 

[ajetrumpet]

would you care to post your database??

The code in post #4 should work fine Mike, but of course you have to substitute your own object names in for the ones I wrote.

How can there be a lot of code in the view?? Querying for what you want does not take a large SQL statement...

 

[Mike20255]

Here is what I am seeing in SQL view:

SELECT [Combined Grantable & Taxable].[Roll No], Sum([Combined Grantable & Taxable].Assessment) AS SumOfAssessment, [Combined Grantable & Taxable].Corporation, [Combined Grantable & Taxable].Class, [2007TaxRates].Code, [City WideR]+[Cons AreaR]+[PoliceR]+IIf([UrbanFire]="Yes",[Urban FireR],0)+IIf([RuralFire]="Yes",[Rural FireR],0)+IIf([UrbanTransit]="Yes",[Urban TransitR],0)+IIf([RuralTransit]="Yes",[Rural TransitR],0)+IIf([ParaTransit]="Yes",[Para TransitR],0)+IIf([Education]="Yes",[EducationR],0)+IIf([FireSupply]=True,[Fire SupplyR],0) AS [2007 Tax Rate], [Assessment]*[City WideR] AS [City Wide], [Assessment]*[Cons AreaR] AS [Cons Area], [Assessment]*[PoliceR] AS Police, IIf([UrbanFire]="YES",[Assessment]*[Urban FireR],0) AS [Urban Fire], IIf([RuralFire]="YES",[Assessment]*[Rural FireR],0) AS [Rural Fire], IIf([UrbanTransit]="YES",[Assessment]*[Urban TransitR],0) AS [Urban Transit], IIf([RuralTransit]="Yes",[Assessment]*[Rural TransitR],0) AS [Rural Transit], IIf([ParaTransit]="Yes",[Assessment]*[Para TransitR],0) AS [Para Transit], IIf([Education]="Yes",[Assessment]*[EducationR],0) AS [Education$], IIf([FireSupply]=True,[Assessment]*[Fire SupplyR],0) AS [Fire Supply], Sum([city wide]+[cons area]+[police]+[urban fire]+[rural fire]+[urban transit]+[rural transit]+[para transit]+[education$]+[fire supply]) AS [Distributed PIL] 
FROM 2007TaxRates INNER JOIN ([Combined Grantable & Taxable] INNER JOIN PILTCodes ON [Combined Grantable & Taxable].Class = PILTCodes.PILTCodes) ON [2007TaxRates].Class = [Combined Grantable & Taxable].Class
GROUP BY [Combined Grantable & Taxable].[Roll No], [Combined Grantable & Taxable].Corporation, [Combined Grantable & Taxable].Class, [2007TaxRates].Code, [City WideR]+[Cons AreaR]+[PoliceR]+IIf([UrbanFire]="Yes",[Urban FireR],0)+IIf([RuralFire]="Yes",[Rural FireR],0)+IIf([UrbanTransit]="Yes",[Urban TransitR],0)+IIf([RuralTransit]="Yes",[Rural TransitR],0)+IIf([ParaTransit]="Yes",[Para TransitR],0)+IIf([Education]="Yes",[EducationR],0)+IIf([FireSupply]=True,[Fire SupplyR],0), [Assessment]*[City WideR], [Assessment]*[Cons AreaR], [Assessment]*[PoliceR], IIf([UrbanFire]="YES",[Assessment]*[Urban FireR],0), IIf([RuralFire]="YES",[Assessment]*[Rural FireR],0), IIf([UrbanTransit]="YES",[Assessment]*[Urban TransitR],0), IIf([RuralTransit]="Yes",[Assessment]*[Rural TransitR],0), IIf([ParaTransit]="Yes",[Assessment]*[Para TransitR],0), IIf([Education]="Yes",[Assessment]*[EducationR],0), IIf([FireSupply]=True,[Assessment]*[Fire SupplyR],0);

Thanks again

 

[ajetrumpet]

I think with all this...

Here is what I am seeing in SQL view:

SELECT [Combined Grantable & Taxable].[Roll No], 
Sum([Combined Grantable & Taxable].Assessment) AS SumOfAssessment, 
[Combined Grantable & Taxable].Corporation, 
[Combined Grantable & Taxable].Class, [2007TaxRates].Code, 

[City WideR]+[Cons AreaR]+[PoliceR]+IIf([UrbanFire]="Yes",[Urban FireR],0)+IIf([RuralFire]="Yes",[Rural FireR],0)+
IIf([UrbanTransit]="Yes",[Urban TransitR],0)+IIf([RuralTransit]="Yes",[Rural TransitR],0)+IIf([ParaTransit]="Yes",[Para TransitR],0)+
IIf([Education]="Yes",[EducationR],0)+IIf([FireSupply]=True,[Fire SupplyR],0) AS [2007 Tax Rate], 

[Assessment]*[City WideR] AS [City Wide], 
[Assessment]*[Cons AreaR] AS [Cons Area], 
[Assessment]*[PoliceR] AS Police, 
IIf([UrbanFire]="YES",[Assessment]*[Urban FireR],0) AS [Urban Fire], 
IIf([RuralFire]="YES",[Assessment]*[Rural FireR],0) AS [Rural Fire], 
IIf([UrbanTransit]="YES",[Assessment]*[Urban TransitR],0) AS [Urban Transit], 
IIf([RuralTransit]="Yes",[Assessment]*[Rural TransitR],0) AS [Rural Transit], 
IIf([ParaTransit]="Yes",[Assessment]*[Para TransitR],0) AS [Para Transit], 
IIf([Education]="Yes",[Assessment]*[EducationR],0) AS [Education$], 
IIf([FireSupply]=True,[Assessment]*[Fire SupplyR],0) AS [Fire Supply], 
Sum([city wide]+[cons area]+[police]+[urban fire]+[rural fire]+[urban transit]+[rural transit]+[para transit]+[education$]+[fire supply]) AS [Distributed PIL] 

FROM 2007TaxRates INNER JOIN ([Combined Grantable & Taxable] INNER JOIN PILTCodes ON 
[Combined Grantable & Taxable].Class = PILTCodes.PILTCodes) ON [2007TaxRates].Class = [Combined Grantable & Taxable].Class

GROUP BY [Combined Grantable & Taxable].[Roll No], [Combined Grantable & Taxable].Corporation, 
[Combined Grantable & Taxable].Class, [2007TaxRates].Code, 

[City WideR]+[Cons AreaR]+[PoliceR]+IIf([UrbanFire]="Yes",[Urban FireR],0)+IIf([RuralFire]="Yes",[Rural FireR],0)+
IIf([UrbanTransit]="Yes",[Urban TransitR],0)+IIf([RuralTransit]="Yes",[Rural TransitR],0)+IIf([ParaTransit]="Yes",[Para TransitR],0)+
IIf([Education]="Yes",[EducationR],0)+IIf([FireSupply]=True,[Fire SupplyR],0), 

[Assessment]*[City WideR], [Assessment]*[Cons AreaR], [Assessment]*[PoliceR], 
IIf([UrbanFire]="YES",[Assessment]*[Urban FireR],0), IIf([RuralFire]="YES",[Assessment]*[Rural FireR],0), 
IIf([UrbanTransit]="YES",[Assessment]*[Urban TransitR],0), IIf([RuralTransit]="Yes",[Assessment]*[Rural TransitR],0), 
IIf([ParaTransit]="Yes",[Assessment]*[Para TransitR],0), IIf([Education]="Yes",[Assessment]*[EducationR],0), 
IIf([FireSupply]=True,[Assessment]*[Fire SupplyR],0);

Thanks again

I'd have to ask this...

1) Is there something wrong with adapting the process of grouping aggregates??

2) Or are you having a difficult time sifting through this nightmare??

None of this...

[City WideR]+[Cons AreaR]+[PoliceR]+IIf([UrbanFire]="Yes",[Urban FireR],0)+IIf([RuralFire]="Yes",[Rural FireR],0)+
IIf([UrbanTransit]="Yes",[Urban TransitR],0)+IIf([RuralTransit]="Yes",[Rural TransitR],0)+IIf([ParaTransit]="Yes",[Para TransitR],0)+
IIf([Education]="Yes",[EducationR],0)+IIf([FireSupply]=True,[Fire SupplyR],0), 

[Assessment]*[City WideR], [Assessment]*[Cons AreaR], [Assessment]*[PoliceR], 
IIf([UrbanFire]="YES",[Assessment]*[Urban FireR],0), IIf([RuralFire]="YES",[Assessment]*[Rural FireR],0), 
IIf([UrbanTransit]="YES",[Assessment]*[Urban TransitR],0), IIf([RuralTransit]="Yes",[Assessment]*[Rural TransitR],0), 
IIf([ParaTransit]="Yes",[Assessment]*[Para TransitR],0), IIf([Education]="Yes",[Assessment]*[EducationR],0), 
IIf([FireSupply]=True,[Assessment]*[Fire SupplyR],0);

needs to be grouped. Every grouping you've put in here is either a function or an expression. Get rid of them...the GROUP BY section should only include your first little block...

GROUP BY [Combined Grantable & Taxable].[Roll No], [Combined Grantable & Taxable].Corporation, 
[Combined Grantable & Taxable].Class, [2007TaxRates].Code

I assume these first 4 fields are the ones being represented by the table in your first post??

 

[Mike20255]

LOL okay well now I can see what is going on a bit better I was trying to do that but had no clue what was going on

I am so lost with this stuff.

Also that code must have all been created by Access because I only used the "Field" part of the query to enter formulas.

 

[Mike20255]

I think with all this...I'd have to ask this...

1) Is there something wrong with adapting the process of grouping aggregates??

2) Or are you having a difficult time sifting through this nightmare??

None of this...

[City WideR]+[Cons AreaR]+[PoliceR]+IIf([UrbanFire]="Yes",[Urban FireR],0)+IIf([RuralFire]="Yes",[Rural FireR],0)+
IIf([UrbanTransit]="Yes",[Urban TransitR],0)+IIf([RuralTransit]="Yes",[Rural TransitR],0)+IIf([ParaTransit]="Yes",[Para TransitR],0)+
IIf([Education]="Yes",[EducationR],0)+IIf([FireSupply]=True,[Fire SupplyR],0), 

[Assessment]*[City WideR], [Assessment]*[Cons AreaR], [Assessment]*[PoliceR], 
IIf([UrbanFire]="YES",[Assessment]*[Urban FireR],0), IIf([RuralFire]="YES",[Assessment]*[Rural FireR],0), 
IIf([UrbanTransit]="YES",[Assessment]*[Urban TransitR],0), IIf([RuralTransit]="Yes",[Assessment]*[Rural TransitR],0), 
IIf([ParaTransit]="Yes",[Assessment]*[Para TransitR],0), IIf([Education]="Yes",[Assessment]*[EducationR],0), 
IIf([FireSupply]=True,[Assessment]*[Fire SupplyR],0);

needs to be grouped. Every grouping you've put in here is either a function or an expression. Get rid of them...the GROUP BY section should only include your first little block...

GROUP BY [Combined Grantable & Taxable].[Roll No], [Combined Grantable & Taxable].Corporation, 
[Combined Grantable & Taxable].Class, [2007TaxRates].Code

I assume these first 4 fields are the ones being represented by the table in your first post??

No those four fields aren't teh ones it is [Roll No] [Assessment] [Class] and the fourth one was the sum of assessment if the roll no and class matches.

In regards to the first bit of code you put there if I remove it, I get an error?

 

[ajetrumpet]

You said you are using Access '97. I'm on 2007.

When you add that block and you DON'T get the error message, what is not working?? Not getting the sum field??

A couple more things...
1) this will be easier if I can see your file.
2) this will be easier if you split the task up into multiple queries (I'm guessing)

 

[MStef]

Here it is "DemoCBA97.mdb".
Open Form1 and try.