Results 1 to 12 of 12
  1. #1
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253

    Better way to complete comparing queries

    Click image for larger version. 

Name:	CompareTables.JPG 
Views:	19 
Size:	76.8 KB 
ID:	41047

    I have 2 tables in the above picture. In table 1 there are part numbers and dates that tests are completed for the part numbers. There is multiple records of each part number in this table. the dates are then evaluated in that table for the calculated field. Date in test 1 gives a 2 in the calculated field, date in test 2 gives a 4, date in test 3 gives an 8 then any combination of the tests gives additive value of the first 3 tests. then in table 2 there are check boxes that tell what part numbers need what tests completed. currently i have 2 queries 1 that tells me if something is complete and 1 for not complete. I have found some errors in my queries that i don't know how to fix. the following code is for the not complete query. where my challenge comes from is if someone puts dates into the wrong places then i could get numbers in calculated field that are less than or more than what they are supposed to be and the item is actually completed. i was going to try to do a case statement but i don't know how to do those. Could someone please help me.

    The results that i need are to display in a report are selected from a combobox. the options are: completed, all not completed, test 1 not completed, test 2 not completed, test 3 not completed. I am not sure how to get the individual tests not complete to query properly. I have the report already completed and working for the all completed and all not completed.

    [code]
    FROM tbl_LotInfo INNER JOIN tbl_Parts ON tbl_LotInfo.PartID = tbl_Parts.ID
    WHERE (tbl_LotInfo.TestingCompleted <> tbl_Parts.TestCompleted)
    [code]

    if this is in the wrong thread please let me know or move it to where it is supposed to be.
    Thank you in advance for any help.
    Night

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Where to begin? I came here because I saw that you had 25 views and not one reply, which is usually an indication of a problem, and that is certainly true here. Sorry to be the bearer of bad news...

    If that's what your tables really look like, then you have designed them as you would a spreadsheet which is totally wrong. Table data is tall whereas spreadsheets are laid out wide. Worse, you seem to have mixed data types like check fields values and dates. Even if you solve this issue, there will be many more problems borne out of that design. You should research normalization and make sure you understand it, then try applying the principles in a way that will support your case. If you design a schema and want to have it vetted here, that would be another thread.

    Here's some stuff that you might want to start with

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.com...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.com...cation-in.html

    Important for success:
    Naming conventions - http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837

    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html
    About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Micron,

    Thank you for your reply. I thought I had a good explanation but I realize that i left some important parts out. The tbl_LotInfo doesn't look exactly like that it has other info about the shipment that was received. That table currently has 1200 records and grows almost daily when we receive another shipment of parts to test. The tbl_Parts currently has 80 records and will continue to grow every time we get a new part to test. The check fields in the Parts table is just to mark which test is needed by each part. Those are converted to an integer in the calculated field. The dates in the LotInfo table are input into a form when the testing of each test is complete. The dates are converted to an integer in the calculated field also. then the from and where statements in the query compare for if things are equal or not equal. The picture just shows the 7 possible combinations of testing needed for a given part. The�� table has many records with the same part number that is actually the ID from the Parts table. The Parts table only has one of each part number.

    Should I take out the calculated fields and use the IIf statements in queries and then just have nested queries. What i am trying to achieve is for the management and production leads to know what parts have come in and when. Then for them to be able to know if they have finished all the incoming inspections or which inspections still need to be done so they can go to the inspection team that needs to finish and light a fire under them to get it completed. I hope this makes more sense.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you describe the business in simple English instead of telling us HOW you change dates to integers?
    In overview, my understanding is
    -you test Parts
    -there may be several tests per Part
    -some Parts could require/receive multiple Tests
    -Parts are received in Lots

    Sometimes it's easier to walk us through a day at work sort of scenario. We don't know the details that you are intimately familiar with.
    Good luck.

  5. #5
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Orange,

    Sorry about leaving more much needed information out of the post. i will try to explain it better.

    I start the day by logging into the database. The receiving persons bring me or 2 other test leaders shipments that came in the previous day. The information about the shipment is then entered into the database. IE: VendorID (from vendor table), PartNumberID (from Parts table), Quantity, Date Received, lot number, Contract number, Start Serial Number, Stop Serial Number, and New or Used. All this information is in the LotInfo table with the 3 date fields for when the testing is complete for each Lot. A shipment is then given to 1 team to start testing. they do their test and then enter the date that they finished testing that shipment. If it needs another test is goes to the next team. they finish their test and enter their date when they finish their test. There could be many days between tests being finished. The project leads and management need to be able to look at a report from the database to see if the items are finished with all incoming inspections so they can accept the shipment and send payment to the vendor.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    We're getting close I think. As for calculated fields, it's usually ill advised to store calculated values. Nor do I see the point in converting dates or booleans into integers, assuming you really mean integers. Same for determining a part's testing is complete because it adds up to 14 or something.

    Not sure what design you have with respect to test requirements and their dates but it doesn't sound perfect. If you had to add fields for test and date should you ever need a fourth test then the answer is that it's not optimal to say the least. I always say that it doesn't matter if you will never need this. What matters is IF you had to do this then it's not right. In that case, you probably ought to at least have a table to show parts and associated test types as records. Even that could be further normalized into test types, you parts table, and tblPartTests as a junction table.

    In such a design, a query can show any part info where any tblPartTest record has Null for TestDate. I get the impression that you didn't review the links I posted about normalization based on your posts thereafter. I think lack of normalization is the crux of you problem now, and will continue to throw up barriers as you go. Anyway at this point I don't know what I can do to help as your main issue seems to be bad data input which is allowed due to design.

    If I'm way off base with my assumptions on your design, then maybe post a copy of your db or some pics that will show design elements, and do review normalization if you have not already.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It might be helpful if you could post a copy of your database and/or some screenshots of forms, relationships for context.
    I recommend you work through 1 or 2 of these tutorials from RogersAccessLibrary to experience Normalization and table design.
    The procedure he uses is generic and can be used with any database. Once you have worked through a tutorial, you can do the same with your own database. As Micron has mentioned, it seems part of your issue is the database structure.

    tutorial 1 --ZYX Labs
    tutorial 2 --Class Info System


    There are videos related to database planning at the link in my signature that you may find useful.
    Good luck.

  8. #8
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Micron,

    Unfortunately my work does not allow me to access most of the links that you sent.

    here are screen shots of my tables and forms and relationships
    Click image for larger version. 

