help with copying data from one table to another

[daD]

I'm trying to write a small database that tracks people coming and
going from a small campground. I need to have the current guests in
the "current" table" and then have the ability to check them out to
the "archive" table when they leave, by pushing the "check-out"
button. I see the steps as follows:

1. When the check-out button is pressed, the computer should
automatically add the current date into the "date checked-out" field.

The computer should then ask "are you sure?"

2. If the answer is "yes", then the computer should automatically
move the whole record from the "current" table to the "archive"
table.

If the answer is "no", then the computer should ease the date written
into the "date checked-out" field".

I am using Access 2003. I realize this is probably a macro, but I
guess it's beyond me. I need some real talent to help me. Any help
is greatly appreciated.
James

[pietlinden]

Why not just leave all the data in the same table and include a
CheckOutDate field? then your query would be something like

'--- untested code...

private sub cmdArchiveData()

dim intReply as integer '--- somewhere to stuff the response from the
MsgBox function.

intReply=MsgBox("Are you sure you want to archive this
record?",vbYesNo,"You're about to archive this record...")

if intReply=vbYes then
'---update only the current record on the form...
CurrentDB.Execute "UPDATE tblReservation SET CheckOutDate = Now
WHERE tblReservation.ReservationID=" &
Forms![MyForm]![txtReservationID]
end if

end sub

[Allen Browne]

Moving a record to another table consists of executing an Append query (to
add it to the other table), followed by a Delete query. The code you need to
achieve this is explained in this article:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html

However, it seems to me that this is not a relational design. Presumably you
have clients (who could come back some day), and lots (sections of the
camping), and you assign a lot to a client for a brief period. This suggests
3 tables:
Client (one record for each hirer) with fields:
ClientID AutoNumber
Surname Text family name
FirstName Text Christian name of the responsible person
Address Text person's home address
...

Lot (one record for each lot in your camping ground). Fields:
LotID Number
...

Hiring (one record for each time a client rents a lot.) Fields:
LotID Number which lot was hired
ClientID Number which client hired it
FirstNight Date/Time first night of this booking
FinalNight Date/Time last night of booking

Advantages of this approach:
- Client only has to be entered once, even if they have multiple bookings.
- Easy to query usage (history of a lot, what's available when.)
- No need to move records into another table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"daD" <JA********@COX.NETwrote in message
news:ab********************************@4ax.com...

I'm trying to write a small database that tracks people coming and
going from a small campground. I need to have the current guests in
the "current" table" and then have the ability to check them out to
the "archive" table when they leave, by pushing the "check-out"
button. I see the steps as follows:

1. When the check-out button is pressed, the computer should
automatically add the current date into the "date checked-out" field.

The computer should then ask "are you sure?"

2. If the answer is "yes", then the computer should automatically
move the whole record from the "current" table to the "archive"
table.

If the answer is "no", then the computer should ease the date written
into the "date checked-out" field".

I am using Access 2003. I realize this is probably a macro, but I
guess it's beyond me. I need some real talent to help me. Any help
is greatly appreciated.
James

[daD]

Sorry, should have mentioned that the former owner already had a
database built in access 2000 that has thousands of former customers.
I don't like this database, which is why i am trying to craete a new
one. This customer base is importnat to me for marketing efforts,
etc. I want to import that past data into my "archive" table and
query from there to support a direct marketing campaign designed to
lure former guests back for another stay. However, we're talking
about close to 20,000 people, so I don't want them cluttering up the
"curent" table, which only has a max numner 0f 812 (the number of
camping spots) That is why I need to copy from table to table.
Thanks so much for your help, Mr. Browne and everyone else who
answered.

James
On Sun, 3 Sep 2006 12:03:17 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

>Moving a record to another table consists of executing an Append query (to
add it to the other table), followed by a Delete query. The code you need to
achieve this is explained in this article:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html

However, it seems to me that this is not a relational design. Presumably you
have clients (who could come back some day), and lots (sections of the
camping), and you assign a lot to a client for a brief period. This suggests
3 tables:
Client (one record for each hirer) with fields:
ClientID AutoNumber
Surname Text family name
FirstName Text Christian name of the responsible person
Address Text person's home address
...

Lot (one record for each lot in your camping ground). Fields:
LotID Number
...

Hiring (one record for each time a client rents a lot.) Fields:
LotID Number which lot was hired
ClientID Number which client hired it
FirstNight Date/Time first night of this booking
FinalNight Date/Time last night of booking

Advantages of this approach:
- Client only has to be entered once, even if they have multiple bookings.
- Easy to query usage (history of a lot, what's available when.)
- No need to move records into another table.

[pietlinden]

If you design your database right and load only the records you need,
then the 20K records is largely immaterial. As a general rule, don't
base forms directly off tables. Especially if you're sharing the
backend across a network. The peformance will be horrible. If I were
you, I would create a new database and normalize first and then deal
with archived data. you could move it to an "archive" table, but then
you could just as easily base your form on a query...

SELECT <field>, <field>...
FROM <mytable>
WHERE MyTable.Archive=False

or some such thing that just essentially hides the archived data.
Otherwise, you have to muck with union queries, and that's not fun.

[daD]

OK, I think I am on the right track. All the forms are planned to be
based on queries off the 'current" table, except the 'past guests"
form, which is based off a query of the "archive" table, so I can see
who has stayed with us in the past.

i also have a need to know how many people are in the campground each
day. I would like the databse to run a query every day at maybe 8:00
(it should be the same time every day and should be automated) in the
morning and count the number of camping spaces that were occupied and
then store that number somewhere for later use. At the end of the
month I could run a report that lists that number for each day like:

1 800
2 799
3 811

Something simple like that. Is using a temporary table the way to go?
Something I could erase every month and start the whole thing over
again?

I have also been toying with the idea of using a web page as the
front-end. I think that there is so much more frredom in the design
with a web page and a lot of resources for graphics, etc.... Has
anyone ever done anything like that, or do you know of any utilities
that would make it easier to really make it look nice? Access seems a
littel short in that area. i guess I could design the background for
the forms in Frontpage or Photoshop and then put the fields in the
forground. Basically, I want my users accessing the database through
a browser, even though the whole thing (front-end and back-end) would
stay on my network. Tthanks agian to all you great people for sharing
your knowledge!

On 4 Sep 2006 21:09:41 -0700, pi********@hotmail.com wrote:

>If you design your database right and load only the records you need,
then the 20K records is largely immaterial. As a general rule, don't
base forms directly off tables. Especially if you're sharing the
backend across a network. The peformance will be horrible. If I were
you, I would create a new database and normalize first and then deal
with archived data. you could move it to an "archive" table, but then
you could just as easily base your form on a query...

SELECT <field>, <field>...
FROM <mytable>
WHERE MyTable.Archive=False

or some such thing that just essentially hides the archived data.
Otherwise, you have to muck with union queries, and that's not fun.

[pietlinden]

daD wrote:

OK, I think I am on the right track. All the forms are planned to be
based on queries off the 'current" table, except the 'past guests"
form, which is based off a query of the "archive" table, so I can see
who has stayed with us in the past.

i also have a need to know how many people are in the campground each
day. I would like the databse to run a query every day at maybe 8:00
(it should be the same time every day and should be automated) in the
morning and count the number of camping spaces that were occupied and
then store that number somewhere for later use.

On the right track? if you're thinking of putting all this calculated
junk in a table, I'd beg to differ. If you base your forms off queries
and your tables are properly indexed, and you only pull the data you
need from each table, then who cares how many records are in your
tables? Why not just query the live data? The query is a little
weird, but once you have it, you're there. I think storing all this
junk is a waste of space and time, unless you really need it for
querying later and you have so many records that querying the whole
dataset is unwieldy.

[daD]

OK, OK. Maybe not on the right track? I just need to know how many
camping sites were occupied on a given day and I thought I should
check that number at the same time every day to establish a standard.
This helps me establiush a trend of high occupancy times of the year
and low occupancy times of the year. Maybe I can offer incentives
during the slow months to lure customers in. How else would I be able
to get this info? You said somnething about indexes? I want to do it
the easiest way possible obviously, but I really want to use the past
records I inherited and that is over 31,00 records. Thanks again for
helping out.

.On 5 Sep 2006 17:22:56 -0700, pi********@hotmail.com wrote:

>
daD wrote:

>OK, I think I am on the right track. All the forms are planned to be
based on queries off the 'current" table, except the 'past guests"
form, which is based off a query of the "archive" table, so I can see
who has stayed with us in the past.

i also have a need to know how many people are in the campground each
day. I would like the databse to run a query every day at maybe 8:00
(it should be the same time every day and should be automated) in the
morning and count the number of camping spaces that were occupied and
then store that number somewhere for later use.

On the right track? if you're thinking of putting all this calculated
junk in a table, I'd beg to differ. If you base your forms off queries
and your tables are properly indexed, and you only pull the data you
need from each table, then who cares how many records are in your
tables? Why not just query the live data? The query is a little
weird, but once you have it, you're there. I think storing all this
junk is a waste of space and time, unless you really need it for
querying later and you have so many records that querying the whole
dataset is unwieldy.

[Bob Quintal]

daD <JA********@COX.NETwrote in
news:jg********************************@4ax.com:

OK, OK. Maybe not on the right track? I just need to know how
many camping sites were occupied on a given day and I thought
I should check that number at the same time every day to
establish a standard. This helps me establiush a trend of high
occupancy times of the year and low occupancy times of the
year. Maybe I can offer incentives during the slow months to
lure customers in. How else would I be able to get this info?
You said somnething about indexes? I want to do it the
easiest way possible obviously, but I really want to use the
past records I inherited and that is over 31,00 records.
Thanks again for helping out.

.On 5 Sep 2006 17:22:56 -0700, pi********@hotmail.com wrote:

>>
daD wrote:

>>OK, I think I am on the right track. All the forms are
planned to be based on queries off the 'current" table,
except the 'past guests" form, which is based off a query of
the "archive" table, so I can see who has stayed with us in
the past.

i also have a need to know how many people are in the
campground each day. I would like the databse to run a query
every day at maybe 8:00 (it should be the same time every
day and should be automated) in the morning and count the
number of camping spaces that were occupied and then store
that number somewhere for later use.

On the right track? if you're thinking of putting all this
calculated junk in a table, I'd beg to differ. If you base
your forms off queries and your tables are properly indexed,
and you only pull the data you need from each table, then who
cares how many records are in your tables? Why not just query
the live data? The query is a little weird, but once you have
it, you're there. I think storing all this junk is a waste of
space and time, unless you really need it for querying later
and you have so many records that querying the whole dataset
is unwieldy.

First of all, you should NOT have two tables, current and past
guests. You have one table "guests" and a field that indicates
the "past status", the date and time the guest left. It would
also help to store the date and time the guest arrived, and the
location number...

Then it becomes very easy to query the data for any particular
date and time for the info you want...
SELECT count(location) as Occupancy from Gusest where [enter a
date and time for count BETWEEN dtArrival and dtDeparture.

No need for automation, storing results, etc.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com