how do i update records

[Guest]

I have been using Acces for a while now, but would still consider myself a
new user.
I've got a database that i add records to via Frontpage. What i really want
to do is update an existing record, and if it doesn't exist add it to the
database.

As frontpage is a bit I think that it would be easier to Just keep adding
records, but create a routine in access where all records are looked at and
the last entry for each record is kept and all older entries are deleted. i
could run this routine once a month.

what i need to know is how do i go about creating this routine, or even
better is there already such a routine made up that i can alter to suit my
needs.


Al....

 

[John Spencer]

Are you storing the datetime when you last updated a record in the record?

In other words, do you have a way to identify the last entry and a way to
identify the grouping of records?

 

[Guest]

yeah i'm storing the date and time, i'm currently using a query that shows
the last entry per item, but as more entry's are added the database takes
longer to query. I only really need to know the last entry per item, and
therefore can delete all duplicate older entries.


Al....

 

[John Spencer]

Well if you have a query that shows the last entry per item then you can use
it to populate a table with just the current data.
Copy the structure of your current table.
Use the query you have to Append records to the new table.
Delete the old table
Rename the current table

OR

You can also use your query to Delete the other records in the current
table.

Use the table and the query to do an unmatched query (see the query wizard)
on the table. DELETE all the unmatched records in the table.

 

[Guest]

Sounds a bit complicated, but i'll give it a go, Good thing i got an Access
Inside Out book.
Hopefully i'll get it.

Thanks for your help


Al....

 

[John Spencer]

Post back if you don't solve this.

When you do post back include a copy of the SQL text (Select View: SQL) from
the menu. That will make it easier for someone to help you develop a
solution.

 

[Guest]

cheers john, i can get it to show the records i dont want by doing the
unmatched query as you suggested, however any attempt to modify the SQL
statement results in an error when run, usually that i need to modify the
statement to delete one record at a time which isn't really any use.

here is the SQL form the unaltered query

SELECT resultscopy1.*
FROM resultscopy1 LEFT JOIN CurrentLocation ON
resultscopy1.ID=CurrentLocation.ID
WHERE (((CurrentLocation.ID) Is Null));

I tried putting the whole thing in brackets and adding
DELETE * FROM resultscopy1 WHERE
at the start but still got errors

I'll give it a look later as were kindof snowed under with work.

Thanks for your help

Al....

 

[Guest]

Wooo Hooo I got it going, took a lot of messing about searching the internet
but i got it to delete the records i dont want.

So the table that i store all records is Results
The Query that shows all Latest records Per Item is CurrentLocation1
And the Delete Unmatched Query is

DELETE Results.*
FROM Results
WHERE Results.ID IN (SELECT Results.ID
FROM Results LEFT JOIN CurrentLocation1 ON Results.ID = CurrentLocation1.ID
WHERE (((CurrentLocation1.ID) Is Null));

Just one thing though, before deleteing the unmatched records the file size
was 11,920 KB and after deleting Approx 32,000 records and leaving Approx
4,000 records the file size got bigger 11,952 KB.

Certainly the searching of records is quicker but i thought the file size
would also reduce, since it said that i can't undo the delete.

Al....

 

[John Spencer]

The space is not recovered until you compact the database.

 

[Guest]

So how do i do that?.

The space is not recovered until you compact the database.

 

[Craig Alexander Morrison]

check the Tools Menu, Database Utilities.

...and perhaps read the manual/help file for a better understanding of the
product.

 

[Guest]

Thanks craig, i would have read the Access 2003 Inside out book that i bought
but i left it in the house, which is why i asked the question.

Thanks for the answer anyway.

I do admit that sometimes that it is easier to ask questions when i should
actually be doing a bit of research, but i'd say that we are all guilty of
that sometimes.

Thanks

Al....

 

[Craig Alexander Morrison]

Thanks craig, i would have read the Access 2003 Inside out book that i

bought
but i left it in the house, which is why i asked the question.

That's a decent intro to the product, there is a complete text of the book
(in PDF I believe) on the CD. Keep that handy for quick reference when you
do not have the physical book and do have access to a PC.