My sql skills are pretty poor, so please bear with me!
I need to take info from two tables (in Access db) and produce two txt files.
The data in the first table is listed as follows: OrderID, OrderDate, CustomerID, Total
The data in the second table is listed as follows: OrderID, Quantity, Discount, UnitPrice
The user has to be able to specify a time frame (start date and end date).
The major problem I'm having is with setting up the txt files. The header has to have the OrderID listed as "!!OrderID" (this is the way the fake company has their db set up apparently) Also, in the actual data in the txt files the OrderDate has to start w/ "#" and the CustomerID has to start w/ "&".
Well, in MS Access you can't use "!" in column name, so you'll have to handle this using VBA coding and replace column name when writing into the text file. You didn't describe tables relationship.
"#" + [OrderDate] AS [OrderDate],
"&" + [CustomerID] AS [CustomerID],
store the result into some VBA object (I don't remember which one to use, it's long time since I used Access for last time). Open text file and read row by row from VBA object and write it into the file. Replace column name OrderID with !!OrderID
Thanks for the help. The tables are linked through OrderID. Also, I found out that I don't have to use Access to do the project. MySQL is allowed as well. If that makes a difference w/ the !! problem, let me know.