Access Database help

[Charles L. Cotton]

I hope we have some Access database experts here, because I need some help. Without getting into too much detail, here is the problem.

I periodically import a database into Access and create a Query. That Query is used in a merge print document in Word. I need to make sure that I don't duplicate recipients of the merge print document when a new database is imported and a new Query is created using the same or similar criteria.

I can add a field to the database and "manually" enter something indicating a document had previously been created for that record, but I'd sure like to automate this procedure. I don't know, but I suspect a macro could be used if I was using the same database. However, I don't have any idea if a macro could be created that takes the results of a Query on database-1 and uses it to locate and edit records in database-2.

Any thoughts?

Thanks,
Chas.

[kd5zex]

Without seeing your DB I can't give you a specific example but it should be as simple as adding the field in your DB and then set the field to -1 when you run your query.

Also, you will need to add a SELECT WHERE printed = 0 to your query to keep the ones that have already been printed from being printed again.

Let me know if I have misunderstood your objective.

[Charles L. Cotton]

Without seeing your DB I can't give you a specific example but it should be as simple as adding the field in your DB and then set the field to -1 when you run your query.

Also, you will need to add a SELECT WHERE printed = 0 to your query to keep the ones that have already been printed from being printed again.

Let me know if I have misunderstood your objective.

You understand my objective perfectly. Your suggestion will work with a "static" database, but I don't know how to do this with a new database I import periodically. I'll be losing the data in the new field I add to store the print history. I could create a new table in the DB and use it to store the print history, but can that table be imported to the new database? (Every database I import will have unique identifier for each record and that will be the same across all databases.)

Chas.

[kd5zex]

The addition of a printed table will work great as well provided you maintain the unique ID. You can import tables, queries from other DBs through "File", "Get External Data", "Import". Or you can open both DBs and drag and drop the required tables or queries. Then your print query will just need exclude all records that match an ID in the printed table.

ETA: Is there a reason why you are creating new DBs rather than appending to an existing table or adding a new table?

[TexasComputerDude]

I'm sure you can't show us the data but if you were to give us a sample database with fake names it might help. I'm fairly good at access but I have to be sitting in front of it.

Generally I just let my fingers do their thing LOL.

[Charles L. Cotton]

ETA: Is there a reason why you are creating new DBs rather than appending to an existing table or adding a new table?

I'm importing a .txt file that contains membership information, including their status and member number. Members' status, address, phone number, etc. change periodically, so I need a new database to get the updated information.

Chas.

[Kalrog]

One thing... I am usually not a fan of just putting flags in there. A date field usually works much better. If the date is filled out, you have the flag set to yes, but you might need to know when you sent it last for some reason (maybe the contents of the form printed change for instance). So I would say use a date field in the printed table instead of the printed "flag".

Also, why not use a sequence of delete/update/insert statements instead of dropping and starting over? You can import your data from you txt file into a temporary table that only sticks around until you have your main one updated.

So it would be something like "Delete from PermTable where ID not in (select ID from TempTable)"
Followed by "Update PermTable Set <PermTable.Field> = <TempTable.Field> from PermTable inner join TempTable on PermTable.Id = TempTable.Id"
Followed by "Insert Into PermTable <fields> Select <Fields> from TempTable where Id not in (Select Id from PermTable)"

[Charles L. Cotton]

One thing... I am usually not a fan of just putting flags in there. A date field usually works much better. If the date is filled out, you have the flag set to yes, but you might need to know when you sent it last for some reason (maybe the contents of the form printed change for instance). So I would say use a date field in the printed table instead of the printed "flag".

Also, why not use a sequence of delete/update/insert statements instead of dropping and starting over? You can import your data from you txt file into a temporary table that only sticks around until you have your main one updated.

So it would be something like "Delete from PermTable where ID not in (select ID from TempTable)"
Followed by "Update PermTable Set <PermTable.Field> = <TempTable.Field> from PermTable inner join TempTable on PermTable.Id = TempTable.Id"
Followed by "Insert Into PermTable <fields> Select <Fields> from TempTable where Id not in (Select Id from PermTable)"

This sounds promising. I knew you could do things like this is SQL, but I didn't know I could in Access.

I could keep a permanent database that included the mailing history, if I can update it from a temporary table and do the following:

• Delete anyone who is no longer a member (i.e. not in the temporary table);
  Add new members (i.e. not in the permanent database, but is in the temporary table);
  Update any changed address, phone number and status.

Is all of this possible in Access?

Thanks,
Chas.

[kd5zex]

Is all of this possible in Access?

Thanks,
Chas.

Access can do all that and then some, Access is only a front end to a SQL queryable database. When you make a query using the Access " query designer" you are actually writing a SQL statement. You can see this by opening or creating a query and changing to the "SQL view" under the View menu.

If you really wanted to get into it, you could create an Access application complete with vb scripts and buttons to manipulate and retrieve your data.