 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / May 2005yes/no checkmarks PLEASE HELP
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,  SignatureJack 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. SignatureRick B > 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, 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, SignatureJack Peckham Idyllwild Fire Protection District > I posted a resopnse to your last post. Please stick to ONE THREAD PER > QUESTION. [quoted text clipped - 20 lines] > > does not change the Checkboxes > > Thanks, 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 [quoted text clipped - 29 lines] > > > does not change the Checkboxes > > > Thanks, >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 [quoted text clipped - 4 lines] >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] 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 > >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 [quoted text clipped - 33 lines] > > John W. Vinson[MVP] >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 [quoted text clipped - 5 lines] >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] 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 > >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 [quoted text clipped - 40 lines] > > John W. Vinson[MVP] 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 [quoted text clipped - 29 lines] > > > does not change the Checkboxes > > > Thanks, |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.