Duplicate Data from one table into another jasonn 20.07.2006 12:31:01 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

Re: Duplicate Data from one table into another "Al Camp" <anon[ at ]anon.net> 20.07.2006 13:17:18 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... [Quoted Text] > 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. -- hth Al Camp Candia Computer Consulting - Candia NH http://home.comcast.net/~cccsolutions "jasonn" <jasonn[ at ]discussions.microsoft.com> wrote in message news:0B391BDF-F3E5-478B-9E80-23F6AE941C33[ at ]microsoft.com... > 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

Re: Duplicate Data from one table into another jasonn 20.07.2006 13:50:02 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" wrote: [Quoted Text] > 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. > > -- > hth > Al Camp > Candia Computer Consulting - Candia NH > http://home.comcast.net/~cccsolutions > > "jasonn" <jasonn[ at ]discussions.microsoft.com> wrote in message > news:0B391BDF-F3E5-478B-9E80-23F6AE941C33[ at ]microsoft.com... > > 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 > > >

Re: Duplicate Data from one table into another "Al Camp" <anon[ at ]anon.net> 21.07.2006 13:30:19 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... [Quoted Text] > 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. -- hth Al Camp Candia Computer Consulting - Candia NH http://home.comcast.net/~cccsolutions "jasonn" <jasonn[ at ]discussions.microsoft.com> wrote in message news:16C3F8F2-76C7-46A4-BDB2-B7C623ED4D06[ at ]microsoft.com... > 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" wrote: > >> 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. >> >> -- >> hth >> Al Camp >> Candia Computer Consulting - Candia NH >> http://home.comcast.net/~cccsolutions >> >> "jasonn" <jasonn[ at ]discussions.microsoft.com> wrote in message >> news:0B391BDF-F3E5-478B-9E80-23F6AE941C33[ at ]microsoft.com... >> > 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 >> >> >>