Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Location
    Google, KS
    Posts
    47

    Unanswered: Dynamic Field Names issue

    Hey Guys,

    I am working on building a new database.
    It uses a two linked databases that I download from the client. One of them displays point of sale quantity for the past 13 weeks. Unfortunately the field names are dynamic, they use the year and week to name the field, i.e. 201012 -201013 - 201014 etc for 13 weeks.

    How can I write a query that would sum 13 weeks POS qty data for a particular store when week after week the field names change?

    Any ideas? I am at a total loss. I tried doing some research but I am not even sure if I am asking the question in the right way.
    Attached Thumbnails Attached Thumbnails db_POS.jpg  
    Last edited by Marsbars; 06-23-10 at 20:09. Reason: right = write, crappy gammar police
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Are the first n fields always the same? If so, you can use a recordset and use the index of the Fields collection to reference the data. So if you had a recordset, rst, it would look like:

    Code:
    For lngFieldID = 8 to 20
       dblTotal = dblTotal + rst.Fields(lngFieldID)
    Next LnFieldID

  3. #3
    Join Date
    Nov 2009
    Location
    Google, KS
    Posts
    47
    That did the trick. Thanks.
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

Posting Permissions

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