Make Table Query, Question....

G

Guest

I have a MTQ that I am creating. This is from a duplicates query that I ran.
I now want to condense the dups into one row. The problem is in the last 4
columns I have YES and NO in the cell. If one of them has a NO then I need
to have the condensed line have a NO. If between the two duplicates they are
both YES then I need the condensed row to have a YES and the same for the NO.
I have tried a few iIF statements but I am not getting the results I want.
Any help would be appreciated.
 
G

Guest

Try this -
SELECT T_Miller.X, IIf(Abs(Sum([C1]))=Count([x]),-1,0) AS Column_1,
IIf(Abs(Sum([C2]))=Count([x]),-1,0) AS Column_2,
IIf(Abs(Sum([C3]))=Count([x]),-1,0) AS Column_3,
IIf(Abs(Sum([C4]))=Count([x]),-1,0) AS Column_4
FROM T_Miller
GROUP BY T_Miller.X;

C1, C2, C3, and C4 are your 4 Yes/No fields. X represents all of the other
fields.
This compares the absolute sum of a column to the count. If they are equal
then all are Yes for an out out of -1.
 
G

Guest

Ok, here is the make table ---
SELECT T_Miller.X, IIf(Abs(Sum([C1]))=Count([x]),-1,0) AS Column_1,
IIf(Abs(Sum([C2]))=Count([x]),-1,0) AS Column_2,
IIf(Abs(Sum([C3]))=Count([x]),-1,0) AS Column_3,
IIf(Abs(Sum([C4]))=Count([x]),-1,0) AS Column_4 INTO [T_Miller-X]
FROM T_Miller
GROUP BY T_Miller.X;
--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Try this -
SELECT T_Miller.X, IIf(Abs(Sum([C1]))=Count([x]),-1,0) AS Column_1,
IIf(Abs(Sum([C2]))=Count([x]),-1,0) AS Column_2,
IIf(Abs(Sum([C3]))=Count([x]),-1,0) AS Column_3,
IIf(Abs(Sum([C4]))=Count([x]),-1,0) AS Column_4
FROM T_Miller
GROUP BY T_Miller.X;

C1, C2, C3, and C4 are your 4 Yes/No fields. X represents all of the other
fields.
This compares the absolute sum of a column to the count. If they are equal
then all are Yes for an out out of -1.
--
KARL DEWEY
Build a little - Test a little


T Miller said:
I have a MTQ that I am creating. This is from a duplicates query that I ran.
I now want to condense the dups into one row. The problem is in the last 4
columns I have YES and NO in the cell. If one of them has a NO then I need
to have the condensed line have a NO. If between the two duplicates they are
both YES then I need the condensed row to have a YES and the same for the NO.
I have tried a few iIF statements but I am not getting the results I want.
Any help would be appreciated.
Click to expand...
Click to expand...
 
G

Guest

aKarl,

I am also using a sum function on all of the other fields (Group By) and one
on them is (Last) to pull into on row. The last four columns that have the
YES and NO in them I am trying to do what I originally posted. With that
said, the iIF statement that you provided will not run with the sum function?
Do I need to change something in the iIF statement? Here is the error I am
getting "circular reference caused by alias 'PTCApcnt' in query definition's
SELECT list"
--
Thomas


KARL DEWEY said:
Ok, here is the make table ---
SELECT T_Miller.X, IIf(Abs(Sum([C1]))=Count([x]),-1,0) AS Column_1,
IIf(Abs(Sum([C2]))=Count([x]),-1,0) AS Column_2,
IIf(Abs(Sum([C3]))=Count([x]),-1,0) AS Column_3,
IIf(Abs(Sum([C4]))=Count([x]),-1,0) AS Column_4 INTO [T_Miller-X]
FROM T_Miller
GROUP BY T_Miller.X;
--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Try this -
SELECT T_Miller.X, IIf(Abs(Sum([C1]))=Count([x]),-1,0) AS Column_1,
IIf(Abs(Sum([C2]))=Count([x]),-1,0) AS Column_2,
IIf(Abs(Sum([C3]))=Count([x]),-1,0) AS Column_3,
IIf(Abs(Sum([C4]))=Count([x]),-1,0) AS Column_4
FROM T_Miller
GROUP BY T_Miller.X;

C1, C2, C3, and C4 are your 4 Yes/No fields. X represents all of the other
fields.
This compares the absolute sum of a column to the count. If they are equal
then all are Yes for an out out of -1.
--
KARL DEWEY
Build a little - Test a little


