Results 1 to 12 of 12
  1. #1
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376

    This thing is beating me up bad

    Hello all,



    I have been trying for 2 days to get the desired result from a query and I can't seem to get it correct. I am trying to take data from the tblOptimumPurchasedParts 10-15-18 and appending it to the Inventory Transactions table. I have the AAA 10-16-18 query which shows what I want to append to in the Inventory Transactions table. I should get 482 records appended but it appears as though there will only be 3.

    Can someone please help. I believe I have included all required tables in the attached database but if not, let me know. This is a very stripped done version of the original.

    Silverado 10-16-18.zip

    Thanks

    Dave

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As the field names don't tally in your query and destination table it isn't obvious what to append to what.
    Please post your append query SQL so we have a starting point
    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

  3. #3
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Ridders,

    Attached is a file with the existing append query.

    Silverado 10-16-18 #2.zip

    Thanks

    Dave

  4. #4
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376

    Post

    Ridders52,

    The last file I posted was missing part of the append query, this one has it included
    Silverado 10-16-18 #2.zip
    Thanks

    Dave

  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
    Tried the latest one but the append query is looking for a table/query called AAA-Opt which isn't included.
    Tried renaming the query with a date to that but it returns no records
    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
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Ridders52,

    Sorry about leaving a file out, this has just got me so flustered, I am making some headway on my own I think though.

    Here is another file with the other query included.

    Silverado 10-16-18 #3.zip

    Thanks

    Dave

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Third time lucky!

    The append query has no results!

    I'm just starting to look at all the underlying queries used in your append query
    First of all recommend you avoid spaces in table, query & field names

    The query "Relieve Inventory ....BKP" has 78894 records but it uses what is called a cartesian join AKA no join between tables
    Rearranging the tables in the query design window shows 2 unrelated groups of 3 tables so you get every possible combination of results from each

    Click image for larger version. 

Name:	Capture.PNG 
Views:	18 
Size:	26.0 KB 
ID:	35846

    Joining both tblPartMain by the PK field reduces this to 4 records.
    The same outcome can be got with this simplified code

    Code:
    SELECT tblPartMain.MfrPartNumber, tblPartMain.PartDescription, tblPartMain.PartMainID_PK, tblPartMain.MfrPartNumber AS Child_PN, tblPartMain.PartDescription AS Child_Description, [tblOptimumPurchasedParts 10-15-18].PurchasedPart, [Work Orders].Qty, [tblOptimumPurchasedParts 10-15-18]![Qty]*[Work Orders]![Qty] AS [Build Qty], [Work Orders].DHRNumber, [Work Orders].DHRID, [Work Orders].SerialNum, [Inventory Transactions].ProductID, [tblOptimumPurchasedParts 10-15-18].QtyFROM (tblCategories INNER JOIN ((tblPartMain INNER JOIN [Inventory Transactions] ON tblPartMain.PartMainID_PK = [Inventory Transactions].ProductID) INNER JOIN [Work Orders] ON ([Work Orders].DHRID = [Inventory Transactions].DHRID) AND (tblPartMain.PartMainID_PK = [Work Orders].PartID)) ON tblCategories.ID = tblPartMain.CategoryID) INNER JOIN [tblOptimumPurchasedParts 10-15-18] ON tblPartMain.PartMainID_PK = [tblOptimumPurchasedParts 10-15-18].ChildID
    WHERE ((([tblOptimumPurchasedParts 10-15-18].PurchasedPart)=True));
    In the designer that looks like

    Click image for larger version. 

Name:	Capture2.PNG 
Views:	18 
Size:	30.5 KB 
ID:	35847

    Can you see the next issue?
    To get results, both ProductID & DHRID have to be identical. Can you see why?
    And is that really what you want?

    Check the above is what you want & after I hear back from you, I'll carry on ...

    Two more issues:
    Qty is shown twice but are the same value - remove one of them from the design
    You are using captions for the field DHRNumber (Work Order #) & DHRID (Work Order ID) which makes it unnecessarily difficult to follow what's happening
    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

  8. #8
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Colin,
    What I want is for every record in the tblOptimumPurchasedParts table to be appended into the Inventory Transactions table with the quantity for each record and the serialNum from Work Orders table and the other items listed in the append query relative to the Inventory transactions table.

    I don't see what you mean by "To get results, both ProductID & DHRID have to be identical. Can you see why?" and no hat isn't what I want. I should have 482 records appended to the Inventory Transactions table when the query is ran, 1 record for each record in the tblOptimumPurchasedParts table.

    Does that make sense?

    Thanks

    Dave

  9. #9
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Colin,

    I believe I have it. Here is what I came up with, I was making it a lot harder that it had to be.

    Click image for larger version. 

Name:	10-16-18 working.PNG 
Views:	16 
Size:	29.2 KB 
ID:	35848

    Does this look correct to you? It does give me the correct # of records appended to the Inventory Transactions table and with just a quick look, the entries seem correct.

    Thanks

    Dave

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I'll have a look later but this is yet another CARTESIAN query. No join between 2 of the tables. WHY?

    EDIT 3 hours later
    OK I've looked at the cartesian query & it will indeed give you 482 results which are identical EXCEPT for the first 2 fields which are from the tblOptimumPurchasedParts 10-15-18 table
    If that's what you want then its perfect for your needs
    However, cartesian queries are, with very rare exceptions, a bad approach in Access
    Last edited by isladogs; 10-16-2018 at 03:29 PM.
    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

  11. #11
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Colin,

    It does seem to give me the desired results, however, I do respect your knowledge and ultimately I believe I have found another way to get the same results. I will use this for now and work on editing another table to accommodate those changes.

    I will use this approach for now. The Table only contains parts used for the particular end item so I don't think it is too risky. It does populate the Inventory transactions table properly. I am sure it isn't the most elegant approach, but until I can get the other table modified it will work for now.

    I hadn't heard of cartesian query's before but will look into them for future reference and make sure not to use them

    Thanks

    Dave

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Glad you're happy with it.
    Many thanks for your reputation feedback comments
    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

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

Similar Threads

  1. I need a very simple thing!!
    By Z1nkstar in forum Access
    Replies: 2
    Last Post: 05-12-2014, 12:36 PM
  2. Another thing i'm stuck on
    By Durks123 in forum Database Design
    Replies: 1
    Last Post: 07-29-2013, 06:07 PM
  3. Probably a Strange thing to ask
    By everette in forum Access
    Replies: 3
    Last Post: 03-26-2011, 08:33 PM
  4. Most likely a programming thing
    By dfelock in forum Programming
    Replies: 7
    Last Post: 12-21-2010, 11:10 AM
  5. Craziest Thing You've Seen/Done
    By Rawb in forum Access
    Replies: 1
    Last Post: 10-25-2010, 02:56 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