Duplicate Data from one table into another

[Guest]

Hi there

I'm creating a database to help a friend with his courier business and
running into a few problems.
I have 3 tables: Customers, Services, Customer Rates
Most Customers use all the services offered and each customer has a unique
pricing structure.

Here's what i want to do:
When I enter a new customer in the Customers table(using a form), I want the
customer Rates to be updated automatically with all the services for that
specific customer. I've created a command button on the Customers form to
open the Rates form to just enter the prices for the services.

Can this be done? Can someone please help me?

Thanks in Advance

 

[Al Camp]

Jason,
I'm a bit confused about your question.
Are you saying that for Cust#1 Svc1 = $22.00, but for Cust#2 Svc1 = $24.00, and for
Cust#3 Svc1 = $26.00?
In other words... do prices for your services vary from one customer to another?

This appears to be what your saying in...

Most Customers use all the services offered and each customer has a unique
pricing structure.

If so, then there is no need for a CustomerRates table. All customers share "common"
Services, but they do not share "common" prices.
There is no relationship between Services and Prices.
For each customer... select the Services they require, and manually price them.

 

[Guest]

Hi Al

Thanks for the response. That is exactly what i'm saying but I don't want to
have to choose the service to enter the price. Is there not a way when i
enter a new customer that the Customer Rate table gets updated with the
customer name and all the services so i can just price them?

Say i have 30 different services and each service has a minimum Weight,
Minimum Price and Price per Kg for weights above minimum that gets added to
your Minimum Price. What I want is that when i enter a new customer than my
Customer table must be updated with 30 records that have the same customer
name with the 30 different Services. Then i just add the weight and prices
for each record.

Can this be done?

 

[Al Camp]

Jason,
This seems to be a bit convoluted... It's like a customer comes into my store, and
instead of just charging them for the items they purchased, I list all 1500 items that I
sell, and then just check off the 5 they purchased.
I have used the below method to append 60 records to a subform recordset, BUT... every
one of those subform records required a user entry.
Adding Services that won't be used is

You wrote...

What I want is that when i enter a new customer than my
*Customer* table must be updated with 30 records that have the same customer
name with the 30 different Services.

You don't want to update the Customer table with 30 records... the Customer is the ONE
side of the realtionship. The 30 records should go in a related table (pehaps that's what
yoummenat by CustomerRate table??) ... linked to Customer via a CustID. CustomerRate is
the MANY?

Assuming that...
Customers on the main form, and CustomerRates in a subform.
So, when adding a new customer, at some point you would initiate an Append query to
add 30 records from table Services to CustomerRates, with that CustID included in each
record appended record.
Use The CustID from Customers, linked to Services to generate the Append data to
CustomerRates.

Upon a subform requery, those Service types records would appear in your subform, with
no Price.