T Miller said:
I have a MTQ that I am creating. This is from a duplicates query that I ran.
I now want to condense the dups into one row. The problem is in the last 4
columns I have YES and NO in the cell. If one of them has a NO then I need
to have the condensed line have a NO. If between the two duplicates they are
both YES then I need the condensed row to have a YES and the same for the NO.
I have tried a few iIF statements but I am not getting the results I want.
Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Karl,

Sorry, I am just not getting it,

In the Field: I should have,

PTCApcnt: SELECT (IC Data), IIf(Abs(Sum([PTCAppcnt]))=Count([ALL OTHER
FIELDS]),-1,0) AS Column_1,
ElectroPcnt:IIf(Abs(Sum([ElectroPcnt]))=Count([ALL OTHER FIELDS]),-1,0) AS
Column_2,
CompElectPcnt:IIf(Abs(Sum([CompElectPcnt]))=Count([ALL OTHER FIELDS]),-1,0)
AS Column_3,
IncPaceDefib:IIf(Abs(Sum([IncPaceDefib]))=Count([ALL OTHER FIELDS]),-1,0) AS
Column_4 INTO [T_Miller-X], this part I don't undersand??
FROM T_Miller this part I don't understand??
GROUP BY T_Miller.X; this part I don't understand??
Click to expand...

I appreciate your help with this, I knew this was not going to be easy and
it is not easy to articulate as well.

--
Thomas


KARL DEWEY said:
Ok, here is the make table ---
SELECT T_Miller.X, IIf(Abs(Sum([C1]))=Count([x]),-1,0) AS Column_1,
IIf(Abs(Sum([C2]))=Count([x]),-1,0) AS Column_2,
IIf(Abs(Sum([C3]))=Count([x]),-1,0) AS Column_3,
IIf(Abs(Sum([C4]))=Count([x]),-1,0) AS Column_4 INTO [T_Miller-X]
FROM T_Miller
GROUP BY T_Miller.X;
--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Try this -
SELECT T_Miller.X, IIf(Abs(Sum([C1]))=Count([x]),-1,0) AS Column_1,
IIf(Abs(Sum([C2]))=Count([x]),-1,0) AS Column_2,
IIf(Abs(Sum([C3]))=Count([x]),-1,0) AS Column_3,
IIf(Abs(Sum([C4]))=Count([x]),-1,0) AS Column_4
FROM T_Miller
GROUP BY T_Miller.X;

C1, C2, C3, and C4 are your 4 Yes/No fields. X represents all of the other
fields.
This compares the absolute sum of a column to the count. If they are equal
then all are Yes for an out out of -1.
--
KARL DEWEY
Build a little - Test a little


T Miller said:
I have a MTQ that I am creating. This is from a duplicates query that I ran.
I now want to condense the dups into one row. The problem is in the last 4
columns I have YES and NO in the cell. If one of them has a NO then I need
to have the condensed line have a NO. If between the two duplicates they are
both YES then I need the condensed row to have a YES and the same for the NO.
I have tried a few iIF statements but I am not getting the results I want.
Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Here is the error I am getting "circular reference caused by alias
'PTCApcnt' in query definition's SELECT list"
PTCApcnt: SELECT (IC Data), IIf(Abs(Sum([PTCAppcnt]))=Count([ALL OTHER
Click to expand...
Click to expand...
FIELDS]),-1,0) AS Column_1,
You have an alias named the same as a field name - PTCApcnt - therefore the
circular reference.
Do you really have a field named [ALL OTHER FIELDS]?
What I post was a complete SQL statement of a query but it seems that you
pasted it as an output field in a query design view grid.
Post your query SQL and I or someone else will try to edit it for you.

--
KARL DEWEY
Build a little - Test a little


T Miller said:
Karl,

Sorry, I am just not getting it,

In the Field: I should have,

PTCApcnt: SELECT (IC Data), IIf(Abs(Sum([PTCAppcnt]))=Count([ALL OTHER
FIELDS]),-1,0) AS Column_1,
ElectroPcnt:IIf(Abs(Sum([ElectroPcnt]))=Count([ALL OTHER FIELDS]),-1,0) AS
Column_2,
CompElectPcnt:IIf(Abs(Sum([CompElectPcnt]))=Count([ALL OTHER FIELDS]),-1,0)
AS Column_3,
IncPaceDefib:IIf(Abs(Sum([IncPaceDefib]))=Count([ALL OTHER FIELDS]),-1,0) AS
Column_4 INTO [T_Miller-X], this part I don't undersand??
FROM T_Miller this part I don't understand??
GROUP BY T_Miller.X; this part I don't understand??
Click to expand...

