Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005
    Posts
    2

    Unanswered: Producing Text File

    Hello all,
    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/ "&".

    here's what I mean:

    !!OrderID OrderDate CustomerID Total
    --------- --------- ------------ ------
    123456 #456789 &abcdef 987654

    If you can help me at all, I'd much appreciate it!!! Thank you!

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    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.
    Code:
    SELECT 
    [OrderID],
    "#" + [OrderDate] AS [OrderDate],
    "&" + [CustomerID] AS [CustomerID],
    [Total]
    from
    your_table;
    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

  3. #3
    Join Date
    Nov 2005
    Posts
    2
    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.

    Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •