Query question

[Guest]

My table:

Employee No Work Code No of hours Start Value
2 4.0
2 89 2
2 92 1
2 92 1
2 92 2


Here is my query – the results I am trying to get are:

Work Code 89 is Earned
Work Code 92 is Taken


The start value is 4 – so I would need to add work code 89 and then minus 92
and then the result is 2? How would I set up a query?

 

[Guest]

Try this after you fill the nulls with zeros.
SELECT Kelly.[Employee No], Sum(IIf([Work Code]=92,([No of hours]*-1)+[Start
value],[No of hours]+[Start value])) AS Balance
FROM Kelly
GROUP BY Kelly.[Employee No];

OR this with the nulls
SELECT Kelly.[Employee No], Sum(IIf([Work Code]=92,([No of
hours]*-1)+IIf([Start value] Is Null,0,[Start value]),[No of
hours]+IIf([Start value] Is Null,0,[Start value]))) AS Balance
FROM Kelly
GROUP BY Kelly.[Employee No];

 

[Guest]

Do I enter that in a query in a column by it self?
--
Kelly

Try this after you fill the nulls with zeros.
SELECT Kelly.[Employee No], Sum(IIf([Work Code]=92,([No of hours]*-1)+[Start
value],[No of hours]+[Start value])) AS Balance
FROM Kelly
GROUP BY Kelly.[Employee No];

OR this with the nulls
SELECT Kelly.[Employee No], Sum(IIf([Work Code]=92,([No of
hours]*-1)+IIf([Start value] Is Null,0,[Start value]),[No of
hours]+IIf([Start value] Is Null,0,[Start value]))) AS Balance
FROM Kelly
GROUP BY Kelly.[Employee No];

--
KARL DEWEY
Build a little - Test a little

My table:

Employee No Work Code No of hours Start Value
2 4.0
2 89 2
2 92 1
2 92 1
2 92 2


Here is my query – the results I am trying to get are:

Work Code 89 is Earned
Work Code 92 is Taken


The start value is 4 – so I would need to add work code 89 and then minus 92
and then the result is 2? How would I set up a query?

 

[Guest]

