Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Posts
    9

    Unanswered: Using a query to fill in numbers

    I have a database that has two columns
    out rec# src file#
    1 A
    B
    2 A
    B
    and so on. I would like to fill in the out rec# so that it would look like
    this

    out rec# src file
    1 A
    1 B
    2 A
    2 B

    Is this at all possible in Access (maybe using an IIf statement in a query).

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That is incredibly vague. Instead of using fake data (i.e. A, B, C, etc) please post some more comprehensive, real sample data (4 rows is not enough to clearly show your requirements).

    Undoubtedly more questions will follow, but at least give us a chance
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2008
    Location
    Amherst NY (near Buffalo)
    Posts
    11
    I agree. Vague question = vague answer.

    The answer to your question is, "yes."


  4. #4
    Join Date
    Aug 2008
    Posts
    9
    Code:
    1	*A	1	13357	SCIALDO	MAUREEN	125 S 4TH AVE
    	B	201	13357	SCIALDO	MAUREEN	125 S 4TH AVE
    2	*A	2	13357	PADDOCK	CHARLES	127 S 4TH AVE
    	B	200	13357	PADDOCK	CHARLES	127 S 4TH AVE
    3	*A	3	13357	JONES	PATRICIA	129 S 4TH AVE
    	B	199	13357	JONES	PATRICIA	129 S 4TH AVE
    4	*A	4	13357	HAGGERTY	DAVID	131 S 4TH AVE
    	B	198	13357	HAGGERTY	DAVID	131 S 4TH AVE
    5	*A	5	13357	LONIS	PAULA	135 S 4TH AVE
    	B	197	13357	LONIS	PAULA	135 S 4TH AVE
    6	*A	6	13357	HIGGINS	CHRIS	139 S 4TH AVE
    	B	196	13357	HIGGINS	CHRIS	139 S 4TH AVE
    When you look at this the B should be under the A so number1 has two records A and B etc.


    Okay, here is a copy of some of the rows in my database. I know in excel i can add a column before outrec and then add a row, then i can put a formula in as =if(b2=" ", A1, b2). This formula after copied down will put a 1 under the 1, a 2 under the 2, etc.So with that said I can't use excel , I need to use access, and I'm wondering if I could use the same formula somehow in a query. I'm trying to research IIF but with no luck.
    Last edited by gvee; 09-30-08 at 16:11. Reason: [CODE] tags added for readability

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    UPDATE my_table
    SET    that_first_column = x.that_first_column
    FROM   my_table x
     INNER
      JOIN my_table y
        ON x.common_field_1 = y.common_field_1
       AND x.common_field_2 = y.common_field_2
       AND x.common_field_N = y.common_field_N
    George
    Home | Blog

  6. #6
    Join Date
    Aug 2008
    Posts
    9
    Thanks George. I just have a question, I changed the [my table] to the table name and [that_first_column ] to I assume the column that has the number in it. I changed the coding to read the other column names, which I'm assuming that was what you were going for. So I ended up with this, but when I run it it comes up with an error -Syntax error (missing operator) in query expression x.outrec and then the rest of the code starting with FROM. Can you help me with this, I would appreciate it.

    UPDATE [copy mergerpt]
    SET outrec = x.outrec
    FROM [copy mergerpt] x
    INNER JOIN [copy mergerpt] y
    ON x.scrfile = y.scrfile
    AND x.srcrec = y.srcrec
    AND x.zip+4 = y.zip+4
    AND x.lastname = y.lastname
    AND x.firstname = y.firstname
    AND x.address = y.address

Posting Permissions

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