Results 1 to 3 of 3
  1. #1
    SecretGeek is offline Novice
    Windows XP Access 2000
    Join Date
    Aug 2010
    Posts
    2

    Creating Multiple Lines per Member

    Hi All I need a little help here
    I'm using MS Access 2000

    I have a large table that I need to convert to loading up to my database system here

    a simple example of what I'm looking to do.......



    it's currently in format:

    RefNo Surname Date1 Salary1 Date2 Salary2 Date3 Salary3

    001 Jones 01/01 100.00 01/02 150.00 01/03 170.00
    002 Peters 01/04 200.00 01/05 220.00 01/06 290.00


    I need to keep the ref no and surname together but have all dates underneath in one column, and all salaries in the next, and possibly add the sequence no per member at this stage if possible eg:

    Refno Surname Dates Salaries Seq no

    001 Jones 01/01 100.00 1
    01/02 150.00 2
    01/03 170.00 3

    002 Peters 01/04 200.00 1
    01/05 220.00 2
    01/06 290.00 3

    etc

    My knowlege of SQL is pretty basic but i pick up on it fairly quickly, I'm just not sure how to compile the string on this one!
    Any help appreciated!
    Cheers.

  2. #2
    SecretGeek is offline Novice
    Windows XP Access 2000
    Join Date
    Aug 2010
    Posts
    2
    shit it deleted the extra spaces - i had all the dates and sals directly under each other if you know what i mean

  3. #3
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    if you normalize your table by creating 2 tables:

    tblPersons with the fields
    perID (PK, most times an autonumber)
    perReference
    perName
    ......

    and tblSalaries with the field:
    salID (PK; autonumber)
    salPerson (Long integer: FK to tblPersons.perID)
    salDate
    salAmount
    .....

    then you fill the tables with some append queries

    Once you have a normalized system writing queries is fairly easy.

    succes
    NG

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query for distinct member number
    By Lauri in forum Queries
    Replies: 1
    Last Post: 08-23-2013, 03:22 AM
  2. Replies: 4
    Last Post: 01-25-2010, 04:14 PM
  3. Multiple lines per record: Report?
    By cjbuechler in forum Queries
    Replies: 7
    Last Post: 07-30-2009, 08:32 AM
  4. Creating multiple reports from one Query
    By Mike Cooper in forum Reports
    Replies: 5
    Last Post: 04-24-2006, 05:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums