Move data from one record to another record

[Mrkeith]

Greetings Form!

I'm trying to get a DB to move records simultaneously using a command button. The records are setup with check boxes for example: one column has a check box for 1 Year Ratio the next column has a check box for 2 Year Ratio and the third column has a check box for 3 Year Ratio.

How can i move the information out from 3 Year Ratio into another table and then move 2 Year Ratio check into 3 Year Ratio check box and 1 Year Ratio check into 2 Year Ratio check box by using a command button.

I'm not sure how to set this up. But with your help I can.

Thanks in advance Form.

 

[plog]

Your database doesn't sound like it structured properly. Records usually don't get 'moved'. Instead, data in that record gets changed.

It sounds like YearRatio should be a numeric field in your table. Then, to change it from year to year, you would change the value in it to the new appropriate value (2->3).

If you want to post your table structure we can help move you to the correct one.

 

[Mrkeith]

Plog,

Thanks for responding. The DB is setup for check boxes. I just need to be able to change, move or copy the information from 1 Year Ratio to 2 Year Ratio and change, move or copy 2 Year Ratio to 3 Year Ratio. These are all check marks not a value.

The Attachment only highlights a few of the columns and lets use the examples that are listed. let say i want to copy the EMIS check mark to Licensed SPED Staff and then RTI to EMIS and Ratios to RTI. Is there a way to have this done?

 

[plog]

A check mark is a value. Do your ratio tables (1 Year, 2 Year, 3 Year, etc) all have the same structure?

If you wanted to copy one columns' values to another column you would use an UPDATE query.

 

[Mrkeith]

Plog,

Yes, 1 Year, 2 Year and 3 Year all have the same structure.

The answer to your second statement is yes I would like to use an UPDATE query, but how do you setup the query to run something like this?

 

[plog]

First, I would fix your table structure. Anytime you have numerated table or field names (Year1, Year2, Year3...) its a sign you've set something up incorrectly. Another sign is multiple tables with the same structure.

You shouldn't store data in the name of the table or field.
Instead of X number of ratio tables, you should have one ratio table with a field to capture X. That means those 3 tables should have their data merged into one table (Ratios) with an additional field where you would put the year the record is for.

The issue you are trying to solve is just a symptom of the larger problem. You need to fix the underlying issue or you are going to have to keep putting band-aids on this thing.

 

[Mrkeith]

Plog,

I think we are not on the same page here. My first question is did you take a look at the attachment i sent you?

The check marks only indicate either Yes or No that the category is compliant.

The table structure is fine, i just need to know how I could move the check marks into another column.

I guess my question is it possible to use an UPDATE query or use VBA code to perform this task?

 

[spikepl]

As Plog says, your table structure is very definitely NOT fine!

However, there is no normalization police that will prosecute/persecute you for this. The built-in penalty for unnormalized data structure like yours is eternal PITA, like you are experiencing now because you have to jump through weird hoops, and then every time you need to change something, or expand or maintain, or do some new query/report ... But then again, some people do like PITA

 

[plog]

Yes I did, and it didn't seem to match what you asked in your first post. Your first post mentioned moving data around 3 tables, your image only showed one datasource (it was actually a query, not a table), not the 3 data sources you mentioned using initially.

I am confused as if you want to move data from column to column within the same table, or from a column in one table to a column in another table.

However, if you have 3 tables with the same structure, your doing something incorrectly.

 

[Mrkeith]

I apologize for the weird post, but i'm just a little frustrated with the database. Plog, you are correct the attachment you received is a query that consist of 3 tables (Main, 2 Year Contrast and 3 Year Contrast) and the form is design to capture this information.

Ok, let me slow down here, so when you click in any of the (3) sections 1 Year, 2 Year or 3 Year I've setup a command button to move the 3 Year information to another table call 3 Year Contrast Transfer, which consist of using a UPDATE query to move this information. Now 3 Year Contrast check boxes are now empty on the form. This is ok! that's what i want.

But now I need to have 2 Year Contrast check marks moved into 3 Year Contrast check boxes and 1 Year Contrast check marks move into 2 Year Contrast check boxes. I hope this clears up the mis-communication. Here is also an attachment of the DB Form.

Thanks to also spikepl for your reply as well. Again I apologize for being so short.

 

[plog]

You didn't come off as short with me and even if you did, don't worry about it, we've all spent hours on things that turn out to be simple once you know what to do. Programming can be frustrating.

However, I'm going to have to frustrate you more, and stick by my advice and tell you to properly normalize this thing. What you want becomes trivial (your form will have to change), if your database is structured properly. Instead of moving records, you will simply update a field in it. You should have 1 table as I mentioned above, with a new field that tells you what year that data is for. Then to 'move' the data from Year 1 to Year 2, you change the value in that year field to 2.

This is a structure issue, not a form, query or VBA issue.

 

[Mrkeith]

Plog,

Thanks for all your help. I really didn't want to hear i need to change the form, but i new that was coming. Well, I guess i need to get started. Again thanks so much for your help.

I can always count on the forum to help me.


Thanks again!

 

[plog]

Can you provide sample data? If you can upload database that would help. Or just send a screenshot of the structure of the tables in question.

 

[Mrkeith]

Plog,

This DB is huge! I would need to send this to you using We Transfer. Can you send me your email address?

Thanks.

 

[plog]

Honestly, the best advice I can give you is to either hire someone to straighten it all out, or pick up a database book. What you sent me confirmed that you have an improperly structured database--and you have more issues than I intuited.

I'll speak to the issue I've been parroting throughout this post--tables with the same structure, shouldn't exist, instead you should have all the data in the same table, with a new field to differentiate the data.

Let's take those visit tables you have (1 Visit, 2 Visit, 3 Visit). For brevity's sake let's say this is their structure:

1 Visit
ID, 1 Visit ID, School Name, 1 Visit Ratios, 1 Visit RTI

2 Visit
ID, 2 Visit ID, School Name, 2 Visit Ratios, 2 Visit RTI

3 Visit
ID, 3 Visit ID, School Name, 3 Visit Ratios, 3 Visit RTI

Instead of those 3 tables, you should have one table with this structure:

Visits
ID, Visit ID, School Name, Visit Ratios, Visit RTI, VisitNum

All the fields are the same (with the exception of being prefixed with a number), and there is a new field called VisitNum, where that number will now go. That 1 table will replace the old 3.

It looks like you have multiple instances of this issue (State Performance Plan Indicators tables, Notes DB tables).

 

[Mrkeith]

Plog,

Thanks for all your help on this issue. I figure it out. Thanks again!