calculating values by grouping

[batman1056]

backgound:-
I have a report that lists people from different areas i.e (field called : district)
Newcastle
Sunderland

In each line of data it will show if that person has for example been to event A,B or C (yes/No) Fields called (A,B,C)

Is it possible to include a formula at the end of this line to check the area then to check the A,B,C and produce a value. The values of ABC are depended on the area

Example
Costs Newcastle
A=£100
B=£150
C=£78
Costs Sunderland
A=£85
B=£132
C=£83

So...
If J.Bown from sunderland went to A and C the total at the end would be £168
if K Brown from Newcastle went to C the total would be £78

Not sure how to do this - in excel I would use an if statement but in access not sure how to reference these

=if (A1="newcastle",if A=Yes,100,0)+if (A1="newcastle",if B=yes,150,0)+if (A1="newcastle",if C=yes,78,0), if (A1="sunderland" etc.....

I know the above forumla is not correct but you get the idea what I am trying to do

 

[ajetrumpet]

Basically, you will either use an IIF function in the query, if the report is based on a query, or an IF, THEN statement with a report event.

Where is the cost data stored in the database? Apparently, there are lines on the report that represent records, and all the information except the cost data is present in those records?

In relation to spreadsheets, the function in Access to do this with is probably like the SUMIF function in Excel.

 

[batman1056]

great I will try this later and post any problems with calculating it
Thanks

 

[batman1056]

HELP

The key report names as are follows
[district] is the main place
[application form] = A (yes/no field)
[startup] = B (yes/no field)
[post application] = C (yes/no field)

I created a text box and created the following formula on the line
if([district]="newcastle",if [application form]="YES",100,0)+if ([district]="newcastle",if [startup]="YES",150,0)+if ([district]="newcastle",if [post application]="YES",78,0)+([district]="sunderland",if [application form]="YES",85,0)+if ([district]="newcastle",if [startup]="YES",132,0)+if ([district]="newcastle",if [post application]="YES",83,0

This does not work -- ermmm Help!

BTW - the report is created from a table

I could move it to a query if this is easier

Thanks

 

[ajetrumpet]

I guess I need to ask you what you are trying to get out of this...

if([district]="newcastle",if [application form]="YES",100,0)+

if ([district]="newcastle",if [startup]="YES",150,0)+

if ([district]="newcastle",if [post application]="YES",78,0)+

([district]="sunderland",if [application form]="YES",85,0)+i

f ([district]="newcastle",if [startup]="YES",132,0)+

if ([district]="newcastle",if [post application]="YES",83,0

Another thing, typing an expression into the control's LINE (typing directly into the control while in design view) will produce a controlsource. So, make sure the expression you are typing in is valid for a controlsource. E.G. - An IIF statement is not a valid control source, and it will not work.

So, what (in words) are you wanting with the above expression, and are you trying to write an IF, THEN conditional statement, or a simple IIF statement???

 

[batman1056]

I have a report which lists clients I have supported and I get paid per client I have supported and depending on which area the client lives I get paid a different value (i.e. Newcastle or Sunderland)

My report (from a table)
Shows a fulls list of client names with district and yes/no statements for A,B,C
If they have a tick in A I get paid X
If they have a tick in B I get paid Y
If they ahve a tick in C I get paid Z
If they have multipul ticks i.e. A and B I get the total for A and B
These values are depended on which area they live

So in theory the total on each line will check the area, then check A,B,C add them up and give me a value per person

Values from the table (fields are)
[district] is the main place
[application form] = A (yes/no field)
[startup] = B (yes/no field)
[post application] = C (yes/no field)

Costs assocaited with each item
Costs Newcastle (district)
A=£100
B=£150
C=£78
Costs Sunderland (district)
A=£85
B=£132
C=£83

 

[ajetrumpet]

I would base your report off of a query, instead of a table.

The bottom line is that you have to check for...

1) the set of rates for A, B, & C that are applicable depending on where the client is from.

Once this is done, then add those rates for A, B, or C with an IIF statement.

I assume these values can be looked up in tables?? To get the set of values you need for the three tick marks, you will need a DLookup function.

A screenshot of the report's table and of the report itself would help to.

 

[batman1056]

ok.. this is the report layout - please note that the names have been changed and also the tick box's do not appear on each line - hopefully you will be able to understand what I am doing


for the values of A,B,C I can create a table with these in - this is not a problem

so If I created a table [tbl_costs]
with the fields
district
A
B
C

and then put in
Sunderland and the values
still not sure how to get the queiry to look at the fields and pull the values
In my Quiery I will have
[tbl_claims] and [tbl_costs]

in tbl_claims I will have the following fields
name, district, Application (A), Start form (B) and post start (C)

ideally it should look at the district, then the A,B and C Yes/No fields and add them up (value depended on the district)



Sorry

 

[ajetrumpet]

I meant to ask you this last time, when we hashed through that other thread of yours...what's with the name? Like the movies? I personally liked the series, but I thought it went downhill after Keaton left. When they lost Burton's imagination, I was kinda sad too.

Anyway, about the problem: Here's what I would do...

**Create a lookup table for the different districts and the money associated with each of the three events (tblcosts). So, a structure like...

District A B C
NewCastle $$ $$ $$
Other $$ $$ $$

**Then, query the table that your report is based off of now, and add an extra field to it.
**The extra field should be an expression with IIF statements in it. Something like this...

(IIF([A] = -1, DLookup("[A]", "tblcosts", "tblcosts.district = [district]), 0) + 

    IIF([B] = -1, DLookup("[B]", "tblcosts", "tblcosts.district = [district]), 0) + 

  IIF([C] = -1, DLookup("[C]", "tblcosts", "tblcosts.district = [district]), 0)) 

As [TotalCostsAssociatedWithClient]

If you get that, all you have to do then is create an auto report from the query.

 

[batman1056]

Thanks - working on your solution and will post it how I get on.
- Re - name - I took it years ago after the first film, like the dark appraoch of the film, reminds me of the dark Knight series in the graphic novels.

Quick question not sure if this is an easy answer or hard answer

In my Forms, I record a tick against a customer if they have done A,B or C and they I am paid depending on A,B,C
now against each ABC I had a date box, which shows me which month that customer accessed ABC, they might have done A in Aug, B in Sept, and no C etc..

I have used 3 append queires which check for my date and add that client data to a new table (which is where I am running this report from)

Is their an easier way of doing this?

 

[batman1056]

(IIF([Application From] = -1, DLookup("[A]","tblcosts","tblcosts.district = [district]),0) + IIF([Startup Declaration] = -1,DLookup("[B]","tblcosts", "tblcosts.district = [district]), 0) + IIF([Post Start] = -1, DLookup("[C]", "tblcosts", "tblcosts.district = [district]), 0))As [TotalCostsAssociatedWithClient]

The query takes the data from the table
tbl_claims

In this table I have
Application From (i know spelt it wrong, this is A)
Startup Declaration (this is B)
Post Start (this is C)

tblcosts I have
district
A
B
C

Example data
District New
A £11.20
B £33.00
C £44.00
District Sun
A £33.00
B £20.00
C £11.34

This code I think, should look a the field from this table called application from and if its -1 (i.e. YES) then it it lookups the field A in the tblcosts, then it looks at the same table and checks that the district line is equal to the one from the tbl_claims for this person, if it is it takes the value then it goes onto the next value. -- tried it and it does not work

So in theory, if I get this right, when I put the above code in the query in the Criteria section of a new expression field, I tried it but I get a "invalid syntex".

Thanks in advance

 

[ajetrumpet]

Quick question not sure if this is an easy answer or hard answer

In my Forms, I record a tick against a customer if they have done A,B or C and they I am paid depending on A,B,C
now against each ABC I had a date box, which shows me which month that customer accessed ABC, they might have done A in Aug, B in Sept, and no C etc..

I have used 3 append queires which check for my date and add that client data to a new table (which is where I am running this report from)

Is their an easier way of doing this?

I think the last time you posted a question about this DB, I was the one that suggested the append queries. Remember that thread? I remember the append queries, and since I usually think before typing anything, I would say that there probably isn't a better way, because I helped set those up initially.

The query takes the data from the table
tbl_claims

In this table I have
Application From (i know spelt it wrong, this is A)
Startup Declaration (this is B)
Post Start (this is C)

tblcosts I have
district
A
B
C

Example data
District New
A £11.20
B £33.00
C £44.00
District Sun
A £33.00
B £20.00
C £11.34

Are you pulling from tblclaims and tblcosts?

**You should only be pulling from tbl costs.

**The multiple IIF expression statement should not be in a WHERE clause (or, for that matter, in a criteria section of anything)
**I was under the impression that [Application From] = A, [Startup] = B, and [Post] = C

**I notice in your query you have the Report Field Label NAMES in the [ ] (brackets). The field names of the table are supposed to be in brackets. I thought the field names of the table from which you are pulling costs from are A, B, and C??? If they are, then those letters need to be in the [ ] instead.

**I have no idea why you are getting the syntax error. The IIF statement you wrote looks just fine. If there is other SQL in the query, the problem is probably part of that instead. What exactly does the error message say? I don't think I've ever seen an error message like "syntax error". Usually, it will read, "Syntax Error in query expression "(portion of the SQL statement where the error is found)"

I tried to copy what you're trying to do here. It's in the attachment. I got the DB from the last post I helped you with. Look at NEWQUERY and NEWREPORT. I also added the Costs Table.

 

[batman1056]

Genious!

Fantastic - now I know where I went wrong - put the expression in the wrong place, I used your layout - many many thanks

This worked exactly how I wanted. - BUT

it does not distinguish between the two areas - in your database - go into the queiry - and tick newcastle and sunderland application forms only - you get the same costs., 90% done!

As for the second issue on creating the append tables, I am still working on de-duping the data as I get lots of duplicates, and people may run the same date twice!

Many thanks again - this is excellent.

 

[ajetrumpet]

many many thanks

Welcome

it does not distinguish between the two areas - in your database - go into the queiry - and tick newcastle and sunderland application forms only - you get the same costs.

I noticed...

I have played around with it for a while, and I can't figure out what is going on. The syntax is correct. The query is ignoring the WHERE portion of the DLookup, and it is pulling the first value from the column with each IIF statement. I checked the syntax in my databases and they are the same, but they work correctly. I even tried copying the objects into a new database file, and that didn't work. I don't have an answer for this. It seems strange, corruption maybe? I will look at it later on, and if I find anything I will let you know.

 

[batman1056]

thanks

IF you can get this working on a different database, can you post that here,
I will then utilise your database, and import all my data and recreate the forms, and drop in the reports.

Should only take a few hours to get it up and running but at least this report will run

tba thanks