Solved

duplicating and incrementing records vfp

Posted on 2010-11-17
5
239 Views
Last Modified: 2012-05-10
Hi:
Trying to wean my staff off excel....
I have 100 customers and 10 products. Each customer has a different price for each product.
Currently I have 100 excel sheets
I have created a customer table with 100 records and a product table with 10 records.
So I believe I need now to create a joining table  (called say "price") comprising 3 columns - customer , product, and price for that product.
Is there any fast and easy way of looping through each customer in the customer table, appending to the price table 10 records for each customer,pulling each product from the product table? So I end up with (100*10) 1000 records? One record for every product for every customer?
 I can make the product code sequential 1-10 if easier!
0
Comment
Question by:ClaytonGlass
  • 3
5 Comments
 
LVL 7

Expert Comment

by:mkobrin
Comment Utility
i don't know foxpro, but the way i would do it in MSSQL would be as follows:
declare cCustomers scroll cursor for select customerID from customers

declare @i int = 0

open cCustomers

fetch next from cCustomers into @i

while @@fetch_status = 0

BEGIN

	insert into customerProduct(customerID, productID, price) values(@i, product1ID, product1Price)

	insert into customerProduct(customerID, productID, price) values(@i, product2ID, product2Price)

	insert into customerProduct(customerID, productID, price) values(@i, product3ID, product3Price)

	insert into customerProduct(customerID, productID, price) values(@i, product4ID, product4Price)

	insert into customerProduct(customerID, productID, price) values(@i, product5ID, product5Price)

	insert into customerProduct(customerID, productID, price) values(@i, product6ID, product6Price)

	insert into customerProduct(customerID, productID, price) values(@i, product7ID, product7Price)

	insert into customerProduct(customerID, productID, price) values(@i, product8ID, product8Price)

	insert into customerProduct(customerID, productID, price) values(@i, product9ID, product9Price)

	insert into customerProduct(customerID, productID, price) values(@i, product10ID, product10Price)

         fetch next from cCustomers into @i

END

deallocate cCustomers

Open in new window

0
 
LVL 27

Expert Comment

by:CaptainCyril
Comment Utility
Customer (id, customer, address, phone, ...)
Product (id, product, defaultprice, ..., order)
CustomerProductPrice (customer, product, price)

SELECT Customer.id As customer, Product.id As product, defaultprice as price;
 FROM Customer, Product;
 ORDER BY Product.order;
 INTO CURSOR temp
SELECT CustomerProductPrice
APPEND FROM DBF('temp')
0
 
LVL 27

Accepted Solution

by:
CaptainCyril earned 250 total points
Comment Utility
I am sorry
ORDER BY Customer.Customer, Product.order

or


ORDER BY Customer.Customer, Product.Product
0
 

Author Closing Comment

by:ClaytonGlass
Comment Utility
Thank you very much, Captain!
I would thank mkorbin but since the captain offered a foxpro solution I will go with that. Thanks for the offer,anyway!
0
 
LVL 27

Expert Comment

by:CaptainCyril
Comment Utility
You are welcome.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
HOW TO: Upload an ISO image to a VMware datastore for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere Host Client, and checking its MD5 checksum signature is correct.  It's a good idea to compare checksums, because many installat…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now