I appreciate your help with this, I knew this was not going to be easy and
it is not easy to articulate as well.

--
Thomas


KARL DEWEY said:
Ok, here is the make table ---
SELECT T_Miller.X, IIf(Abs(Sum([C1]))=Count([x]),-1,0) AS Column_1,
IIf(Abs(Sum([C2]))=Count([x]),-1,0) AS Column_2,
IIf(Abs(Sum([C3]))=Count([x]),-1,0) AS Column_3,
IIf(Abs(Sum([C4]))=Count([x]),-1,0) AS Column_4 INTO [T_Miller-X]
FROM T_Miller
GROUP BY T_Miller.X;
--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Try this -
SELECT T_Miller.X, IIf(Abs(Sum([C1]))=Count([x]),-1,0) AS Column_1,
IIf(Abs(Sum([C2]))=Count([x]),-1,0) AS Column_2,
IIf(Abs(Sum([C3]))=Count([x]),-1,0) AS Column_3,
IIf(Abs(Sum([C4]))=Count([x]),-1,0) AS Column_4
FROM T_Miller
GROUP BY T_Miller.X;

C1, C2, C3, and C4 are your 4 Yes/No fields. X represents all of the other
fields.
This compares the absolute sum of a column to the count. If they are equal
then all are Yes for an out out of -1.
--
KARL DEWEY
Build a little - Test a little


:

I have a MTQ that I am creating. This is from a duplicates query that I ran.
I now want to condense the dups into one row. The problem is in the last 4
columns I have YES and NO in the cell. If one of them has a NO then I need
to have the condensed line have a NO. If between the two duplicates they are
both YES then I need the condensed row to have a YES and the same for the NO.
I have tried a few iIF statements but I am not getting the results I want.
Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

No sir I do not have them as "ALL OTHER FIELDS" I was trying to say do I put
all of the other field names here. Sorry, like I was saying I am trying to
say it the best way I can. Here is the SQL view of what I have, with out
what you said to put in it.

SELECT [All Duplicates IC Profile].FacID, [All Duplicates IC
Profile].Year, [All Duplicates IC Profile].Qtr, [All Duplicates IC
Profile].Facility, [All Duplicates IC Profile].City, [All Duplicates IC
Profile].State, [All Duplicates IC Profile].Zip, [All Duplicates IC
Profile].[Census Div], [All Duplicates IC Profile].[VHA Region], [All
Duplicates IC Profile].ProfID, Last([All Duplicates IC Profile].Department)
AS LastOfDepartment, [All Duplicates IC Profile].PTCApcnt, [All Duplicates
IC Profile].ElectroPcnt, [All Duplicates IC Profile].CompElectPcnt, [All
Duplicates IC Profile].IncPaceDefib
FROM [All Duplicates IC Profile]
GROUP BY [All Duplicates IC Profile].FacID, [All Duplicates IC
Profile].Year, [All Duplicates IC Profile].Qtr, [All Duplicates IC
Profile].Facility, [All Duplicates IC Profile].City, [All Duplicates IC
Profile].State, [All Duplicates IC Profile].Zip, [All Duplicates IC
Profile].[Census Div], [All Duplicates IC Profile].[VHA Region], [All
Duplicates IC Profile].ProfID, [All Duplicates IC Profile].PTCApcnt, [All
Duplicates IC Profile].ElectroPcnt, [All Duplicates IC
Profile].CompElectPcnt, [All Duplicates IC Profile].IncPaceDefib;

--
Thomas


KARL DEWEY said:
Here is the error I am getting "circular reference caused by alias 'PTCApcnt' in query definition's SELECT list"
PTCApcnt: SELECT (IC Data), IIf(Abs(Sum([PTCAppcnt]))=Count([ALL OTHER
Click to expand...
Click to expand...
FIELDS]),-1,0) AS Column_1,
You have an alias named the same as a field name - PTCApcnt - therefore the
circular reference.
Do you really have a field named [ALL OTHER FIELDS]?
What I post was a complete SQL statement of a query but it seems that you
pasted it as an output field in a query design view grid.
Post your query SQL and I or someone else will try to edit it for you.

--
KARL DEWEY
Build a little - Test a little


T Miller said:
Karl,

Sorry, I am just not getting it,

In the Field: I should have,

