entering updated data in a table without deleting existing same ty

[saqi]

HI, I am a new user of MS Access and looking for help desperately.
I have so many questions but right now would ask only one. The data i am
working at is a customer mailing list. i have made following tables.
tblCompanyInfo( CompanyName, Address, City, State, Zip, ContactFirstName,
LastName). In this table company name is set as a primery Key.
tblMailingDetails ( CompanyName, NumberOfMailSent, MailSentDate, Response).
in this table company name is a foreign key.
I want to be able to keep mailing history, in order to update records i am
trying to run an upate query with these fields, CompanyName(from
tblCompanyInfo), NumberofMailsent (from tblMailingHistory) and MailSentDate
from (tblMailHistory).
Problem is when i run query it basically updates existing history rather
than adding new number of mail and mail sent date, Please tell me if i am
doing it wrong and what is the right way to do it.... I will highly apriciate
your help ...

 

[Allen Browne]

Try running an Append query rather than an Update query to add new mailings
to your related table.

 

[saqi]

Thanks much for a quick reply Allen. Would you please walk me through this a
little , i am doing it and some how it is not working... thanks again

 

[Allen Browne]

1. Open your query in design view.
Presumably this query is based on the table that has the new values (the
ones you want to append.)

2. On the Query menu, choose Append.
Answer the question about which table to append the new records to.
Access adds a new row to the design grid.

3. Map the fields so that the values are read from the existing data table,
and written to the matching field in the target table.

4. Run the query.

 

[saqi]

Allen, Thanks much for getting back to me, I was checking out your site and
am Impressed with your Qualifications and long history of work with Access
Data base.
I do not have a new table to append data from, let me explain a little more.
in my tblMailingHistory, Rather then updating the dates (where i can use
update query and which will infact delete old dates and add new date) I want
to add new date (so old date history can stay and viewer can view all the
dates) just to refresh your memory, my first and main table is
tblCompanyInfo with( CompanyName, Address, City, State, ZipCode,
ContactFname, LastName, SalesPerson). Second table is tblMailingHistory with
( CompanyName, NumberofMailing, DateofMailing)
I believe i need to run a query with tblMailingHistory which will add new
number of mailing and mailing date in to the table without deleting previous
history, I dont know which query will be helpful here.?. thanks for your
patience.

 

[Allen Browne]

Oh: your tblMailingDetails does not contain a record for each time you mail
the company? Can I encourage you to change that design?

tblCompany:
CompanyID AutoNumber(primary key),
CompanyName Text
Address Text
City Text
'etc

tblMailing (one record each time you do a mass-mailing):
MailingID AutoNumber
MailingDate Date/Time
MailingDescrip Memo (description)

tblMailingCompany (one record for each company in each mailing):
MailingID Number relates to tblMailing.MailingID
CompanyID Number relates to tblCompany.CompanyID
The combination of both fields is the primary key in this table.
So, if Mailing 25 was sent to companies 2, 6, and 99, you would have these
records:
MailingID CompanyID
25 2
25 6
25 99

Now adding a new mailing is just a matter of executing an Append query to
add the records to tblMailingCompany.

You can also easily do things like:
- Create a Totals query that gives you the count of mailings and last date
for each company (output looks similar to the table you have.)
- Undo a mailing to redo it (just by deleting the MailingID, and letting
cascade-delete remove all the related entries in tblMailingCompany.)
- Show a complete history of your mailings to a company.
- Add other fields that let you calculate the effectiveness of your mailings
over time, or compare one year/month to another.

But the most important thing is that the data is demonstrably correct. If
you just store the NumberOfMailSent, how can you show this number is right?
How can you maintain that as mailings are added or removed? If something
goes wrong (e.g. an update to this table fails because another user is using
that data at the time), will the count gradually become more and more wrong
over time?

 

[saqi]

Great, Thanks Much Allen, I made changes in the tables and re-established
relationships. just one last question, what are the fields i need to add in
to the append query to add mailing dates ..? I apriciate all of your help ....

 

[Allen Browne]

You will execute an Append query statement, based on some way of selecting
the companies to include in the mailing.

Firstly, OpenRecordset on tblMailing, AddNew, Update, and get the new
MailingID.

Then, assuming this new number is in the variable lngMailingID, the append
query statement will be executed like this:
Dim strSql As String
strSql = "INSERT INTO tblMailingCompany ( MailingID, CompanyID ) " & _
"SELECT " & lngMailingID " AS MailingID, CompanyID " & _
"FROM tblCompany;"
dbEngine(0)(0).Execute strSql, dbFailOnError

 

[saqi]

Thank you so much for all of your help Allen, I will do this and Please bear
with me if i end up coming back to you with a question again because Access
is really new to me but i am commited to learn it all.. Thanks again ...
Saqi

 

[saqi]

Ok, I added a new record in tblMailing which gave me mailing ID. Made a query
with tblMailingCompany and trying to add this str statement into source
connect str property, after adding the str when i try to run query it gives
me this error message
" No data base specified in connection str or IN clause " .. Please help ...

 

[Allen Browne]

Post the SQL statement.

(SQL View on the View menu in query design.)

 

[saqi]

Dim strSql As String
strSql = "INSERT INTO tblMailingCompany ( MailingID, CompanyID ) " & _
"SELECT " & lngMailingID " AS MailingID, CompanyID " & _
"FROM tblCompany;"
dbEngine(0)(0).Execute strSql, dbFailOnError
Same as you wrote, I dont know if i suppose to make any changes in it ....

 

[Allen Browne]

The statement assumes:
- You have a table named tblCompany, with a primary key field named
CompanyID.
- You have a table named tblMailingCompnay, with fields MailingID and
CompanyID.
If the actual tables you have are different, change the names to match.

If you are still stuck, you can ask the query to help you design the SQL
statement:
Add the line:
? strSql
just above the dbEngine line.
When it fails, open the Immediate Window (Ctrl+G.)
Copy the SQL statement.
Create a new query.
Switch the query to SQL View (View menu.)
Paste in the query statement.
Switch to Design view.
Fix the query statement up to match your statement.

Once you have a query statement that works, you can use this utility to help
get it back into VBA code:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html