What I posted IS the query.
Open a new query in design view, click on menu VIEW - SQL View, and pase the
post in the window that opens. Remove any hard returns the copying, posting,
copying, and pasting may put in such as
)+[Start
value],[No of hours]+[
TO BE ---
)+[Start value],[No of hours]+[

Replace Kelly with your table name.
--
KARL DEWEY
Build a little - Test a little

Do I enter that in a query in a column by it self?
--
Kelly

Try this after you fill the nulls with zeros.
SELECT Kelly.[Employee No], Sum(IIf([Work Code]=92,([No of hours]*-1)+[Start
value],[No of hours]+[Start value])) AS Balance
FROM Kelly
GROUP BY Kelly.[Employee No];

OR this with the nulls
SELECT Kelly.[Employee No], Sum(IIf([Work Code]=92,([No of
hours]*-1)+IIf([Start value] Is Null,0,[Start value]),[No of
hours]+IIf([Start value] Is Null,0,[Start value]))) AS Balance
FROM Kelly
GROUP BY Kelly.[Employee No];

--
KARL DEWEY
Build a little - Test a little

My table:

Employee No Work Code No of hours Start Value
2 4.0
2 89 2
2 92 1
2 92 1
2 92 2


Here is my query – the results I am trying to get are:

Work Code 89 is Earned
Work Code 92 is Taken


The start value is 4 – so I would need to add work code 89 and then minus 92
and then the result is 2? How would I set up a query?

 

[Guest]

The name of my table is Comp Time and I did change that but I am getting an
error.

Syntax error (missing operator) in query expression ‘Comp Time.[Employee No]’.

I do have three other colums in that table, but I don't want them in my
query - is that the problem?

SELECT Comp Time. [Employee No], Sum(IIf([Work Code]=92,([No of
hours]*-1)+IIf([Start value] Is Null,0,[Start value]),[No of
hours]+IIf([Start value] Is Null,0,[Start value]))) AS Balance FROM Comp Time
GROUP BY Comp Time.[Employee No];
--
Kelly

What I posted IS the query.
Open a new query in design view, click on menu VIEW - SQL View, and pase the
post in the window that opens. Remove any hard returns the copying, posting,
copying, and pasting may put in such as
)+[Start
value],[No of hours]+[
TO BE ---
)+[Start value],[No of hours]+[

Replace Kelly with your table name.
--
KARL DEWEY
Build a little - Test a little

Do I enter that in a query in a column by it self?
--
Kelly

Try this after you fill the nulls with zeros.
SELECT Kelly.[Employee No], Sum(IIf([Work Code]=92,([No of hours]*-1)+[Start
value],[No of hours]+[Start value])) AS Balance
FROM Kelly
GROUP BY Kelly.[Employee No];

OR this with the nulls
SELECT Kelly.[Employee No], Sum(IIf([Work Code]=92,([No of
hours]*-1)+IIf([Start value] Is Null,0,[Start value]),[No of
hours]+IIf([Start value] Is Null,0,[Start value]))) AS Balance
FROM Kelly
GROUP BY Kelly.[Employee No];

--
KARL DEWEY
Build a little - Test a little


:

My table:

Employee No Work Code No of hours Start Value
2 4.0
2 89 2
2 92 1
2 92 1
2 92 2


Here is my query – the results I am trying to get are:

Work Code 89 is Earned
Work Code 92 is Taken


The start value is 4 – so I would need to add work code 89 and then minus 92
and then the result is 2? How would I set up a query?

 

[Guest]

Try this --
SELECT [Comp Time].[Employee No], Sum(IIf([Work Code]=92,([No of hours]*-1)
+IIf([Start value] Is Null,0,[Start value]),[No of hours] +IIf([Start value]
Is Null,0,[Start value]))) AS Balance FROM [Comp Time]
GROUP BY [Comp Time].[Employee No];

Your table name has a space in it therefore it must be bracketed.
--
KARL DEWEY
Build a little - Test a little

The name of my table is Comp Time and I did change that but I am getting an
error.

Syntax error (missing operator) in query expression ‘Comp Time.[Employee No]’.

I do have three other colums in that table, but I don't want them in my
query - is that the problem?

SELECT Comp Time. [Employee No], Sum(IIf([Work Code]=92,([No of
hours]*-1)+IIf([Start value] Is Null,0,[Start value]),[No of
hours]+IIf([Start value] Is Null,0,[Start value]))) AS Balance FROM Comp Time
GROUP BY Comp Time.[Employee No];
--
Kelly

What I posted IS the query.
Open a new query in design view, click on menu VIEW - SQL View, and pase the
post in the window that opens. Remove any hard returns the copying, posting,
copying, and pasting may put in such as
)+[Start
value],[No of hours]+[
TO BE ---
)+[Start value],[No of hours]+[

Replace Kelly with your table name.
--
KARL DEWEY
Build a little - Test a little

Do I enter that in a query in a column by it self?
--
Kelly


:

Try this after you fill the nulls with zeros.
SELECT Kelly.[Employee No], Sum(IIf([Work Code]=92,([No of hours]*-1)+[Start
value],[No of hours]+[Start value])) AS Balance
FROM Kelly
GROUP BY Kelly.[Employee No];

OR this with the nulls
SELECT Kelly.[Employee No], Sum(IIf([Work Code]=92,([No of
hours]*-1)+IIf([Start value] Is Null,0,[Start value]),[No of
hours]+IIf([Start value] Is Null,0,[Start value]))) AS Balance
FROM Kelly
GROUP BY Kelly.[Employee No];

--
KARL DEWEY
Build a little - Test a little


:

My table:

Employee No Work Code No of hours Start Value
2 4.0
2 89 2
2 92 1
2 92 1
2 92 2


Here is my query – the results I am trying to get are:

Work Code 89 is Earned
Work Code 92 is Taken


The start value is 4 – so I would need to add work code 89 and then minus 92
and then the result is 2? How would I set up a query?

 

[Guest]

Getting this error:

Data type mismatch in criteria expression

SELECT[Comp Time].[Employee No], Sum(IIf([Work Code]=92,([No of
hours]*-1)+IIf([Start value] Is Null,0,[Start value]),[No of
hours]+IIf([Start value] Is Null,0,[Start value]))) AS Balance FROM [Comp
Time]GROUP BY [Comp Time].[Employee No];Kelly

Try this --
SELECT [Comp Time].[Employee No], Sum(IIf([Work Code]=92,([No of hours]*-1)
+IIf([Start value] Is Null,0,[Start value]),[No of hours] +IIf([Start value]
Is Null,0,[Start value]))) AS Balance FROM [Comp Time]
GROUP BY [Comp Time].[Employee No];

Your table name has a space in it therefore it must be bracketed.
--
KARL DEWEY
Build a little - Test a little

The name of my table is Comp Time and I did change that but I am getting an
error.

Syntax error (missing operator) in query expression ‘Comp Time.[Employee No]’.

I do have three other colums in that table, but I don't want them in my
query - is that the problem?

SELECT Comp Time. [Employee No], Sum(IIf([Work Code]=92,([No of
hours]*-1)+IIf([Start value] Is Null,0,[Start value]),[No of
hours]+IIf([Start value] Is Null,0,[Start value]))) AS Balance FROM Comp Time
GROUP BY Comp Time.[Employee No];
--
Kelly

What I posted IS the query.
Open a new query in design view, click on menu VIEW - SQL View, and pase the
post in the window that opens. Remove any hard returns the copying, posting,
copying, and pasting may put in such as
)+[Start
value],[No of hours]+[
TO BE ---
)+[Start value],[No of hours]+[

Replace Kelly with your table name.
--
KARL DEWEY
Build a little - Test a little


:

Do I enter that in a query in a column by it self?
--
Kelly


:

Try this after you fill the nulls with zeros.
SELECT Kelly.[Employee No], Sum(IIf([Work Code]=92,([No of hours]*-1)+[Start
value],[No of hours]+[Start value])) AS Balance
FROM Kelly
GROUP BY Kelly.[Employee No];

OR this with the nulls
SELECT Kelly.[Employee No], Sum(IIf([Work Code]=92,([No of
hours]*-1)+IIf([Start value] Is Null,0,[Start value]),[No of
hours]+IIf([Start value] Is Null,0,[Start value]))) AS Balance
FROM Kelly
GROUP BY Kelly.[Employee No];

--
KARL DEWEY
Build a little - Test a little


:

My table:

Employee No Work Code No of hours Start Value
2 4.0
2 89 2
2 92 1
2 92 1
2 92 2


Here is my query – the results I am trying to get are:

Work Code 89 is Earned
Work Code 92 is Taken


The start value is 4 – so I would need to add work code 89 and then minus 92
and then the result is 2? How would I set up a query?

 

[John W. Vinson]

Getting this error:

Data type mismatch in criteria expression

SELECT[Comp Time].[Employee No], Sum(IIf([Work Code]=92,([No of
hours]*-1)+IIf([Start value] Is Null,0,[Start value]),[No of
hours]+IIf([Start value] Is Null,0,[Start value]))) AS Balance FROM [Comp
Time]GROUP BY [Comp Time].[Employee No];Kelly

I'm guessing that [Work Code] is a Text field rather than an Integer field -
if so use

IIF([Work Code] = '92', ...


John W. Vinson [MVP]