Delete Records Then Append

[Guest]

Here is the situation I am stuck in. I have to import "txt" files on a regular basis. I have set up access to do this automatically. I now have a new problem. I am being asked if we can update more often. Normally I update once ever month. Now I need to update once every week. Doing this will create a problem because the "txt" files only get reset once a month, thus lots of duplicate data. The only way I have come up to take care of this, is something that I don't know how to do

I can't simply use an append query that removes duplicates because there may be some duplicates that are valid. What I think I can do but need some help on is, I have set up a blank table called "ImportData" to import the new data into. I have about 18 fields but two that I need to look at, these are a field for Month/Year and Location. If I can somehow get
the value from the month/year field (these will be the same for each record because these are monthly reports
the value from the location field (these will be the same for each record since there is a seperate file for each location

Then I will need to use these two value in the "StoredData" table. I will need to delete any record that has these month/year and location values, then I can append the value from the "ImportData" table to the "StoredData" table.

This is at least my thought process on how I can do this, but I am not sure how or if this can be done. I could really use some help on this one

Thanks

Mike Altma
V.P. Operation
The Competitive Edge

 

[Guest]

Ok I think I have gotten closer, although I am not sure, although I am not new to Access, I am new to doing more powerful things with it. I have set up a delete query that will bring up a box prompting me for a location number and a date, and then it will delete all of the records that meet those criteria. Now how do I set it up so that those two values that I have to input are automatically taken from the fields in another table. There will me many records for each of the fields, but for these specific two they will all be the same

----- Mike Altman wrote: ----

Here is the situation I am stuck in. I have to import "txt" files on a regular basis. I have set up access to do this automatically. I now have a new problem. I am being asked if we can update more often. Normally I update once ever month. Now I need to update once every week. Doing this will create a problem because the "txt" files only get reset once a month, thus lots of duplicate data. The only way I have come up to take care of this, is something that I don't know how to do

I can't simply use an append query that removes duplicates because there may be some duplicates that are valid. What I think I can do but need some help on is, I have set up a blank table called "ImportData" to import the new data into. I have about 18 fields but two that I need to look at, these are a field for Month/Year and Location. If I can somehow get
the value from the month/year field (these will be the same for each record because these are monthly reports
the value from the location field (these will be the same for each record since there is a seperate file for each location

Then I will need to use these two value in the "StoredData" table. I will need to delete any record that has these month/year and location values, then I can append the value from the "ImportData" table to the "StoredData" table.

This is at least my thought process on how I can do this, but I am not sure how or if this can be done. I could really use some help on this one

Thanks

Mike Altma
V.P. Operation
The Competitive Edge

 

[Ken Snell]

In place of the parameters that you have put in the query (example, "[Enter
the value:]") as the criteria, you can use the DLookup function in an
expression to read the value from a record in a table (or query, for that
matter). Syntax would be something like this:

=DLookup("FieldNameToGet", "TableOrQueryName")

You also can use "WHERE" type expressions in the third argument of the
function to further filter. Note that DLookup will find the "first"
(meaning, whichever record ACCESS first finds that matches the request) --
which may not be the one you want -- so if you have more than one record in
the table or query be sure to use a WHERE statement clause to limit the
records that can be "found".

Check out DLookup in Help files, and post back if you have additional
questions about this.


--
Ken Snell
<MS ACCESS MVP>

Ok I think I have gotten closer, although I am not sure, although I am not

new to Access, I am new to doing more powerful things with it. I have set
up a delete query that will bring up a box prompting me for a location
number and a date, and then it will delete all of the records that meet
those criteria. Now how do I set it up so that those two values that I have
to input are automatically taken from the fields in another table. There
will me many records for each of the fields, but for these specific two they
will all be the same.

----- Mike Altman wrote: -----

Here is the situation I am stuck in. I have to import "txt" files on

a regular basis. I have set up access to do this automatically. I now have
a new problem. I am being asked if we can update more often. Normally I
update once ever month. Now I need to update once every week. Doing this
will create a problem because the "txt" files only get reset once a month,
thus lots of duplicate data. The only way I have come up to take care of
this, is something that I don't know how to do.

I can't simply use an append query that removes duplicates because

there may be some duplicates that are valid. What I think I can do but need
some help on is, I have set up a blank table called "ImportData" to import
the new data into. I have about 18 fields but two that I need to look at,
these are a field for Month/Year and Location. If I can somehow get:

the value from the month/year field (these will be the same

for each record because these are monthly reports)

the value from the location field (these will be the same for

each record since there is a seperate file for each location)

Then I will need to use these two value in the "StoredData" table. I

will need to delete any record that has these month/year and location
values, then I can append the value from the "ImportData" table to the
"StoredData" table.

This is at least my thought process on how I can do this, but I am

not sure how or if this can be done. I could really use some help on this
one.

 

[Guest]

Thank you so much, that seems to work great. I do have one additional question to add though. Is there a way to make it loop though all the records when I do DLookUp, that would be an even quicker way to do what I need done. Right now it will look in the first record and get the value for month/year and for location. Then it deletes them, which is exactly what I want. But now is there a way to have it go down to the next record and do this same process for all records in the table

My thought here is I have upwards of 45 locations, I can set up excel to get the location name and the month/year value from each .txt file, then import this file to an access table

So basically is there a way to have this query do this delete operation for each record set in say "Table1". I don't know if this would require VB or not

Any further assistance would be great, and thank you for what you have given me so far

Mik



----- Ken Snell wrote: ----

In place of the parameters that you have put in the query (example, "[Ente
the value:]") as the criteria, you can use the DLookup function in a
expression to read the value from a record in a table (or query, for tha
matter). Syntax would be something like this

=DLookup("FieldNameToGet", "TableOrQueryName"

You also can use "WHERE" type expressions in the third argument of th
function to further filter. Note that DLookup will find the "first
(meaning, whichever record ACCESS first finds that matches the request) --
which may not be the one you want -- so if you have more than one record i
the table or query be sure to use a WHERE statement clause to limit th
records that can be "found"

Check out DLookup in Help files, and post back if you have additiona
questions about this


--
Ken Snel
<MS ACCESS MVP

Ok I think I have gotten closer, although I am not sure, although I am no

new to Access, I am new to doing more powerful things with it. I have se
up a delete query that will bring up a box prompting me for a locatio
number and a date, and then it will delete all of the records that mee
those criteria. Now how do I set it up so that those two values that I hav
to input are automatically taken from the fields in another table. Ther
will me many records for each of the fields, but for these specific two the
will all be the samea regular basis. I have set up access to do this automatically. I now hav
a new problem. I am being asked if we can update more often. Normally
update once ever month. Now I need to update once every week. Doing thi
will create a problem because the "txt" files only get reset once a month
thus lots of duplicate data. The only way I have come up to take care o
this, is something that I don't know how to dothere may be some duplicates that are valid. What I think I can do but nee
some help on is, I have set up a blank table called "ImportData" to impor
the new data into. I have about 18 fields but two that I need to look at
these are a field for Month/Year and Location. If I can somehow get

the value from the month/year field (these will be the sam

for each record because these are monthly reports

the value from the location field (these will be the same fo

each record since there is a seperate file for each locationwill need to delete any record that has these month/year and locatio
values, then I can append the value from the "ImportData" table to th
"StoredData" table.not sure how or if this can be done. I could really use some help on this
one.

 

[Ken Snell]

I am not sure that I fully understand what you seek, but it appears that you
want to use all the records in the table that is being used in the DLookup
function when you want to do the deletions?

I probably would do this via VBA code, as it would allow various
approaches - such as building one long "WHERE" statement from the records in
the table and then running a single delete query, or such as looping through
each record in the table and doing a delete query for each record.

Can you post more info about the exact setup of this "lookup" table? Also
post an example of the SQL statement for the delete query so that we can see
your setup more clearly.


--
Ken Snell
<MS ACCESS MVP>

Thank you so much, that seems to work great. I do have one additional

question to add though. Is there a way to make it loop though all the
records when I do DLookUp, that would be an even quicker way to do what I
need done. Right now it will look in the first record and get the value for
month/year and for location. Then it deletes them, which is exactly what I
want. But now is there a way to have it go down to the next record and do
this same process for all records in the table.

My thought here is I have upwards of 45 locations, I can set up excel to

get the location name and the month/year value from each .txt file, then
import this file to an access table.

So basically is there a way to have this query do this delete operation

for each record set in say "Table1". I don't know if this would require VB
or not.

Any further assistance would be great, and thank you for what you have given me so far.

Mike





----- Ken Snell wrote: -----

In place of the parameters that you have put in the query (example, "[Enter
the value:]") as the criteria, you can use the DLookup function in an
expression to read the value from a record in a table (or query, for that
matter). Syntax would be something like this:

=DLookup("FieldNameToGet", "TableOrQueryName")

You also can use "WHERE" type expressions in the third argument of the
function to further filter. Note that DLookup will find the "first"
(meaning, whichever record ACCESS first finds that matches the request) --
which may not be the one you want -- so if you have more than one record in
the table or query be sure to use a WHERE statement clause to limit the
records that can be "found".

Check out DLookup in Help files, and post back if you have additional
questions about this.


--
Ken Snell
<MS ACCESS MVP>

Ok I think I have gotten closer, although I am not sure, although I

am not
new to Access, I am new to doing more powerful things with it. I have set
up a delete query that will bring up a box prompting me for a location
number and a date, and then it will delete all of the records that meet
those criteria. Now how do I set it up so that those two values that I have
to input are automatically taken from the fields in another table. There
will me many records for each of the fields, but for these specific two they
will all be the same. files on
a regular basis. I have set up access to do this automatically. I now have
a new problem. I am being asked if we can update more often. Normally I
update once ever month. Now I need to update once every week. Doing this
will create a problem because the "txt" files only get reset once a m onth,
thus lots of duplicate data. The only way I have come up to take care of
this, is something that I don't know how to do. because
there may be some duplicates that are valid. What I think I can do but need
some help on is, I have set up a blank table called "ImportData" to import
the new data into. I have about 18 fields but two that I need to look at,
these are a field for Month/Year and Location. If I can somehow get:

the value from the month/year field (these will be the

same
for each record because these are monthly reports)

the value from the location field (these will be the

same for
each record since there is a seperate file for each location) table. I
will need to delete any record that has these month/year and location
values, then I can append the value from the "ImportData" table to the
"StoredData" table. I am
not sure how or if this can be done. I could really use some help on this

one.
V.P. Operations
The Competitive Edge