PTCApcnt: SELECT (IC Data), IIf(Abs(Sum([PTCAppcnt]))=Count([ALL OTHER
FIELDS]),-1,0) AS Column_1,
ElectroPcnt:IIf(Abs(Sum([ElectroPcnt]))=Count([ALL OTHER FIELDS]),-1,0) AS
Column_2,
CompElectPcnt:IIf(Abs(Sum([CompElectPcnt]))=Count([ALL OTHER FIELDS]),-1,0)
AS Column_3,
IncPaceDefib:IIf(Abs(Sum([IncPaceDefib]))=Count([ALL OTHER FIELDS]),-1,0) AS
Column_4 INTO [T_Miller-X], this part I don't undersand??
FROM T_Miller this part I don't understand??
GROUP BY T_Miller.X; this part I don't understand??
Click to expand...

I appreciate your help with this, I knew this was not going to be easy and
it is not easy to articulate as well.

--
Thomas


KARL DEWEY said:
Ok, here is the make table ---
SELECT T_Miller.X, IIf(Abs(Sum([C1]))=Count([x]),-1,0) AS Column_1,
IIf(Abs(Sum([C2]))=Count([x]),-1,0) AS Column_2,
IIf(Abs(Sum([C3]))=Count([x]),-1,0) AS Column_3,
IIf(Abs(Sum([C4]))=Count([x]),-1,0) AS Column_4 INTO [T_Miller-X]
FROM T_Miller
GROUP BY T_Miller.X;
--
KARL DEWEY
Build a little - Test a little


:

Try this -
SELECT T_Miller.X, IIf(Abs(Sum([C1]))=Count([x]),-1,0) AS Column_1,
IIf(Abs(Sum([C2]))=Count([x]),-1,0) AS Column_2,
IIf(Abs(Sum([C3]))=Count([x]),-1,0) AS Column_3,
IIf(Abs(Sum([C4]))=Count([x]),-1,0) AS Column_4
FROM T_Miller
GROUP BY T_Miller.X;

C1, C2, C3, and C4 are your 4 Yes/No fields. X represents all of the other
fields.
This compares the absolute sum of a column to the count. If they are equal
then all are Yes for an out out of -1.
--
KARL DEWEY
Build a little - Test a little


:

I have a MTQ that I am creating. This is from a duplicates query that I ran.
I now want to condense the dups into one row. The problem is in the last 4
columns I have YES and NO in the cell. If one of them has a NO then I need
to have the condensed line have a NO. If between the two duplicates they are
both YES then I need the condensed row to have a YES and the same for the NO.
I have tried a few iIF statements but I am not getting the results I want.
Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Which of the fields are your Yes/No fields?

--
KARL DEWEY
Build a little - Test a little


T Miller said:
No sir I do not have them as "ALL OTHER FIELDS" I was trying to say do I put
all of the other field names here. Sorry, like I was saying I am trying to
say it the best way I can. Here is the SQL view of what I have, with out
what you said to put in it.

SELECT [All Duplicates IC Profile].FacID, [All Duplicates IC
Profile].Year, [All Duplicates IC Profile].Qtr, [All Duplicates IC
Profile].Facility, [All Duplicates IC Profile].City, [All Duplicates IC
Profile].State, [All Duplicates IC Profile].Zip, [All Duplicates IC
Profile].[Census Div], [All Duplicates IC Profile].[VHA Region], [All
Duplicates IC Profile].ProfID, Last([All Duplicates IC Profile].Department)
AS LastOfDepartment, [All Duplicates IC Profile].PTCApcnt, [All Duplicates
IC Profile].ElectroPcnt, [All Duplicates IC Profile].CompElectPcnt, [All
Duplicates IC Profile].IncPaceDefib
FROM [All Duplicates IC Profile]
GROUP BY [All Duplicates IC Profile].FacID, [All Duplicates IC
Profile].Year, [All Duplicates IC Profile].Qtr, [All Duplicates IC
Profile].Facility, [All Duplicates IC Profile].City, [All Duplicates IC
Profile].State, [All Duplicates IC Profile].Zip, [All Duplicates IC
Profile].[Census Div], [All Duplicates IC Profile].[VHA Region], [All
Duplicates IC Profile].ProfID, [All Duplicates IC Profile].PTCApcnt, [All
Duplicates IC Profile].ElectroPcnt, [All Duplicates IC
Profile].CompElectPcnt, [All Duplicates IC Profile].IncPaceDefib;

--
Thomas


