yes/no checkmarks PLEASE HELP

[Guest]

I need to clear about 200,000 checkmarks in my database, does anyone know how
I can do this? I have tried REPLACE with (-1 with 0) and (Yes with No) but it
does not change the Checkboxes
Thanks,

 

[Rick B]

I posted a resopnse to your last post. Please stick to ONE THREAD PER
QUESTION.

In that post, I asked you for more details. Is this one FIELD in a table
(use database terms to describe your issue - don't say 'checkboxes'). If
this is a particular field in a record, then you would create an UPDATE
QUERY. In the query, you would set it up to pull all the records where the
field is TRUE and you would set the UPDATE TO: field to FALSE.

Unless you tell us your structure or give us a better description, you will
most likely not get a better answer. This is not a spreadsheet, it is a
database. You can't simply find and replace an entire database. Most
databases are made up of multiple tables with multiple fields in a table.
You have not told us yet if you are trying to change one particular field in
a table to be all false, or if you have several "checkboxes" per record.

Help us out here, buddy. We can't see your screen.

 

[Guest]

Sorry Rick, I reposted because I could not find the 1st post. I am a fireman
not a programer, just trying to do the best I can. In the Table, I have 4000
records, each with 60 colmns of yes/no checkboxes ( they ARE checkboxes)
with last years data in them. I copied & saved the data & now want to clear
the data to NO or FALSE for the entire Table. I have several computers &
PDAs that look to this Table for info.
Does this help?
Thanks,
--
Jack Peckham
Idyllwild Fire Protection District

I posted a resopnse to your last post. Please stick to ONE THREAD PER
QUESTION.

In that post, I asked you for more details. Is this one FIELD in a table
(use database terms to describe your issue - don't say 'checkboxes'). If
this is a particular field in a record, then you would create an UPDATE
QUERY. In the query, you would set it up to pull all the records where the
field is TRUE and you would set the UPDATE TO: field to FALSE.

Unless you tell us your structure or give us a better description, you will
most likely not get a better answer. This is not a spreadsheet, it is a
database. You can't simply find and replace an entire database. Most
databases are made up of multiple tables with multiple fields in a table.
You have not told us yet if you are trying to change one particular field in
a table to be all false, or if you have several "checkboxes" per record.

Help us out here, buddy. We can't see your screen.

 

[Guest]

Try using a simple update query. In the query design select all the fields
to update and then update them all to 'No' in a one off run.

Sorry Rick, I reposted because I could not find the 1st post. I am a fireman
not a programer, just trying to do the best I can. In the Table, I have 4000
records, each with 60 colmns of yes/no checkboxes ( they ARE checkboxes)
with last years data in them. I copied & saved the data & now want to clear
the data to NO or FALSE for the entire Table. I have several computers &
PDAs that look to this Table for info.
Does this help?
Thanks,

 

[John Vinson]

Sorry Rick, I reposted because I could not find the 1st post. I am a fireman
not a programer, just trying to do the best I can. In the Table, I have 4000
records, each with 60 colmns of yes/no checkboxes ( they ARE checkboxes)
with last years data in them. I copied & saved the data & now want to clear
the data to NO or FALSE for the entire Table. I have several computers &
PDAs that look to this Table for info.
Does this help?
Thanks,

Short answer:

Create a new Query based on your table. Change it to an Update Query
using the query type icon, or the Query menu option.

Select all sixty fields. On the Update To line under each of them type

False

or

0

Run the query by clicking the ! icon; Access will replace all the
values in all sixty yes/no fields with 0 (false, unchecked).

What you *SEE* is a checkbox. But the table does not contain
checkboxes! A table contains DATA - for a Yes/No field it contains -1
for Yes, 0 for No. You can choose to *display* -1 as a checked
checkbox, and 0 as an empty one - but that's a display tool, not data.

The longer (and better) answer: Redesign your table structure. Sixty
yes/no fields is CERTAINLY not properly normalized; you're storing
data in fieldnames. What are some of these fields? What is the meaning
of a checkbox? I'm quite sure that you have one or more one-to-many
relationships embedded in each record.


John W. Vinson[MVP]

 

[DL]

Perhaps I'm missing something here;
The data is specific to a year? but you now wish to clear it, having copied
the data elsewhere?
Surely simpler to create a field for the date, then filter by date whenever
neccessary?

Sorry Rick, I reposted because I could not find the 1st post. I am a fireman
not a programer, just trying to do the best I can. In the Table, I have 4000
records, each with 60 colmns of yes/no checkboxes ( they ARE checkboxes)
with last years data in them. I copied & saved the data & now want to clear
the data to NO or FALSE for the entire Table. I have several computers &
PDAs that look to this Table for info.
Does this help?
Thanks,

 

[Guest]

Thank You Thank You Thank You, that did the trick. I do need all the fields.
We use this form to do Fire Abaitment, we have 18 (A-R) boxes that corespond
with 18 Violations. we have to do it 3 times a year, so we have A-1, A-2,
A-3, B-1, ect. plus a few other thinga like "Fire Resistive Roof" "Water
Tank" we wnat to keep track of the old data to find trends. As far as I can
tell, we need all the fields. we sync this data onto PDAs to do the field
collection work & I dont want to have to re-sync the data to new DB each year
so I just copy, rename old & past the original back. All my sync's &
shortcuts still work.

Thanks again for the help.
Jack Peckham
Idyllwild Fire Protection District

 

[John Vinson]

Thank You Thank You Thank You, that did the trick. I do need all the fields.
We use this form to do Fire Abaitment, we have 18 (A-R) boxes that corespond
with 18 Violations. we have to do it 3 times a year, so we have A-1, A-2,
A-3, B-1, ect. plus a few other thinga like "Fire Resistive Roof" "Water
Tank" we wnat to keep track of the old data to find trends. As far as I can
tell, we need all the fields. we sync this data onto PDAs to do the field
collection work & I dont want to have to re-sync the data to new DB each year
so I just copy, rename old & past the original back. All my sync's &
shortcuts still work.

No. You do NOT NEED to store your data in a non-normalized
spreadsheet. In fact it's much better, and much more flexible and
searchable, to store your data in a normalized structure!

If you have 18 violations currently... can you be ABSOLUTELY certain
that you might not have 20 next year, as the laws and technology
change? Do you really want to restructure your entire database just to
add another type of violation?

If you have a many to many relationship between dates, sites, and
violations, I'd suggest that you need a table structure such as:

Violations
ViolationID
ViolationDescription

Sites
SiteID
Address
<other info about the site, owner etc.>

INspections
SiteID ' where was the inspection done
VisitDate ' when
Violation ' what was found to be in violation
Comments

This will make it MUCH MUCH easier to determine trends, search for
patterns of violation, count the number of violations at a given site,
and so on and so on.

Do consider restructuring your tables!

John W. Vinson[MVP]

 

[tina]

John is absolutely right - you'll be amazed at how much statistical
information you can get from your data if you normalize it - you can analyze
it till you wring it dry of every scrap of valuable info!

the only additional thing you might want to consider in the basic table
design is that many sites probably have multiple violations identified
during one inspection. if that's possible, you could replace the table

Inspections
SiteID ' where was the inspection done
VisitDate ' when
Violation ' what was found to be in violation
Comments

with two tables:

tblInspections
InspectionID
SiteID ' where was the inspection done
VisitDate ' when

tblInspectionViolations
InspectionID ' which inspection the violation belongs to
ViolationID
Comments

the Comments field could be in either tblInspections or in
tblInspectionViolations, whichever is more appropriate (or you could have a
Comments field in both tables, if you need to.)

hth