Name:	LotForm.JPG 
Views:	11 
Size:	86.0 KB 
ID:	41073 Click image for larger version. 

Name:	LotTable.JPG 
Views:	11 
Size:	72.6 KB 
ID:	41074 Click image for larger version. 

Name:	PartsTable.JPG 
Views:	12 
Size:	33.5 KB 
ID:	41075 Click image for larger version. 

Name:	relationships.JPG 
Views:	12 
Size:	29.8 KB 
ID:	41076

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe you should email those home and look them up there - or ask for the ability to educate yourself in normalization concepts on company time. Just my opinion, but if this is a work related db you ought to be able to do research there, otherwise how are you supposed to be successful at this? You have design issues IMHO.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    I would like to thank everyone for their time and patience on this. Also, thank you for your honest and constructive criticism. I will be looking up the links when i get home. Should I mark this as solved or let it ride?

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I would not mark it solved until you find a workable solution. I think you have a structure/design issue as does Micron.
    As suggested earlier, the tutorials from RogersAccessLibrary will lead you through a design process to ensure your database supports your business.
    Good luck.

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I guess it depends on where it goes from here or what course you decide to take after researching normalization. If it turns into a thread about that, you might as well close this one IMO.

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

Similar Threads

  1. Replies: 12
    Last Post: 05-07-2019, 08:09 PM
  2. Comparing two queries
    By Carbontrader in forum Queries
    Replies: 5
    Last Post: 10-25-2018, 02:58 AM
  3. Cannot get a complete report
    By Lou_Reed in forum Access
    Replies: 20
    Last Post: 03-08-2017, 03:17 PM
  4. Replies: 6
    Last Post: 04-01-2013, 12:25 PM
  5. Comparing records using forms, reports or queries
    By jonathanjilla in forum Access
    Replies: 2
    Last Post: 12-10-2011, 10:48 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