Append Table & Related Sub-Table

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Hi;

Just spent the last 35 minutes searching Microsoft tech support for:
Append Query.

Using Acc2000.

When a Quote turns into a Sale:have to copy data from table "tblQuoteMain"
and its related sub-table "tblQuoteSub" to "tblSoldMain" and its related
sub-table "tblSoldSub".
..
"tblQuote*" Relationship: QuoteMainId, One-To-Many, Enforce Referential
Integrity, Cascade Update & Delete on.
"tblSold*" Relationship: SoldMainId, One-To-Many, Enforce Referential
Integrity, Cascade Update & Delete on.

I know that an Append query will copy the data from the "Main" tables
correctly.
But what about the "Sub" tables?

What needs to be done so that the related data in a sub-table arrives in the
proper sub-table of an appended table?

Oh; the search results from Microsoft "Help", and I use that word "Help"
loosely, number 43 was:
Query Modem Operation Appends Output to Log File Even Though the "Append"
Option Is Not Selected
I searched Access Support for "Append Query"


Thank You.

Andy
 
Andy

Why? (remember, we're not there, we can't see your tables)

As in "why do you need to duplicate all the info from one table
(quote-related) to another table (sale-related)?" Would it be sufficient to
just add a field that indicates DateSold?

Would it be appropriate to model the (potential) customer in one table, the
customerID in a "quotes" table, and, if sold (and preserving totally
different info re: the sale), the customerID in a "sold" table. That way,
nothing needs to get copied.

Or am I missing something?

Good luck

Jeff Boyce
<Access MVP>
 
Jeff;

Two seperate entities.

When the customer buys, the information from the quote, (Purchaser Name,
Items Purchased, Quantity, Etc,), needs to be copied from the Quote Database
to the Sales Database.

We can't be the only one looking for an answer to this obstacle.

Andy
 
Andy

Please re-read my response. Once you've captured (some) info such as
PersonName, Address, Phone, etc. in a "Person" table, you don't need to
duplicate it in "Quote" and "Sale" tables -- you only need to put the ID of
the Person. By all means, keep your separate entity/tables for these two,
just don't duplicate fields.

There are circumstances (apparently not yours) in which the information
captured for a quote would be quite similar (?duplicate) what would be kept
for a sale -- that's why I asked.

Good luck

Jeff Boyce
<Access MVP>
 
Jeff;

Thank You for Your replies.

Seems we are getting off the path.

This must happened in thousands of companies daily.

There are 2 seperate databases. Quote.mdb & Sold.mdb

The question is:

What is the procedure for appending a table with the Potential custormer's
name/address/phone and it's related sub-table that contains a datasheet list
of items quoted, to a "Sold" database that contains a table with the
Customer's name/address/phone and it's related sub-table that contains a
datasheet list of the items purchased?

Jeff; again Thank You for Your replies.

Andy
 
Andy

I missed that in your second post -- I took the "two tables" from your first
and assumed they were in the same .mdb file.

Have you tried linking to the "quote" tables from within the "sales"
database? You could then do an append query using the linked table(s) to
write values into your "sales" table(s).

Good luck

Jeff Boyce
<Access MVP>
 
Jeff;

The link is easy. What do You need to do to append the sub-tables so that
the data is correctly appened?

Andy
 
Andy

Your "sub" table rows are related to certain "main" table rows. First
append the main table rows as needed. Then "collect" the related subtable
rows. Then append the related subtable rows.

Good luck

Jeff Boyce
<Access MVP>
 
Jeff;

You are an MVP!
Thank You for Your help.

There is a common field for the relationship.

What does "Collect" mean?

Andy
 
I used a 'jeff-ism'. I meant to create a query that returns the same rows
from the main table, plus the associated sub (child) table rows.

Jeff Boyce
<Access MVP>
 
Jeff;

Have worked it out. Its six steps.

Here is the answer to the question:

"What is the procedure for appending a table with the Potential custormer's
name/address/phone and it's related sub-table that contains a datasheet list
of items quoted, to a "Sold" database that contains a table with the
Customer's name/address/phone and it's related sub-table that contains a
datasheet list of the items purchased?"

First added "QuoteID" to the tblSales. Set it to "Yes (No Duplicates)"

Second; created an Append qry that appended tblQuote to tblSales.
Data Inc.: Prospect Name to Customer Name, Prospect Address to Customer
Address and QuoteID to QuoteID.

Third; created an Append qry that included the following.
tblSales & tblQuotedItems.

Set the relationship Left Table tblQuotedItems Right Table tblSales,
"QuoteID to QuoteID" and option 2"
"Include all records from tblQuotedItems and only those records from
tblSales where the joined fields are equal."

Then clicked on the Query Command and clicked on "Append Query" selected the
tblSalesItems as the table to append to.

Finally added the field "SalesID" from tblSales to the append.
SalesID is the relationship between tblSales & tblSalesItems.

Works perfectly. The appending of the SalesID adds that number to the
records in tblSalesItems so when the Form is displayed all of the quoted
items are listed in the sfrmSalesItems.

Jeff Thank You for Your help. You encouraged me to give it a shot.

Andy
 
Back
Top