Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006

    Unanswered: Change format of datebase.

    I have a database with many fields, but only three are important for what I wanna do.

    Acct and Code

    Theres an entry for each code with the acct, like the following.

    Acct Code Quantity
    2432 107 2
    2432 108a 1
    2432 112 1
    2432 124 3
    2456 107 1
    2456 119 1

    Hundreds of entrys
    Lets say there are 20 differant Codes. What i want to do is convert this to a database with the Fields being the Acct and all the possible Codes. 101, 102, 103, 104, etc.

    Since there are multiple entrys with the same acct number I wanna put the quantity in the new fields.

    So the new database will look like so

    Acct 101 102 103 104 105 ......

    Any ideas?
    Last edited by Jay59; 11-24-06 at 23:03.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    the first step should be to create a query which counts the number of occurances of each account & code combination. look inthe help file for the count() & Group By terms in SQL

    ...having done that convert that query into a make table query

    both of these tasks can be doen using the query wizard that ships with MS Access

    however it does beg the question why do you need to put the quantity of Account & Codes) into a column in a table. Its derived data, it will be guaranteed accurate for the instant you run the query, and after that the accuracy will dissapear as soon as someone changes the data. So in my mind its crying out for the first query run with what ever parameters you require (eg date periods).

    Heck it may even be possible to do this as part of a crosstab query
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2006
    The quantity is how man 107's are charged.

    So one Acct will have 2 107 on it.

    The crosstab query works, sorta. Gonna have to play with it some, but it seems like it will work in the end.

Posting Permissions

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