Is it even possible...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey, I'm quite new to Access and was looking for some help. I was wondering
if this is actually possible to do in Access:

Is it possible to place a button on a form that copies the record the person
is viewing and adds it to a different table (therefore deleting it on the
table they were viewing it from.)? The other table has the exact same
structure, just different records.

Is this even possible? What would it involve?

I'm sorry if I put this in the wrong place I wasn't sure.

Thanks in advance.
 
Hey, I'm quite new to Access and was looking for some help. I was wondering
if this is actually possible to do in Access:

Is it possible to place a button on a form that copies the record the person
is viewing and adds it to a different table (therefore deleting it on the
table they were viewing it from.)? The other table has the exact same
structure, just different records.

Is this even possible? What would it involve?

I'm sorry if I put this in the wrong place I wasn't sure.

Thanks in advance.
Click to expand...

It sounds very possible, but why do you need to do it this way? If both tables
have the exact same structure, as you say, then why not keep everything in one
table but add an additional column which would more or less signify which
"table" they are in?

For example, assume you have a table called "People" which is a list ofpeople
and the states they live in. For simplicity's sake, you might have just these
three columns: Lastname, Firstname, State. When you want to view only people who
live in California, you would create a select query like this:

SELECT Lastname, Firstname FROM People WHERE State="CA";

(assuming that only the abbreviation is being stored in the column "State").
Then you could make "CA" a parameter which would mean that whenever the query is
run, you could replace "CA" with a different state.

But judging from your above question, it sounds like you are trying to keep a
separate *table* for each state! This would be a nightmare to maintain, even if
you only had two or three such tables. If you could give us some more details, I
am sure that someone will be able to help you come up with a better design than
copying and deleting records in several tables.
 
rednikcam,
Given that the record your viewing has a unique key value, the button would first
initiate an Append query from table1 to table2.
Then the code could continue on, and delete the record in table1 that you are viewing
at that time.

Since you really didn't explain what "logic" is involved in the records in table1 vs.
table2, I'd suggest you consider this...
It would be unusual to have 2 tables in one mdb with exactly the same table structure.
It would be more "normal", to have all the records in one table, but differentiated by
some field value. With the example of table1 vs. table2, if all the records were in one
table, they could be differentiated by a field like "Type" for example.
RecID PartNo Type
1425 X91-245 1
1762 Y314-w1 1
8182 A816720 2
8817 B2423-1 1
87225 D-1429-1 2

This would allow you to treat one table as two, according to the Type number. As a
matter of fact, one form could handle all the records... by filtering for "1" or "2"
accordingly.
Given the problem you stated, with this setup, duplicate the separate tables Append and
Delete, you would simply edit Type from 1 to 2, or from 2 to 1.

Think about that concept... as of now, there's no reason I can see to have two separate
tables.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."
 
rednikcam said:
Hey, I'm quite new to Access and was looking for some help. I was
wondering
if this is actually possible to do in Access:

Is it possible to place a button on a form that copies the record the
person
is viewing and adds it to a different table (therefore deleting it on the
table they were viewing it from.)? The other table has the exact same
structure, just different records.

Is this even possible? What would it involve?

I'm sorry if I put this in the wrong place I wasn't sure.

Thanks in advance.
Click to expand...

It is possible, yes, but a futile exercise IMHO. The first question to ask
yourself is why you have two identical tables. A normalised solution would
be to have the one table with a flag field to differentiate between the two
variations of data. For example, if your "second" table was to contain
records deleted from your "first" table, then have one table with a
"Deleted" Yes/No field type. Then, instead of deleting from one table and
appending to the other, you would simply set the flag.

The immediate benefits I can think of are only having one table to maintain
and less file-size bloat.

HTH - Keith.
www.keithwilby.com
 
Okay I understand the point you've all made. Thank you, I will take your
advice.

Thanks again.
 
Back
Top