Results 1 to 5 of 5
  1. #1
    Join Date
    May 2010
    Posts
    11

    Unanswered: Help with make table

    Can someone please help me with this. I have posted to other sites but just keep getting asked questions like why not filter? I cannot make anyone understand that I have to do it the way I am posting because that is what management wants. They will not settle for filters. I just need help figuring out this loop and if coding.

    My data: (actually I have a lot more than this but this is an example)


    mpin claim dos descript item qty unit total invoice contract
    3 123 11/15/2011 60MM ACE SHELL, HEMI, HOLED 1706-0-0060 1 $3,450.00 $3,450.00 $39,985.00 Implant Pass-through: PPR Tied to Invoice
    3 123 11/15/2011 20MM, 6.5 CANCELLOUS BONE SCREW 0013-1-6520 1 $250.00 $250.00 Implant Pass-through: PPR Tied to Invoice
    3 123 11/15/2011 LOCKING NUT, TI, 30MM 3449-03000-0 1 $350.00 $350.00 Implant Pass-through: PPR Tied to Invoice
    3 123 11/15/2011 26 X 30, PROX BODY, HA/PLASMA 3461-5260-A 1 $4,950.00 $4,950.00 Implant Pass-through: PPR Tied to Invoice
    3 123 11/15/2011 10CC DBM PUTTY DBM510 2 $2,950.00 $5,900.00 Implant Pass-through: PPR Tied to Invoice
    3 123 11/15/2011 .5ML BIODFACTOR HUMAN AMNION ALLOGRAFT BD50 1 $4,000.00 $4,000.00 Implant Pass-through: PPR Tied to Invoice
    3 123 11/15/2011 30CC CRUSHED CANCELLOUS CC30 1 $1,200.00 $1,200.00 Implant Pass-through: PPR Tied to Invoice
    3 123 11/15/2011 4X6CM HUMAN AMNION PATCH HA460 1 $5,000.00 $5,000.00 Implant Pass-through: PPR Tied to Invoice
    3 123 11/15/2011 15MM, 6.5 CANCELLOUS BONE SCRW 0013-1-6515 1 $250.00 $250.00 Implant Pass-through: PPR Tied to Invoice
    3 123 11/15/2011 36X60 XLINK ACE INSERT 10 HOOD 1008-0-3660 1 $3,150.00 $3,150.00 Implant Pass-through: PPR Tied to Invoice
    3 123 11/15/2011 NECK, STANDARD 36MM X 34MM 3441-13634-A 1 $4,795.00 $4,795.00 Implant Pass-through: PPR Tied to Invoice
    3 123 11/15/2011 STEM, 18X160 FLUTED, STRAIGHT 3422-11816-0 1 $3,795.00 $3,795.00 Implant Pass-through: PPR Tied to Invoice
    3 123 11/15/2011 NEU, 36MM BIOLOX DELTA FEM HD 0005-0-3602 1 $2,895.00 $2,895.00 Implant Pass-through: PPR Tied to Invoice
    4 899 11/20/2011 VeriFLEX (MR) US 4.5 x 16mm 1 $560.00 $560.00 $1,120.00 Implant Pass-through: PPR Tied to Invoice
    4 899 11/20/2011 VeriFLEX (MR) US 3.5 x 12mm 1 $560.00 $560.00 Implant Pass-through: PPR Tied to Invoice
    4 980 11/29/2011 $0.00 $0.00 Implant Pass-through: PPR Tied to Invoice
    4 400 11/29/2011 FG,Promus Element Plus, MR,US 3.5x28 14714363 1 $1,485.00 $1,485.00 $1,485.00 Implant Pass-through: PPR Tied to Invoice


    I am trying to do a make table to take the invalid data and put it in a table of its own. But it has to be the entire claim. Not just 1 part of the claim that is wrong. A valid claim is one that has the mpin, claim, dos, descript, qty, unit, total and contract in all columns and the invoice amount is in one itself. So, if I try running a query and say give me the nulls and put them in the table, it does not work because it selects only those lines. For instance, from the above it should create a table like this: This is because claim 899 does not have the item information. Then claim 980 is missing all information.


    mpin claim dos descript item qty unit total invoice contract
    4 899 11/20/2011 VeriFLEX (MR) US 4.5 x 16mm 1 $560.00 $560.00 $1,120.00 Implant Pass-through: PPR Tied to Invoice
    4 899 11/20/2011 VeriFLEX (MR) US 3.5 x 12mm 1 $560.00 $560.00 Implant Pass-through: PPR Tied to Invoice
    4 980 11/29/2011 $0.00 $0.00 Implant Pass-through: PPR Tied to Invoice

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Is it what you're looking for?
    Code:
    Sub MakeTable(ByVal TableName As String)
    
        Const c_SQL As String = "CREATE TABLE @N ( mpin LONG, claim LONG, dos DATETIME, descript TEXT(50), item TEXT(50), " & _
                                                  "qty LONG, unit MONEY, total MONEY, invoice MONEY, contract TEXT(50) );"
                                                  
        CurrentDb.Execute Replace(c_SQL, "@N", TableName), dbFailOnError
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Just out of interest, why does it matter to Management how you meet their request? If you can obtain a dataset showing what they need to see, it shouldn't matter to them how you obtained it.

    Personally, I don't like make-table queries - you can only run them once without either deleting the table that they make, or changing the design of them to amend the name of the table that they create. I prefer to create the destination table, and then write append queries to write records to it.

    Your description of the problem does not marry up with your description of the requirements and the sample data. (As an aside, please use the CODE and other tags to format your data into something that looks like a table, so that it's possible to see where each column of data starts and finishes. Some experienced users will glance at an unformatted heap of data and skip on to the next thread, meaning that you miss out on their advice.)
    Anyway, if you need to pick up all records relating to a claim that is missing data from one of the fields in any record, looking for nulls in any of them should work:
    Code:
    SELECT 
       a.mpin
    ,  a.claim
    ,  a.dos
    ,  a.descript
    ,  a.item
    ,  a.qty
    ,  a.unit
    ,  a.total
    ,  a.invoice
    ,  a.contract
    INTO
       tblBadData
    FROM
       tblData a
    WHERE
       a.claim IN
    (SELECT
       b.claim
    FROM
       tblData b
    WHERE
       b.mpin IS NULL
    OR
       b.dos IS NULL
    OR
       b.descript IS NULL
    OR
       b.item IS NULL
    OR
       b.qty IS NULL
    OR
       b.unit IS NULL
    OR
       b.total IS NULL
    OR
       b.invoice IS NULL
    OR
       b.contract IS NULL);
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    May 2010
    Posts
    11

    wow

    boy was i making that one hard or what. i thought i needed a bunch of loops and if statements. too used to SAS. yes mgmnt is a pain sometimes but they want what they want. i will not be the one researching the incomplete claims. that will go to the team and they want all that in a separate table for them to play with. hopefully they will not corrupt it. this is why i hate using access for enterprise use. most the time we use SAS and save things that way thnx this worked like a charm

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Happy to help!

    For once, that is a valid management reason for wanting something done a certain way...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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