merging and sum two text boxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report for employee production based on orders filled. For each
order we assign an employee ( each employee has its own unique number to
identify him), we put his number in a text box [EIDC], there is some orders
that we need to assign two employees and in that case we have a second text
box [EIDC2] that we assign the other employee number, in this case the order
which is based in pounds is split in half for each employee. I need to be
able to have only one total sum for each employee regardless whether they did
all the orders assign to him by himseldf or if some were made by two
employees. Here is the code that I have so far: SELECT Count(ShipAddress.OID)
AS OrdersMade, Sum(EmployeesFillOrderQuery1Auto.SumOfBox) AS TotalBoxes,
Sum(EmployeesFillOrderQuery1Auto.SumOfTWeightUnits) AS TotalWeight,
Sum(EmployeesFillOrderQuery2Auto.SumOfBox2) AS SumOfSumOfBox2,
Sum(EmployeesFillOrderQuery2Auto.SumOfGTWeightUnits) AS
SumOfSumOfGTWeightUnits, ShipAddress.EIDC, ShipAddress.EIDC2
FROM (ShipAddress INNER JOIN EmployeesFillOrderQuery1Auto ON ShipAddress.OID
= EmployeesFillOrderQuery1Auto.OID) INNER JOIN EmployeesFillOrderQuery2Auto
ON ShipAddress.OID = EmployeesFillOrderQuery2Auto.OID
GROUP BY ShipAddress.EIDC, ShipAddress.EIDC2
ORDER BY Sum(EmployeesFillOrderQuery1Auto.SumOfTWeightUnits) DESC;
 
Mario,

In your query, do a Sum on the following:

IIf(IsNull([EIDC]),IIf(IsNull([EDIC2]),0,1),IIf(IsNull([EDIC2]),1,.5))

The above will return 1 if either EDIC or EDIC2 is not null, but will return
..5 if both are not null. If both are null, it returns 0.

Bruce
 
Thank you for your help Bruce, there seem to be a problem with the code, it
gives me a message that there is an extra comma (I played around taking some
off but did not work).

BruceS said:
Mario,

In your query, do a Sum on the following:

IIf(IsNull([EIDC]),IIf(IsNull([EDIC2]),0,1),IIf(IsNull([EDIC2]),1,.5))

The above will return 1 if either EDIC or EDIC2 is not null, but will return
.5 if both are not null. If both are null, it returns 0.

Bruce


Mario said:
I have a report for employee production based on orders filled. For each
order we assign an employee ( each employee has its own unique number to
identify him), we put his number in a text box [EIDC], there is some orders
that we need to assign two employees and in that case we have a second text
box [EIDC2] that we assign the other employee number, in this case the order
which is based in pounds is split in half for each employee. I need to be
able to have only one total sum for each employee regardless whether they did
all the orders assign to him by himseldf or if some were made by two
employees. Here is the code that I have so far: SELECT Count(ShipAddress.OID)
AS OrdersMade, Sum(EmployeesFillOrderQuery1Auto.SumOfBox) AS TotalBoxes,
Sum(EmployeesFillOrderQuery1Auto.SumOfTWeightUnits) AS TotalWeight,
Sum(EmployeesFillOrderQuery2Auto.SumOfBox2) AS SumOfSumOfBox2,
Sum(EmployeesFillOrderQuery2Auto.SumOfGTWeightUnits) AS
SumOfSumOfGTWeightUnits, ShipAddress.EIDC, ShipAddress.EIDC2
FROM (ShipAddress INNER JOIN EmployeesFillOrderQuery1Auto ON ShipAddress.OID
= EmployeesFillOrderQuery1Auto.OID) INNER JOIN EmployeesFillOrderQuery2Auto
ON ShipAddress.OID = EmployeesFillOrderQuery2Auto.OID
GROUP BY ShipAddress.EIDC, ShipAddress.EIDC2
ORDER BY Sum(EmployeesFillOrderQuery1Auto.SumOfTWeightUnits) DESC;
Click to expand...
Click to expand...
 
Mario,
Sorry for the delay. I had a cookie problem and could not get logged back
in to reply until today.

My error was not in the commas, it was in the field name. In original reply
I transposed two characters in the firest "EDIC". It should have read:

IIf(IsNull([EDIC]),IIf(IsNull([EDIC2]),0,1),IIf(IsNull([EDIC2]),1,.5))

When placed into the Query design, it should be in the top line as:

Expr1: IIf(IsNull([EIDC]),IIf(IsNull([EDIC2]),0,1),IIf(IsNull([EDIC2]),1,.5))

You can then Sum, Count, whatever, the returned value of 0, 1, or 0.5.

Have tested, and it works.

Bruce

Mario said:
Thank you for your help Bruce, there seem to be a problem with the code, it
gives me a message that there is an extra comma (I played around taking some
off but did not work).

