Results 1 to 12 of 12
  1. #1
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296

    Is there a way to make Unique Records ignore the Autonum field while still having it show?

    Thank you for clicking and any help you can offer.
    I am still a beginner and I am learning the hard way due to not having much if any documentation for Access 2000. (by trying to adapt tutorials and lessons to the older version.)
    This is Access 2000 and I cannot upgrade.

    Maybe I am misunderstanding how Unique records works.
    I have an Autonum field, a Lot Number, and some chemistry make up fields (eg: C, Mn, P, S).
    Everything is set to short text type because sometimes letters are used.

    Each lot number has a designated make up of chemistry so for example Lot 1 will always be C: 5, Mn: 8, P: 6, S: 10. It will never change.
    The problem is that for almost 10 years people have been manually entering them so I am trying to write a query to find all unique records/mistyped records.
    For Instance say this is our table
    Autonum: 1 Autonum: 2 Autonum: 3
    Lot: 1 Lot: 1 Lot: 1
    C: 5 C: 5 C: 55
    Mn: 8 Mn: 8 Mn: 8
    P: 6 P: 6 P: 6
    S: 10 S: 10 S: 10

    Lot 1 was used several times but it looks like someone accidentally hit the 5 key twice.
    I want a query that would ignore the autonum field. So it would show me only Autonum 1 or 2 (since they are the same) and Autonum 3.



    That way I can give this list to one of the people who enters them and they can tell which Autonum is wrong and go edit it.
    Unfortunately the person who made this database before me put all this information in one table instead of linking the Lot to each Autonum.
    Before someone says just run it without including the Autonum field, Yes that would work but we use Autonum as our ID system so while I could see what lot is wrong I would have to sort through hundreds of Autonums with that Lot number since they are contained in the same table. So I somehow need to know which Autonum has the messed up Lot information.

    Currently I am trying to use the Unique Records option but it thinks every record is unique due to the Autonum field. Its an odd problem I know.
    So that brings us to my title, Is there a way to make Unique Records ignore the Autonum field while still having it show?

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Normally people show the data as it appears in the table?, which is horizontally, not vertically?

    You could try a Group By and make the Autonum a Where and criteria > 0

    Here is a quick test on my data.
    Code:
    SELECT Transactions.Description, Transactions.Category, Count(Transactions.ID) AS CountOfID
    FROM Transactions
    WHERE (((Transactions.ID)>0))
    GROUP BY Transactions.Description, Transactions.Category;
    The count will show how many of each.
    Attached Thumbnails Attached Thumbnails Capture.PNG  
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Apologies I'm not used to posting tables on forums. Normally I would do a screenshot but my employer is very paranoid.
    I tried Group By but then I realized it still doesn't tell me which Autonum I need to change. It just tells me there are 7 of the correct one and 1 incorrect one but it doesn't tell which record I have to go fix.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    mock up your data in Excel and dump it here - you'll automatically create an html table. What you have there is too hard to follow, IMO. If you have fields named e.g. P, Mn, S etc. this is the crux of your problem. The table is designed like a spreadsheet (it's not normalized). Aggregate functions (e.g. total, count) are difficult to apply across fields. Autonum1, 2 and 3 is especially puzzling if that's from one table. If it's not, then the normalization issue would be even worse.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    All I can think of at present is to concatenate the ID fields for each grouped row?

    You can use http://www.accessmvp.com/thedbguy/co...itle=simplecsv and make that an expression.
    So you will get 6 iDs for the correct version and only 1 for the errant entry or as many as there of of each type.

    Your criteria would need to be all four fields?

    See the actual code at the bottom of the link as the best example.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your data should look along the lines of this, where LotIDfk might be the primary key value from a lot table as a foreign key value in the test data table, assuming this is even about testing.
    tblTests
    TestIDpk LotIDfk Element TestValue
    1 1 C 5
    2 1 Mn 8
    3 1 P 6
    4 1 S 10
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Click image for larger version. 

Name:	Untitled.jpg 
Views:	11 
Size:	29.9 KB 
ID:	48792
    Perhaps this helps to understand the table? Heats = Lot
    I understand the table is not structured right and I am attempting to slowly adjust it overtime. For now I am just trying to fix the Heats/Lots that were messed up by human error.
    Note how 1, 4, and 6 are the same Heat but in 6 the C field is mistyped.
    So I would want the query to only show me 1 and 6. Keep the first unique instance and ditch duplicates.




    Quote Originally Posted by Welshgasman View Post
    All I can think of at present is to concatenate the ID fields for each grouped row?

    You can use http://www.accessmvp.com/thedbguy/co...itle=simplecsv and make that an expression.
    So you will get 6 iDs for the correct version and only 1 for the errant entry or as many as there of of each type.

    Your criteria would need to be all four fields?

    See the actual code at the bottom of the link as the best example.
    I am gonna try my best to apply this and see if it works but I think it may be above me.

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    So is A150 always 5,8,6 and 10 ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Yes. Once a Heat/Lot has its Chemistry values set it will never change. They are the chemical make-up of that specific Heat.
    (After I clear up all these error ones I plan on adding them to a separate table so that it can be updated and up kept easier.)

  10. #10
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think all you need to do is to create a totals query in which you bring the all the fields you want to compare and the autonumber. In the Totals row for the Heat, C, Mn, P and S chooose Group By, for the autonumber choose Min:

    SELECT tblHeats.Heats, tblHeats.C, tblHeats.Mn, tblHeats.P, tblHeats.S, Min(tblHeats.AutoID) AS MinOfAutoID
    FROM tblHeats
    GROUP BY tblHeats.Heats, tblHeats.C, tblHeats.Mn, tblHeats.P, tblHeats.S;

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Gicu View Post
    I think all you need to do is to create a totals query in which you bring the all the fields you want to compare and the autonumber. In the Totals row for the Heat, C, Mn, P and S chooose Group By, for the autonumber choose Min:

    SELECT tblHeats.Heats, tblHeats.C, tblHeats.Mn, tblHeats.P, tblHeats.S, Min(tblHeats.AutoID) AS MinOfAutoID
    FROM tblHeats
    GROUP BY tblHeats.Heats, tblHeats.C, tblHeats.Mn, tblHeats.P, tblHeats.S;

    Cheers,
    This seems to have worked!!!!!!!!!!! Thank you so much!!!!!!
    (do I have to mark this as the answer or mark this topic as closed?)

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    You can mark it as solved in Thread Tools in the toolbar, top right
    Possibly add to Gicu's reputation with the star icon bottom left of a post?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. Replies: 7
    Last Post: 01-09-2021, 11:33 AM
  2. Continuous form to show Unique records
    By stewegg in forum Forms
    Replies: 7
    Last Post: 03-19-2017, 05:17 AM
  3. Can I make a calculated text field unique?
    By alexandervj in forum Access
    Replies: 3
    Last Post: 03-21-2014, 09:17 AM
  4. Replies: 3
    Last Post: 02-12-2014, 12:18 PM
  5. Replies: 3
    Last Post: 12-04-2013, 08:14 AM

Tags for this Thread

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