KARL DEWEY said:
Here is the error I am getting "circular reference caused by alias
Click to expand...
'PTCApcnt' in query definition's SELECT list"
PTCApcnt: SELECT (IC Data), IIf(Abs(Sum([PTCAppcnt]))=Count([ALL OTHER
Click to expand...
FIELDS]),-1,0) AS Column_1,
You have an alias named the same as a field name - PTCApcnt - therefore the
circular reference.
Do you really have a field named [ALL OTHER FIELDS]?
What I post was a complete SQL statement of a query but it seems that you
pasted it as an output field in a query design view grid.
Post your query SQL and I or someone else will try to edit it for you.

--
KARL DEWEY
Build a little - Test a little


T Miller said:
Karl,

Sorry, I am just not getting it,

In the Field: I should have,

PTCApcnt: SELECT (IC Data), IIf(Abs(Sum([PTCAppcnt]))=Count([ALL OTHER
FIELDS]),-1,0) AS Column_1,
ElectroPcnt:IIf(Abs(Sum([ElectroPcnt]))=Count([ALL OTHER FIELDS]),-1,0) AS
Column_2,
CompElectPcnt:IIf(Abs(Sum([CompElectPcnt]))=Count([ALL OTHER FIELDS]),-1,0)
AS Column_3,
IncPaceDefib:IIf(Abs(Sum([IncPaceDefib]))=Count([ALL OTHER FIELDS]),-1,0) AS
Column_4 INTO [T_Miller-X], this part I don't undersand??
FROM T_Miller this part I don't understand??
GROUP BY T_Miller.X; this part I don't understand??

I appreciate your help with this, I knew this was not going to be easy and
it is not easy to articulate as well.

--
Thomas


:

Ok, here is the make table ---
SELECT T_Miller.X, IIf(Abs(Sum([C1]))=Count([x]),-1,0) AS Column_1,
IIf(Abs(Sum([C2]))=Count([x]),-1,0) AS Column_2,
IIf(Abs(Sum([C3]))=Count([x]),-1,0) AS Column_3,
IIf(Abs(Sum([C4]))=Count([x]),-1,0) AS Column_4 INTO [T_Miller-X]
FROM T_Miller
GROUP BY T_Miller.X;
--
KARL DEWEY
Build a little - Test a little


:

Try this -
SELECT T_Miller.X, IIf(Abs(Sum([C1]))=Count([x]),-1,0) AS Column_1,
IIf(Abs(Sum([C2]))=Count([x]),-1,0) AS Column_2,
IIf(Abs(Sum([C3]))=Count([x]),-1,0) AS Column_3,
IIf(Abs(Sum([C4]))=Count([x]),-1,0) AS Column_4
FROM T_Miller
GROUP BY T_Miller.X;

C1, C2, C3, and C4 are your 4 Yes/No fields. X represents all of the other
fields.
This compares the absolute sum of a column to the count. If they are equal
then all are Yes for an out out of -1.
--
KARL DEWEY
Build a little - Test a little


:

I have a MTQ that I am creating. This is from a duplicates query that I ran.
I now want to condense the dups into one row. The problem is in the last 4
columns I have YES and NO in the cell. If one of them has a NO then I need
to have the condensed line have a NO. If between the two duplicates they are
both YES then I need the condensed row to have a YES and the same for the NO.
I have tried a few iIF statements but I am not getting the results I want.
Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Here are the fields that are the yes/no fields.

[All Duplicates IC Profile].PTCApcnt, [All
Duplicates IC Profile].ElectroPcnt, [All Duplicates IC
Profile].CompElectPcnt, [All Duplicates IC Profile].IncPaceDefib;

--
Thomas


KARL DEWEY said:
Which of the fields are your Yes/No fields?

--
KARL DEWEY
Build a little - Test a little


T Miller said:
No sir I do not have them as "ALL OTHER FIELDS" I was trying to say do I put
all of the other field names here. Sorry, like I was saying I am trying to
say it the best way I can. Here is the SQL view of what I have, with out
what you said to put in it.