BruceS said:
Mario,

In your query, do a Sum on the following:

IIf(IsNull([EIDC]),IIf(IsNull([EDIC2]),0,1),IIf(IsNull([EDIC2]),1,.5))

The above will return 1 if either EDIC or EDIC2 is not null, but will return
.5 if both are not null. If both are null, it returns 0.

Bruce


Mario said:
I have a report for employee production based on orders filled. For each
order we assign an employee ( each employee has its own unique number to
identify him), we put his number in a text box [EIDC], there is some orders
that we need to assign two employees and in that case we have a second text
box [EIDC2] that we assign the other employee number, in this case the order
which is based in pounds is split in half for each employee. I need to be
able to have only one total sum for each employee regardless whether they did
all the orders assign to him by himseldf or if some were made by two
employees. Here is the code that I have so far: SELECT Count(ShipAddress.OID)
AS OrdersMade, Sum(EmployeesFillOrderQuery1Auto.SumOfBox) AS TotalBoxes,
Sum(EmployeesFillOrderQuery1Auto.SumOfTWeightUnits) AS TotalWeight,
Sum(EmployeesFillOrderQuery2Auto.SumOfBox2) AS SumOfSumOfBox2,
Sum(EmployeesFillOrderQuery2Auto.SumOfGTWeightUnits) AS
SumOfSumOfGTWeightUnits, ShipAddress.EIDC, ShipAddress.EIDC2
FROM (ShipAddress INNER JOIN EmployeesFillOrderQuery1Auto ON ShipAddress.OID
= EmployeesFillOrderQuery1Auto.OID) INNER JOIN EmployeesFillOrderQuery2Auto
ON ShipAddress.OID = EmployeesFillOrderQuery2Auto.OID
GROUP BY ShipAddress.EIDC, ShipAddress.EIDC2
ORDER BY Sum(EmployeesFillOrderQuery1Auto.SumOfTWeightUnits) DESC;
Click to expand...
Click to expand...
Click to expand...
 
It worked! Thanks Bruce

BruceS said:
Mario,
Sorry for the delay. I had a cookie problem and could not get logged back
in to reply until today.

My error was not in the commas, it was in the field name. In original reply
I transposed two characters in the firest "EDIC". It should have read:

IIf(IsNull([EDIC]),IIf(IsNull([EDIC2]),0,1),IIf(IsNull([EDIC2]),1,.5))

When placed into the Query design, it should be in the top line as:

Expr1: IIf(IsNull([EIDC]),IIf(IsNull([EDIC2]),0,1),IIf(IsNull([EDIC2]),1,.5))

You can then Sum, Count, whatever, the returned value of 0, 1, or 0.5.

Have tested, and it works.

Bruce

Mario said:
Thank you for your help Bruce, there seem to be a problem with the code, it
gives me a message that there is an extra comma (I played around taking some
off but did not work).

BruceS said:
Mario,

In your query, do a Sum on the following:

IIf(IsNull([EIDC]),IIf(IsNull([EDIC2]),0,1),IIf(IsNull([EDIC2]),1,.5))

The above will return 1 if either EDIC or EDIC2 is not null, but will return
.5 if both are not null. If both are null, it returns 0.

Bruce


:

I have a report for employee production based on orders filled. For each
order we assign an employee ( each employee has its own unique number to
identify him), we put his number in a text box [EIDC], there is some orders
that we need to assign two employees and in that case we have a second text
box [EIDC2] that we assign the other employee number, in this case the order
which is based in pounds is split in half for each employee. I need to be
able to have only one total sum for each employee regardless whether they did
all the orders assign to him by himseldf or if some were made by two
employees. Here is the code that I have so far: SELECT Count(ShipAddress.OID)
AS OrdersMade, Sum(EmployeesFillOrderQuery1Auto.SumOfBox) AS TotalBoxes,
Sum(EmployeesFillOrderQuery1Auto.SumOfTWeightUnits) AS TotalWeight,
Sum(EmployeesFillOrderQuery2Auto.SumOfBox2) AS SumOfSumOfBox2,
Sum(EmployeesFillOrderQuery2Auto.SumOfGTWeightUnits) AS
SumOfSumOfGTWeightUnits, ShipAddress.EIDC, ShipAddress.EIDC2
FROM (ShipAddress INNER JOIN EmployeesFillOrderQuery1Auto ON ShipAddress.OID
= EmployeesFillOrderQuery1Auto.OID) INNER JOIN EmployeesFillOrderQuery2Auto
ON ShipAddress.OID = EmployeesFillOrderQuery2Auto.OID
GROUP BY ShipAddress.EIDC, ShipAddress.EIDC2
ORDER BY Sum(EmployeesFillOrderQuery1Auto.SumOfTWeightUnits) DESC;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top