Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Access 2010 Report calculations

    REPORT Data Fields

    City "GroupBy"
    Subtypes SFR CONDOS TWNHS
    Status "A" and "S"

    For each city I obtain the count of status A and S for SFR CONDOS & TWNHS within each city. This I'm able to do in my current report.

    What I'm trying to accomplish now is to divide S by A and show that result in a new field [AR] for each of the three subtypes per city.

    Something like: If SubType="SFR" DIVIDE A by S to equal AR for SFR. AR will be the name of the resultant field.

    I'm going to work on this now.

    Thanks . . . Rick
    Last edited by Rick Schreiber; 09-14-15 at 15:30. Reason: Report Structure

  2. #2
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    I'm trying to get this to work but after 5 hours I can't even come close!

    ([PropertySubType]="CONDO" [AbsorptionRate]=[Status "S" ]/[Status "A"])

    AbsorptionRate is a text box

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Can you transfer this post to the MS Access forum please? Thanks Rick Schreiber

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    " is a reserved symbol, so don't use it in column or table names

    your code infers you have columns called Status "A" and Status "S", is that correct, well clearly its not correct but is that what you mean, or do you mean columns called A & S in a table Status?
    if the latter then you can refer to hose columns IN A QUERY, PROVIDING the table STATUS is ALSO in that query (either as the main table or in a join) as Status.A & Status.S or if you must persist in violating standards and Access own internal reserved symbols/words you can delimit column & table names using square brackets EG [Status].[A] & [Status].[S]. say you had a table called 'my table' and a column called 'my column' then you can refer to that INSIDE A QUERY as [my table].[my column], although you should instead use either CamelCase or underscore to separate elements of you table / column names EG MyTable.MyColumn or my_table.my_column

    as to where that code goes I dont' knwo

    if the table status is in scope (referenced inside the current query beign used in the form/report) then you donjt' need to refer to the tabel inside the form/report.

    I haven't got a clue what
    Code:
    ([PropertySubType]="CONDO" [AbsorptionRate]=[Status "S" ]/[Status "A"])
    is trying to do
    is it a macro?
    if so Im guesing that if the current row's PropertySubType is a CONDO then calculate the absorption rate
    ..you could do that in the base query
    select my, column, list, iif(PropertySubType = 'CONDO', S/A,NULL) as AbsorptionRate
    ..you coudl do that in the form by placing comse code behind the forms on current AND before update AND on Change events

    if PropertySubType.text = 'CONDO' then
    absorptionrate.value = S / A
    else
    absorptionrate.value = NULL
    endif

    ..place that in a function and call tht function form as many events as required
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    healdem . . .

    What is a reserved symbol?

    There is no query . . . this is all in a report which is derived from a table. tblAbsorptionRate

    The fields or column headings are . . .
    [City] GroupBy
    [PropertySubType] CONDO or SFR or TWNHS
    [Status] A, B, P or S
    Each Status has a count [Count Of Status] This part works well.

    The [Absorption Rate] will equal Count of S divided by Count of A

    It seems so simple but I can't make the connection to place the AR in the [Absorption Rate] field.

    Thanks . . . Rick

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    reserved words reserved symbols are words and symbols you shouldn't use in Access
    https://www.google.co.uk/webhp?sourc...ed+symbol+word
    predimonantly they are words or symbolsa used in JET SQL, things like keywords used in SQL (COUNT, SUM, AVG and so on)m the symbols likewise *eg spaces, ;,, qute or speech marks and so on). essentially by NOT usign such words and symbols you reduce the risk the Access and or JET SQL will get confused.

    so if
    The [Absorption Rate] will equal Count of S divided by Count of A
    holds then
    either use a control with the counts already defined OR use a domain funstion (dcount) to get the COUNTs.

    whether you are using a table or query in a form or report, whether you have explictly defined a query or not, when that form or report 'talks' to the database it is using a query. thats how SQL works


    The fields or column headings are . . .
    [City] GroupBy
    [PropertySubType] CONDO or SFR or TWNHS
    [Status] A, B, P or S
    Each Status has a count [Count Of Status] This part works well.

    The [Absorption Rate] will equal Count of S divided by Count of A
    does this mean you are changing the design requirement or just somehtign you forgot to mention last time around
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Report Structure

    So for the City of AV I could calculate the Absorption Rate for CONDOS, SFR and TWNHS if I chose to.

    There are no queries. The report source data is in tblAbsorptionRate

    For Each City Identify the [PropertySubType] obtain the [Count Of Status] for A and S and then divide S/A which is the Absorption Rate.

    I hope I've clarified the problem here.

    Code:
    City:    [PropertySubTyp] [Status]    [Count Of Status]    [Absorption Rate]
    AV       CONDO                A                   103
    AV       CONDO                B                    52
    AV       CONDO                P                    24
    AV       CONDO                S                    61
    AV       SFR                  A                    72
    AV       SFR                  B                    19
    AV       SFR                  P                    10
    AV       SFR                  S                    23
    AV       TWNHS                A                    27
    AV       TWNHS                B                    22
    AV       TWNHS                P                    7
    AV       TWNHS                S                    13
    Thanks . . . Rick

  8. #8
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Now, only two Status' . . . A and S in the query

    Now there are only two Status' hopefully making this easier.

    Any [PropertySubType] within a City with an A or S [Status] is calculated as [CountOfStatus] S divided [CountOfStatus] A . . . I THINK?


    But How. . .

    New Data Structure
    [code]
    City PropertySubType Status CountOfStatus CalculatedAR
    AV CONDO A 103
    AV CONDO S 61
    AV SFR A 72
    AV SFR S 23
    AV TWNHS A 27
    AV TWNHS S 13
    CDM CONDO A 52
    CDM CONDO S 10
    CDM SFR A 109
    CDM SFR S 13
    CDM TWNHS A 7
    DP CONDO A 54
    DP CONDO S 9
    DP SFR A 160
    DP SFR S 33
    DP TIME A 1
    DP TWNHS A 10
    DP TWNHS S 3
    HB COMRES A 2
    HB CONDO A 138
    HB CONDO S 34
    HB MHOL A 1
    HB SFR A 371
    HB SFR S 94
    HB TWNHS A 35
    HB TWNHS S 16

    [/]

    Rick

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    mebbe I'm being overly thick this morning, but I haven't got a clue what is you are actually trying to achieve
    I don't understand the table.
    you are using terms that are related to your business problem that I don;t understand. the requirement seems to be changing or else being drip fed new elements each time. so to be honest I can't be bothered. soory, but to quote a certain Dragons Den cliché.
    I'm out
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •