UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Help With A Query, Access 2016    
 
   
Eightball3
post Nov 22 2019, 02:06 PM
Post#1



Posts: 406
Joined: 6-May 10



Hi All,
Hoping for a little help with a query that I can't seem to figure out.

I have a table that contains a field called PartCode. This field is a 6 character field that ends with two characters that will be anything from AA, AB, AC, AD.....BA, BB, BC, BD.....CA, CB, CC, CD....etc. The last code would be ZZ. I call the last two characters the PartCodeSuffix. So the part code would be made up of 4 characters plus the last two characters as described e.g. 4455DG, E1G4CX, 3300ZZ etc. The last two characters can be used in many different part numbers as long as the first 4 characters do not duplicate.

Now for the query.

I am trying to write a query that will look at the parts table (all records) and return all the suffixes that HAVE NOT been used yet. Can anyone help me with this? Thanks!
Go to the top of the page
 
theDBguy
post Nov 22 2019, 02:34 PM
Post#2


UA Moderator
Posts: 76,800
Joined: 19-June 07
From: SunnySandyEggo


Hi. If you have a table with a list of all possible code suffix, then you might be able to use a query similar to the find unmatched query.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Eightball3
post Nov 22 2019, 06:09 PM
Post#3



Posts: 406
Joined: 6-May 10



OK, I built the table with all the suffixes but can't seem to figure out how to join the two tables in the query. I would need to use something like Right(PartNumber,2) as one side of the join. Not sure how to do that.
Go to the top of the page
 
Eightball3
post Nov 22 2019, 06:18 PM
Post#4



Posts: 406
Joined: 6-May 10



Got it! Thanks for the help DBguy!
Go to the top of the page
 
theDBguy
post Nov 22 2019, 06:41 PM
Post#5


UA Moderator
Posts: 76,800
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    5th December 2019 - 07:48 PM