SELECT [All Duplicates IC Profile].FacID, [All Duplicates IC
Profile].Year, [All Duplicates IC Profile].Qtr, [All Duplicates IC
Profile].Facility, [All Duplicates IC Profile].City, [All Duplicates IC
Profile].State, [All Duplicates IC Profile].Zip, [All Duplicates IC
Profile].[Census Div], [All Duplicates IC Profile].[VHA Region], [All
Duplicates IC Profile].ProfID, Last([All Duplicates IC Profile].Department)
AS LastOfDepartment, [All Duplicates IC Profile].PTCApcnt, [All Duplicates
IC Profile].ElectroPcnt, [All Duplicates IC Profile].CompElectPcnt, [All
Duplicates IC Profile].IncPaceDefib
FROM [All Duplicates IC Profile]
GROUP BY [All Duplicates IC Profile].FacID, [All Duplicates IC
Profile].Year, [All Duplicates IC Profile].Qtr, [All Duplicates IC
Profile].Facility, [All Duplicates IC Profile].City, [All Duplicates IC
Profile].State, [All Duplicates IC Profile].Zip, [All Duplicates IC
Profile].[Census Div], [All Duplicates IC Profile].[VHA Region], [All
Duplicates IC Profile].ProfID, [All Duplicates IC Profile].PTCApcnt, [All
Duplicates IC Profile].ElectroPcnt, [All Duplicates IC
Profile].CompElectPcnt, [All Duplicates IC Profile].IncPaceDefib;

--
Thomas


KARL DEWEY said:
Here is the error I am getting "circular reference caused by alias
'PTCApcnt' in query definition's SELECT list"
PTCApcnt: SELECT (IC Data), IIf(Abs(Sum([PTCAppcnt]))=Count([ALL OTHER
FIELDS]),-1,0) AS Column_1,
You have an alias named the same as a field name - PTCApcnt - therefore the
circular reference.
Do you really have a field named [ALL OTHER FIELDS]?
What I post was a complete SQL statement of a query but it seems that you
pasted it as an output field in a query design view grid.
Post your query SQL and I or someone else will try to edit it for you.

--
KARL DEWEY
Build a little - Test a little


:

Karl,

Sorry, I am just not getting it,

In the Field: I should have,

PTCApcnt: SELECT (IC Data), IIf(Abs(Sum([PTCAppcnt]))=Count([ALL OTHER
FIELDS]),-1,0) AS Column_1,
ElectroPcnt:IIf(Abs(Sum([ElectroPcnt]))=Count([ALL OTHER FIELDS]),-1,0) AS
Column_2,
CompElectPcnt:IIf(Abs(Sum([CompElectPcnt]))=Count([ALL OTHER FIELDS]),-1,0)
AS Column_3,
IncPaceDefib:IIf(Abs(Sum([IncPaceDefib]))=Count([ALL OTHER FIELDS]),-1,0) AS
Column_4 INTO [T_Miller-X], this part I don't undersand??
FROM T_Miller this part I don't understand??
GROUP BY T_Miller.X; this part I don't understand??

I appreciate your help with this, I knew this was not going to be easy and
it is not easy to articulate as well.

--
Thomas


:

Ok, here is the make table ---
SELECT T_Miller.X, IIf(Abs(Sum([C1]))=Count([x]),-1,0) AS Column_1,
IIf(Abs(Sum([C2]))=Count([x]),-1,0) AS Column_2,
IIf(Abs(Sum([C3]))=Count([x]),-1,0) AS Column_3,
IIf(Abs(Sum([C4]))=Count([x]),-1,0) AS Column_4 INTO [T_Miller-X]
FROM T_Miller
GROUP BY T_Miller.X;
--
KARL DEWEY
Build a little - Test a little


:

Try this -
SELECT T_Miller.X, IIf(Abs(Sum([C1]))=Count([x]),-1,0) AS Column_1,
IIf(Abs(Sum([C2]))=Count([x]),-1,0) AS Column_2,
IIf(Abs(Sum([C3]))=Count([x]),-1,0) AS Column_3,
IIf(Abs(Sum([C4]))=Count([x]),-1,0) AS Column_4
FROM T_Miller
GROUP BY T_Miller.X;

C1, C2, C3, and C4 are your 4 Yes/No fields. X represents all of the other
fields.
This compares the absolute sum of a column to the count. If they are equal
then all are Yes for an out out of -1.
--
KARL DEWEY
Build a little - Test a little


:

I have a MTQ that I am creating. This is from a duplicates query that I ran.
I now want to condense the dups into one row. The problem is in the last 4
columns I have YES and NO in the cell. If one of them has a NO then I need
to have the condensed line have a NO. If between the two duplicates they are
both YES then I need the condensed row to have a YES and the same for the NO.
I have tried a few iIF statements but I am not getting the results I want.
Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

switch function or sumif? 1
Delete dups in a table 3
Deleting dups out of a table or query 1
Help with Query 2
How to Exclude Duplicates 7
Crosstab query (I think) 4
Find Duplicates with Different Yes/No values 3
IIF Statement in Query 2

Top