Results 1 to 9 of 9
  1. #1
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160

    How to check for Product key Present in the table before importing

    Hi guys,



    I have a invoice details table in excel format which I need to import in Access, on weekly basis. importing is not an issue and everything is working fine. But i would first like to verify the ItemID_FK (text field) field and check that all the products are present into database before they are imported. I would like to do this to avoid any later database errors at a later stage or user level stage.

    I have imported all the data to new table tblInvDetImport having a field ItemID_FK (Which needs to be checked). tblItem having ItemID_PK is the table with which the data needs to be cross checked.

    Any ideas or help would be greatly appreciated.
    Thanks and Regards
    Deepak Gupta

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Take the new XL file and save it as a generic name like: c\temp\file2import.xls
    link it as an external file to table :tXlFile2Import
    make a query to check for the missing keys...
    QsBadXL:=Select * where [key] is null.

    either import only the ones with keys or reject them all:
    Code:
    sub btnImport_click()
    if Dcount("*","qsBadXL")>0 then
       MsgBox "Errors in XL file"
    else
       Docmd.openQuery "qaImportXL"
    end if
    so the steps would be:
    1.save new XL file to temp file.
    2.click Import button. (To run the code above)

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I *think* I understand what you are trying to do.... not really clear.

    So you have an Excel spreadsheet with data you import.
    Note: when importing, do not use a Make table query. It causes bloat and can cause corruption. Create the table once and delete the records before importing.

    You say the import table name is "tblInvDetImport" (the invoice details), with a text field name of "ItemID_FK".
    The items/product table name is "tblItem" with a field "ItemID_PK".

    There are a couple of methods:

    1) Use an unmatched query.
    Create a query (maybe "qryItems") on table "tblItem" with one field "ItemID_PK".
    Code:
    SELECT ItemID_PK FROM tblItem ORDER BY ItemID_PK
    Now create a query (maybe "qryXlsImport") on the import table
    Code:
    SELECT DISTINCT ItemID_FK FROM tblInvDetImportORDER BY ItemID_FK
    (remove the keyword "DISTINCT" if you want to see ALL records in "tblInvDetImportORDER" that don't have a match)

    Now use the "Find Unmatched Query Wizard" with the two queries above to create a query that displays items that are not in the table "tblItem". Don't know what fields/data you want to see in the query, but you could add more fields in the "qryXlsImport" if you wanted to see more than just the "ItemID_FK" field.


    2) "Brute" force method using VBA.
    Write code to find what items are in the invoice table "tblInvDetImportORDER", but not in the table "tblItem".
    A couple of record set and some looping.




    ---------------------------------------------------------------------------
    FYI, I would never create a PK field that is a text field. For reasons why, see Microsoft Access Tables: Primary Key Tips and Techniques

  4. #4
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Hi Guys,

    First of all sorry for not being able to reply earlier.

    Ssanfu, you completely understood my requirements and i made queries as per your instruction and it worked perfectly.

    Ranman256, I also added your advise with query and copied your code and was able to get near automated process for importing.

    Now while doing this I have faced a small glitch which I would like some help with:

    In tblInvDetImport I have a field called ItemCode_FK (Which I need to check for validity) & with query it checks fine. Now the issue is while fooling around I noticed that there is lot of task which user has to do in excel before importing, which all of it I feel can be done with help of access very easily, but the issue I am facing is that Item code in access has to be 11 digit code (always) and most of the times the code is all numerals and some time combination of Alpha numeral. Now some times code being imported is less than 11 digits, but in access it needs to be 11 digits. For Example:

    Item Code In Excel Sheet :- 12345678 where as it needs to be in Access :- 00012345678.

    How can I add Zeroes in front of numbers which are less than 11. It always will be preceded by zeroes for all codes which are less than 11 digits.

    Hope I have been able to explain my problem. Please guide me, this would really help me out alot and ease the job for end user.

    Thanks and Regards
    Deepak Gupta

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I haven't read the whole thread but just picked on this point:

    Item Code In Excel Sheet :- 12345678 where as it needs to be in Access :- 00012345678.
    How can I add Zeroes in front of numbers which are less than 11. It always will be preceded by zeroes for all codes which are less than 11 digits.
    Does it actually need to be saved with leading zeroes in the table?
    If so, it MUST be a text field. Use an update query to add leading zeroes as necessary

    However, if it just needs to have leading zeroes for display purposes in forms/reports, just use formatting: 00000000000 in the property sheet for the control(s)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Adding to Colin's comment, you could concatenate zeros to the number, then use the Right() function to return 11 characters.

    In VBA:
    Code:
    Dim txtItemCode as string
    
    txtItemCode = Right("00000000" & ItemCode,11)
    where "ItemCode" is the numeric field from Excel.


    In a query:
    Code:
    SELECT Field1, Field2, Right("00000000" & ItemCode,11) As txtItemCode
    FROM tblInvDetImport

  7. #7
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Thanks Steve, That is exactly what I was looking for. I am using same Right function in excel to achieve desired goal. Never knew it was present in access also. Thanks. Will Give it a try and come back.

    Regards
    Deepak Gupta

  8. #8
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Thanks Collin, your help and guidance is much appreciated.

    Regards
    Deepak Gupta

  9. #9
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Every thing worked great, Thanks for your help.

    Regards
    Deepak Gupta

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

Similar Threads

  1. Replies: 1
    Last Post: 05-06-2015, 04:28 AM
  2. Replies: 6
    Last Post: 02-28-2015, 11:57 AM
  3. Replies: 5
    Last Post: 05-09-2014, 02:38 PM
  4. Replies: 3
    Last Post: 02-26-2013, 05:07 AM
  5. Replies: 2
    Last Post: 07-12-2012, 12:46 AM

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