DSUM Error

[NeoFax]

I am trying to create a query that appends records to a table based on
the remaining percent to 100%. I have records that have the percent
build complete on the last day of each month by station and
workcenter. I want to DSUM the percents of the previous months and
subtract the current months. i.e. if the current percent is 100% and
the summ of the previous was 80%, then this months percent is 20%.
Then append this value to the table as the current days percent build
complete. Here is what my table looks like:

Helo Station W/C Percent Date
14704 1STA Electrical 25 31Dec07
14704 1STA Mechanical 37 31Dec07
14704 1STA Structural 80 31Dec07
14704 2STA Mechanical 19 31Dec07
14704 2STA Structural 25 31Dec07
14705 2STA Electrical 7 31Dec07
14705 1STA Mechanical 19 31Dec07
....
14704 1STA Electrical 9 31Jan08
14704 1STA Mechanical 12 31Jan08
14704 1STA Structural 20 31Jan08
14705 1STA Electrical 12 31Jan08

My DSUM is DSUM("[Percent]","tblMonthlyUnits","[Helo#]=[Helo] and
[STATION]=[Station] and [Lead]=[W/C]"). This gives very strange
results that are not correct. If I change the right side of the
equals to actual data it works. i.e.
DSUM("[Percent]","tblMonthlyUnits","[Helo#]=14704 and [STATION]="1STA"
and [Lead]="Electrical"") I have tried everything I could think of
and nothing seems to work.

 

[Dennis]

When you use variables, they need to be outside the quotes. Hard code values
are OK inside the quotes. If any of the fields are strings then you will also
need a single apostrophe.

DSUM("[Percent]","tblMonthlyUnits","[Helo#]=" & [Helo] & " and [STATION]=" &
[Station] & " and [Lead]= " & [W/C])

or

DSUM("[Percent]","tblMonthlyUnits","[Helo#]='" & [Helo] & "' and
[STATION]='" & [Station] & "' and [Lead]= '" & [W/C] & "'")

 

[NeoFax]

Here is the DSUM I used:

PercentageStage: ([sumofhrs wkd]/[sumofhours])-
(DSum("[PercentStage]","tblImport-Units","[Helo]=" & [Helo] & " and
[STATION]='" & [STATION] & "' and [LEAD]='" & [LEAD] & "'"))

I receive an error still. Also, sorry for the confusion as I wrote
this at home and was going off of memory as how my tables are built.
Here is what I actually have:

tblUnits_Summary:

HELO STATION SumOfHOURS SumOfHRS WKD LEAD
14704 1STA 86.51 86.51 Electrical
14704 1STA 99 99 Mechanical
14704 1STA 38.5 38.5 Structural
14704 2STA 30 30 Electrical
14704 2STA 128 128 Mechanical
14704 2STA 31.5 31.5 Structural
14704 3STA 26 26 Electrical
14704 3STA 116 116 Mechanical
14704 3STA 46.51 46.51 Structural
14704 4FL 21 4 Electrical
14704 4FL 219.5 167.8 Mechanical
14704 4FL 10.01 10 Structural
14704 5CUSTOM 303.01 294.91 Electrical
14704 5CUSTOM 8 8 Mechanical
14704 5CUSTOM 130.5 115.5 Structural
14710 1STA 171.51 165.28 Electrical
14710 1STA 74 71.2 Mechanical
14710 1STA 29.5 21.5 Structural
14710 2STA 32.5 32.4 Electrical
14710 2STA 128 63.35 Mechanical
14710 2STA 26 10 Structural
14710 3STA 79 0 Electrical
14710 3STA 116 20.55 Mechanical
14710 3STA 46.51 22 Structural
14710 4FL 43 0 Electrical
14710 4FL 197.5 0 Mechanical
14710 4FL 183.02 0 Structural
14710 5CUSTOM 32 0 Electrical
14710 5CUSTOM 90 8 Mechanical
14710 5CUSTOM 110.5 32.68 Structural
14712 1STA 165.51 161.16 Electrical
14712 1STA 74 70.3 Mechanical
14712 1STA 21.5 21.2 Structural
14712 2STA 32.5 32.4 Electrical
14712 2STA 128 44.1 Mechanical
14712 2STA 23 8 Structural
14712 3STA 79 11.2 Electrical
14712 3STA 116 15.2 Mechanical
14712 3STA 46.51 7.3 Structural
14712 4FL 39 0 Electrical
14712 4FL 202 0 Mechanical
14712 4FL 10.02 0 Structural
14712 5CUSTOM 7.5 0 Electrical


tblImport-Units:

HELO STATION LEAD Percent Date
14707 2STA ELECTRICAL 31 2/29/2008
14707 3STA STRUCTURAL 1 2/29/2008
14707 3STA MECHANICAL 1 2/29/2008
14707 3STA ELECTRICAL 1 2/29/2008
14707 4FL MECHANICAL 0 2/29/2008
14707 4FL ELECTRICAL 0 2/29/2008
14707 6PAINT STRUCTURAL 0 2/29/2008
14707 INT STRUCTURAL 0 2/29/2008
14707 5CUSTOM STRUCTURAL 24 2/29/2008
14707 5CUSTOM MECHANICAL 24 2/29/2008
14707 5CUSTOM ELECTRICAL 24 2/29/2008
14708 1STA STRUCTURAL 34 2/29/2008
14708 1STA MECHANICAL 34 2/29/2008
14708 1STA ELECTRICAL 34 2/29/2008
14708 2STA STRUCTURAL 14 2/29/2008
14708 2STA MECHANICAL 14 2/29/2008
14708 2STA ELECTRICAL 14 2/29/2008
14708 3STA STRUCTURAL 1 2/29/2008
14708 3STA MECHANICAL 1 2/29/2008
14708 3STA ELECTRICAL 1 2/29/2008
14708 4FL MECHANICAL 0 2/29/2008
14708 4FL ELECTRICAL 0 2/29/2008
14708 6PAINT STRUCTURAL 0 2/29/2008
14708 INT STRUCTURAL 0 2/29/2008
14708 5CUSTOM STRUCTURAL 9 2/29/2008
14708 5CUSTOM MECHANICAL 9 2/29/2008
14708 5CUSTOM ELECTRICAL 9 2/29/2008
14709 1STA STRUCTURAL 6 2/29/2008
14709 1STA MECHANICAL 6 2/29/2008
14709 1STA ELECTRICAL 6 2/29/2008


Here is the query syntax:

SELECT tblUnits_Summary.HELO, tblUnits_Summary.STATION,
tblUnits_Summary.LEAD, tblUnits_Summary.SumOfHOURS, tblUnits_Summary.
[SumOfHRS WKD], ([sumofhrs wkd]/[sumofhours])-
(DSum("[PercentStage]","tblImport-Units","[Helo]=" & [Helo] & " and
[STATION]='" & [STATION] & "' and [LEAD]='" & [LEAD] & "'")) AS
PercentageStage, dhPreviousWorkdayA(Now()) AS [Date]
FROM tblUnits_Summary
WHERE (((tblUnits_Summary.STATION) Not Like "RWK"));


Thanks!

 

[John Spencer]

Is it possible that you are using the wrong name for a field or table.

I notice that you have tblUnits_Summary, but are using tblImport-Units. If
you are being consistent in naming I would have expected tblImport_Units as
the table name.

Another problem could be caused if Helo is ever null. Since that will create
an invalid where clause.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Here is the DSUM I used:

PercentageStage: ([sumofhrs wkd]/[sumofhours])-
(DSum("[PercentStage]","tblImport-Units","[Helo]=" & [Helo] & " and
[STATION]='" & [STATION] & "' and [LEAD]='" & [LEAD] & "'"))

I receive an error still. Also, sorry for the confusion as I wrote
this at home and was going off of memory as how my tables are built.
Here is what I actually have:

tblUnits_Summary:

HELO STATION SumOfHOURS SumOfHRS WKD LEAD
14704 1STA 86.51 86.51 Electrical
14704 1STA 99 99 Mechanical
14704 1STA 38.5 38.5 Structural
14704 2STA 30 30 Electrical
14704 2STA 128 128 Mechanical
14704 2STA 31.5 31.5 Structural
14704 3STA 26 26 Electrical
14704 3STA 116 116 Mechanical
14704 3STA 46.51 46.51 Structural
14704 4FL 21 4 Electrical
14704 4FL 219.5 167.8 Mechanical
14704 4FL 10.01 10 Structural
14704 5CUSTOM 303.01 294.91 Electrical
14704 5CUSTOM 8 8 Mechanical
14704 5CUSTOM 130.5 115.5 Structural
14710 1STA 171.51 165.28 Electrical
14710 1STA 74 71.2 Mechanical
14710 1STA 29.5 21.5 Structural
14710 2STA 32.5 32.4 Electrical
14710 2STA 128 63.35 Mechanical
14710 2STA 26 10 Structural
14710 3STA 79 0 Electrical
14710 3STA 116 20.55 Mechanical
14710 3STA 46.51 22 Structural
14710 4FL 43 0 Electrical
14710 4FL 197.5 0 Mechanical
14710 4FL 183.02 0 Structural
14710 5CUSTOM 32 0 Electrical
14710 5CUSTOM 90 8 Mechanical
14710 5CUSTOM 110.5 32.68 Structural
14712 1STA 165.51 161.16 Electrical
14712 1STA 74 70.3 Mechanical
14712 1STA 21.5 21.2 Structural
14712 2STA 32.5 32.4 Electrical
14712 2STA 128 44.1 Mechanical
14712 2STA 23 8 Structural
14712 3STA 79 11.2 Electrical
14712 3STA 116 15.2 Mechanical
14712 3STA 46.51 7.3 Structural
14712 4FL 39 0 Electrical
14712 4FL 202 0 Mechanical
14712 4FL 10.02 0 Structural
14712 5CUSTOM 7.5 0 Electrical


tblImport-Units:

HELO STATION LEAD Percent Date
14707 2STA ELECTRICAL 31 2/29/2008
14707 3STA STRUCTURAL 1 2/29/2008
14707 3STA MECHANICAL 1 2/29/2008
14707 3STA ELECTRICAL 1 2/29/2008
14707 4FL MECHANICAL 0 2/29/2008
14707 4FL ELECTRICAL 0 2/29/2008
14707 6PAINT STRUCTURAL 0 2/29/2008
14707 INT STRUCTURAL 0 2/29/2008
14707 5CUSTOM STRUCTURAL 24 2/29/2008
14707 5CUSTOM MECHANICAL 24 2/29/2008
14707 5CUSTOM ELECTRICAL 24 2/29/2008
14708 1STA STRUCTURAL 34 2/29/2008
14708 1STA MECHANICAL 34 2/29/2008
14708 1STA ELECTRICAL 34 2/29/2008
14708 2STA STRUCTURAL 14 2/29/2008
14708 2STA MECHANICAL 14 2/29/2008
14708 2STA ELECTRICAL 14 2/29/2008
14708 3STA STRUCTURAL 1 2/29/2008
14708 3STA MECHANICAL 1 2/29/2008
14708 3STA ELECTRICAL 1 2/29/2008
14708 4FL MECHANICAL 0 2/29/2008
14708 4FL ELECTRICAL 0 2/29/2008
14708 6PAINT STRUCTURAL 0 2/29/2008
14708 INT STRUCTURAL 0 2/29/2008
14708 5CUSTOM STRUCTURAL 9 2/29/2008
14708 5CUSTOM MECHANICAL 9 2/29/2008
14708 5CUSTOM ELECTRICAL 9 2/29/2008
14709 1STA STRUCTURAL 6 2/29/2008
14709 1STA MECHANICAL 6 2/29/2008
14709 1STA ELECTRICAL 6 2/29/2008


Here is the query syntax:

SELECT tblUnits_Summary.HELO, tblUnits_Summary.STATION,
tblUnits_Summary.LEAD, tblUnits_Summary.SumOfHOURS, tblUnits_Summary.
[SumOfHRS WKD], ([sumofhrs wkd]/[sumofhours])-
(DSum("[PercentStage]","tblImport-Units","[Helo]=" & [Helo] & " and
[STATION]='" & [STATION] & "' and [LEAD]='" & [LEAD] & "'")) AS
PercentageStage, dhPreviousWorkdayA(Now()) AS [Date]
FROM tblUnits_Summary
WHERE (((tblUnits_Summary.STATION) Not Like "RWK"));


Thanks!

 

[NeoFax]

Is it possible that you are using the wrong name for a field or table.

I notice that you have tblUnits_Summary, but are using tblImport-Units.  If
you are being consistent in naming I would have expected tblImport_Units as
the table name.

Another problem could be caused if Helo is ever null.  Since that will create
an invalid where clause.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Here is the DSUM I used:

PercentageStage: ([sumofhrs wkd]/[sumofhours])-
(DSum("[PercentStage]","tblImport-Units","[Helo]=" & [Helo] & " and
[STATION]='" & [STATION] & "' and [LEAD]='" & [LEAD] & "'"))

I receive an error still.  Also, sorry for the confusion as I wrote
this at home and was going off of memory as how my tables are built.
Here is what I actually have:

HELO       STATION SumOfHOURS      SumOfHRS WKD    LEAD
14704      1STA    86.51   86.51   Electrical
14704      1STA    99      99      Mechanical
14704      1STA    38.5    38.5    Structural
14704      2STA    30      30      Electrical
14704      2STA    128     128     Mechanical
14704      2STA    31.5    31.5    Structural
14704      3STA    26      26      Electrical
14704      3STA    116     116     Mechanical
14704      3STA    46.51   46.51   Structural
14704      4FL     21      4       Electrical
14704      4FL     219.5   167.8   Mechanical
14704      4FL     10.01   10      Structural
14704      5CUSTOM 303.01  294.91  Electrical
14704      5CUSTOM 8       8       Mechanical
14704      5CUSTOM 130.5   115.5   Structural
14710      1STA    171.51  165.28  Electrical
14710      1STA    74      71.2    Mechanical
14710      1STA    29.5    21.5    Structural
14710      2STA    32.5    32.4    Electrical
14710      2STA    128     63.35   Mechanical
14710      2STA    26      10      Structural
14710      3STA    79      0       Electrical
14710      3STA    116     20.55   Mechanical
14710      3STA    46.51   22      Structural
14710      4FL     43      0       Electrical
14710      4FL     197.5   0       Mechanical
14710      4FL     183.02  0       Structural
14710      5CUSTOM 32      0       Electrical
14710      5CUSTOM 90      8       Mechanical
14710      5CUSTOM 110.5   32.68   Structural
14712      1STA    165.51  161.16  Electrical
14712      1STA    74      70.3    Mechanical
14712      1STA    21.5    21.2    Structural
14712      2STA    32.5    32.4    Electrical
14712      2STA    128     44.1    Mechanical
14712      2STA    23      8       Structural
14712      3STA    79      11.2    Electrical
14712      3STA    116     15.2    Mechanical
14712      3STA    46.51   7.3     Structural
14712      4FL     39      0       Electrical
14712      4FL     202     0       Mechanical
14712      4FL     10.02   0       Structural
14712      5CUSTOM 7.5     0       Electrical

HELO       STATION LEAD    Percent Date
14707      2STA    ELECTRICAL      31      2/29/2008
14707      3STA    STRUCTURAL      1       2/29/2008
14707      3STA    MECHANICAL      1       2/29/2008
14707      3STA    ELECTRICAL      1       2/29/2008
14707      4FL     MECHANICAL      0       2/29/2008
14707      4FL     ELECTRICAL      0       2/29/2008
14707      6PAINT  STRUCTURAL      0       2/29/2008
14707      INT     STRUCTURAL      0       2/29/2008
14707      5CUSTOM STRUCTURAL      24      2/29/2008
14707      5CUSTOM MECHANICAL      24      2/29/2008
14707      5CUSTOM ELECTRICAL      24      2/29/2008
14708      1STA    STRUCTURAL      34      2/29/2008
14708      1STA    MECHANICAL      34      2/29/2008
14708      1STA    ELECTRICAL      34      2/29/2008
14708      2STA    STRUCTURAL      14      2/29/2008
14708      2STA    MECHANICAL      14      2/29/2008
14708      2STA    ELECTRICAL      14      2/29/2008
14708      3STA    STRUCTURAL      1       2/29/2008
14708      3STA    MECHANICAL      1       2/29/2008
14708      3STA    ELECTRICAL      1       2/29/2008
14708      4FL     MECHANICAL      0       2/29/2008
14708      4FL     ELECTRICAL      0       2/29/2008
14708      6PAINT  STRUCTURAL      0       2/29/2008
14708      INT     STRUCTURAL      0       2/29/2008
14708      5CUSTOM STRUCTURAL      9       2/29/2008
14708      5CUSTOM MECHANICAL      9       2/29/2008
14708      5CUSTOM ELECTRICAL      9       2/29/2008
14709      1STA    STRUCTURAL      6       2/29/2008
14709      1STA    MECHANICAL      6       2/29/2008
14709      1STA    ELECTRICAL      6       2/29/2008

Here is the query syntax:

SELECT tblUnits_Summary.HELO, tblUnits_Summary.STATION,
tblUnits_Summary.LEAD, tblUnits_Summary.SumOfHOURS, tblUnits_Summary.
[SumOfHRS WKD], ([sumofhrs wkd]/[sumofhours])-
(DSum("[PercentStage]","tblImport-Units","[Helo]=" & [Helo] & " and
[STATION]='" & [STATION] & "' and [LEAD]='" & [LEAD] & "'")) AS
PercentageStage, dhPreviousWorkdayA(Now()) AS [Date]
FROM tblUnits_Summary
WHERE (((tblUnits_Summary.STATION) Not Like "RWK"));

Thanks!- Hide quoted text -

- Show quoted text -

I fixed it and now it works. I was missing a single quote. Now I
have a new dilemma, which I hadn't thought of prior to this. OK, the
percent of the build by workcenter is correct at 100%. However, now I
need to ensure that the station totals up to only 100% max. Currently
I have some stations that are totaling to >100%. How would I get the
average of a percentage mathematically correct? Thanks!