In Dire need of table manipulation help

[narpet]

Hello all.

I have a table in Access that has a check box as one of the fields. I want to write a query or code that will check or uncheck that box based on conditions met by other fields in the table. Here are the specifics as well as I can explain it:

I have the following table fields:

EmployerID
Group
Payroll
Yes/No

In the table I will have multiple instances of an EmployerID, one for each Group. Then there can be a monetary number in the Payroll field (or it can be 0). And the Yes/No field is the check box.

What I want to do is evaluate each group of records based on EmployerID.

If a given EmployerID has all ZEROs in the Payroll field for each Group I want to set the Yes/No to Yes(checked) for each record with that EmployerID.

If even one Payroll field for a given EmployerID has a value greater than ZERO I want to set the Yes/No to No(unchecked) for each record with that EmployerID (even those that have a ZERO Payroll).

I hope this makes sense. I don't care if it's done in a query or a module, as long as it works.

Any help will be greatly appreciated!
Thanks,
narpet

[MMcCarthy]

Hello all.

I have a table in Access that has a check box as one of the fields. I want to write a query or code that will check or uncheck that box based on conditions met by other fields in the table. Here are the specifics as well as I can explain it:

I have the following table fields:

EmployerID
Group
Payroll
Yes/No

In the table I will have multiple instances of an EmployerID, one for each Group.

Is your tables primary key EmployerID and Group together?

Then there can be a monetary number in the Payroll field (or it can be 0). And the Yes/No field is the check box.

What I want to do is evaluate each group of records based on EmployerID.

If a given EmployerID has all ZEROs in the Payroll field for each Group I want to set the Yes/No to Yes(checked) for each record with that EmployerID.

UPDATE TableName SET [Yes/No]="Yes"
WHERE Count(Group) = Count(IIf(Payroll=0,1,0))
AND EmployerID=<some value>;

If even one Payroll field for a given EmployerID has a value greater than ZERO I want to set the Yes/No to No(unchecked) for each record with that EmployerID (even those that have a ZERO Payroll).

Set the default value for [Yes/No] to "No"

[narpet]

Thanks for the help, but I'm not sure if this will work for me because I have to be able to change the Yes/No field without specifying an EmployerID. In other words, it needs to be able to make this change based on Group and EmployerID without calling a specific one...

example records:

EmployerID Group Payroll Yes/No
1 A $10.00
1 B 0
1 C 0
2 A 0
2 B 0
2 C 0

In this example I would want to set all of the Yes/No fields in the records for EmployerID 1 to No because one of those records had money in it.

I would want to set all of the Yes/No fields in the records for EmployerID 2 to Yes because none of those records have money in them.

I need to do it without having to specify EmployerID. There are thousands of records and it's not practical to specify any given criteria.

Any more help will be greatly appreciated.

Thanks,
narpet

[Killer42]

Ok, despite the "top ten" listing I'm not an Access expert - just a dabbler. My SQL knowledge is at a very simple level.

However, to me it sounds as though you might want an aggregate query grouping records by employee ID, with a count of non-zero payrolls, or a subtotal of payrolls. To that, you should be able to JOIN (don't know which type) based on the count (or subtotal) being zero.

Does this sound reasonable?

[NeoPa]

It does indeed Killer.
I just finished looking at this question and decided that it's something I can help with - when my brain is a little less tired than it is now.
If I haven't posted a helpful reply by tomorrow night (I hope you can wait that long), then you have my permission to remind me with a PM.

[Killer42]

...aggregate query grouping records by employee ID, with a count of non-zero payrolls, or a subtotal of payrolls. To that, you should be able to JOIN (don't know which type) based on the count (or subtotal) being zero.

Um... actually, I imagine the "not zero" would have to be applied to the aggregate query before doing the JOIN - help me out here, someone.

[NeoPa]

I don't know the name of your table so, for my answer, I'm using [tbl]. Replace all occurrences of this with your table name when you use it.

UPDATE [tbl] INNER JOIN (SELECT [EmployerID], (NOT Max([Payroll])) AS YN
FROM [tbl]
GROUP BY [Group]) AS subYN ON [tbl].[EmployerID] = [subYN].[EmployerID]
SET [tbl].[Yes/No] = [subYN].[YN]

Now, I suspect this may fail with an error message about being an non-updatable recordset.
If that is the case then we are a little stuck and probably have to do something with a temporary table.
I'm afraid I can't test this myself due to not having your data available (or time for that matter) so post back your test results if you would.

[narpet]

You sparked my imagination and I ended up doing it the simple way.

I used a query to create a temporary table to put all the records where the sum of the Payroll for a given EmployerID was 0.

Then I used that temporary table to update the Yes/No field based on the records in the temporary table.

Works like a charm.

Thanks to everyone for the help and ideas.

narpet

[Killer42]

You sparked my imagination and I ended up doing it the simple way.

If we provided ideas that inspired you to solve the problem for yourself, then that's probably the best result we could hope for. :)

Cheers!