Pulling similar data from same table (1 Viewer)

wolves1

Registered User.
Local time
Today, 00:40
Joined
Feb 3, 2019
Messages
22
Hello,

I'm sort of new to creating Access queries and would like to see if I can get some help. I would like to know if it's possible to create a query to pull similar data sets from the same table or do I need to create a secondary query first. Below is my problem:

The main table is called "tblSample". Within the table, there's 3 columns I want to pull the data from and it's called "TypeCode", "ControlNo", and "Year". Within "TypeCode" column, there's a large set of key codes, but I mainly need to find all codes that are "1501" or "1502" which stands for replacements (this part I got). "ControlNo" column shows the original item number (ex: 123456). If there's an item that got replaced, a new entry should've been created, but an "I" or "F" was added to the end of the Control No (ex: 123456F) and the type code would be "1501" or "1502".

I need help on how to create a query where I can pull all replacement item "ControlNo" and also show the original item "ControlNo" associated with it because I need to make edits to the "Year" column of the replacement items to match the "Year" of the original items.

For example:
Original ControlNo: 123456, Type Code: 0221, Year: 12
Replacement ControlNo: 123456F, Type Code: 1501, Year: 14

I would need to match the replacement ControlNo year to match the original ControlNo year. Hopefully this makes sense. Any help is appreciated!
 

plog

Banishment Pending
Local time
Today, 02:40
Joined
May 11, 2011
Messages
11,613
The best way to communicate data needs is via data. Can you please provide 2 sets of sample data:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what data you expect your query to return when you feed it the data from A.

Again, 2 sets of data--starting and expected results.
 

Micron

AWF VIP
Local time
Today, 03:40
Joined
Oct 20, 2018
Messages
3,476
Agree that the samples will help, but figured I should ask this: could you have additional suffixes besides F for the same ControlNo? If so, these should be kept in a different table, which would make your problem a breeze to solve as you'd link on ControlNo = ControlNo between tables. If fact, it might not be a bad idea to do this even if there can be only one variation.
 

jdraw

Super Moderator
Staff member
Local time
Today, 03:40
Joined
Jan 23, 2006
Messages
15,364
I agree with the previous posters. A question (curious) What happens if/when you have a replacement for 123456F ?
 

wolves1

Registered User.
Local time
Today, 00:40
Joined
Feb 3, 2019
Messages
22
Hello,

I have attached the 2 sets of sample data.

1. The sample database containing all of the data (starting).

2. The expected results of the data the query would pull.

To answer the other questions, there's no other suffixes only "I" or "F" is used for replacement items in the ControlNo. Ideally, I think having it in a different table would be better as well, but unfortunately I'm not the creator of the database.
 

Attachments

  • Expected Data.accdb
    416 KB · Views: 101
  • Sample Database.accdb
    376 KB · Views: 89

wolves1

Registered User.
Local time
Today, 00:40
Joined
Feb 3, 2019
Messages
22
The best way to communicate data needs is via data. Can you please provide 2 sets of sample data:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what data you expect your query to return when you feed it the data from A.

Again, 2 sets of data--starting and expected results.

I have attached the files below the other replies.
 

jdraw

Super Moderator
Staff member
Local time
Today, 03:40
Joined
Jan 23, 2006
Messages
15,364
You might consider 2 queries, but the separate table would be preferred.

1: QRecordsWithTrailingF
Code:
SELECT  sample.CONTROLNO
, sample.PRODYEAR
, sample.TYPECODE
, sample.[PRODUCT NAME]
FROM sample
Where Right([CONTROLNO],1)="F" ;

2:This is a select query, but could be the base for an Update approch based on your needs(untested)
Code:
SELECT sample.*
, QRecordsWithTrailingF.*
FROM sample INNER JOIN
QRecordsWithTrailingF ON
sample.CONTROLNO = Left(QRecordsWithTrailingF.CONTROLNO,9)

Output from 2:

FinalDataToDisplay FinalDataToDisplay

sample.CONTROLNOsample.PRODYEARsample.TYPECODEsample.PRODUCT NAMEQRecordsWithTrailingF.CONTROLNOQRecordsWithTrailingF.PRODYEARQRecordsWithTrailingF.TYPECODEQRecordsWithTrailingF.PRODUCT NAME
16010000412/130115product cookies160100004F18/191502product cookies
16010000816/170103product food160100008F18/191502product food
16010001515/160103product redding160100015F18/191502product redding
16010000315/160103product feather160100003F17/181502product feather
16010001910/115439product sections160100019F16/171502product sessions
 

plog

Banishment Pending
Local time
Today, 02:40
Joined
May 11, 2011
Messages
11,613
The proper way to store your data is with each piece of discrete data in its own field. That means there shouldn't ever be "suffixes" or "prefixes" to data--that data should go into its own field. I created a query to do this, but you should explore fixing the storage.

Attached is a database that has queries which will produce the results you expect.

sub_1: Cleans up your sample table by seperating account from suffixes, fixes field names with spaces

sub_2: identifies account numbers with 1501 or 1502 type codes

Main: produces the result you want by merging the 2 subs.
 

Attachments

  • sample.accdb
    536 KB · Views: 110

Users who are viewing this thread

Top Bottom