filtering records

[guest]

hi,


I have hard one and I don't even if the title is appropriate. its about this. i have huge single table. from the table i need to extract records which are connected from different years. i simplified table for preview and with Criteria: Like "*/????" I got records which are connected and i got query like picture table. field transfer is actually connected fields doc number and year. however there are records which are not filtered because they don't contain in field transfer year. few added records like example in table2 with bold show records that should be added and third can those records be sorted starting with lowest year like in picture table3.
Thanks

[Welshgasman]

You need to show your query, as your description makes little sense to me , and if you cannot adequately describe the issue, it is hard to help correctly.
If you can get table 2 by some criteria, then you could perhaps use a Union query, but likely your criteria is not correct?

[guest]

using the criteria above i got query exactly like table1. I need to get additional records like in table2 but i don't know how to that. if i get it is not problem to make union query. Hoping get some suggestion or example. (criteria is in the field transfer). do you want picture of query?

[June7]

Provide sample raw data as a text table, not image. Include records that should not be retrieved because from what I see so far, every record would be retrieved. Just searching for / character would get the sample output.

Copy/paste into post or use table builder on the advanced editor. Or attach a file - Access or Excel.

Why does Doc number 768 show two Transfer values? This really should be 2 records. Similarly for 347 sub 2 and 64 sub 2.

Bad data in means bad data out.

Also provide attempted SQL as text, not image.

[guest]

768 values show two values because document started with first value 347 from 2016 continued to 768 to 2019 and 768 continued to 231 from 2025. if there are more then two connections like in this sample three previous and next are entered. here is the sql from the query and i will also send the table and query.
SELECT DatePart("yyyy",[DateofReceivng]) AS [Year Searching], DelovodnikForum.[Doc number], DelovodnikForum.Transfer, DelovodnikForum.Subnumber, DelovodnikForum.DateofReceivng
FROM DelovodnikForum
WHERE (((DelovodnikForum.Transfer) Like "*/????"))
ORDER BY DatePart("yyyy",[DateofReceivng]), DelovodnikForum.[Doc number], DelovodnikForum.Subnumber;
here is the database too.

[Welshgasman]

Well the query output looks nothing like your table pictures , so no clue what you want.

[guest]

pictures show only few records not whole database to be clear what i want to do. if you open query first filtered record from database is with doc number 64 from 2017. if we now open table and filter doc number 64 we get 13 records wit doc number 64. last one is one in the query. My problem is how to add another 12 records with doc number 64 from 2017. next doc number in query is 93 from 2017 and so on with all records from the query.

[June7]

Still, multiple discrete data elements saved into 1 field is poor design. However, your posted db does not have this 768 record.

Combining Doc number and Year as a single field value is also poor design. Really should be two fields.

Retrieving records based on data in other records of same table is not simple.

Rules for filtering records still not clear. Why would records with N/A in Transfer be retrieved?

What steps would you follow to manually sort out records. What rule(s) apply for the selection of these additional 12 records?

When I filter on 64 I get 48 records, 13 are in 2017 - why exclude the others?

Simplify year extraction with Year(DateofReceivng)

[guest]

in original version i have a lot of queries forms reports etc. there are also combo boxes... i deleted them because they not relevant for this. N/A is to avoid problems with empty fields for combo boxes. also with n/a i can choose to show all records which have that field empty. those records are records that do not end in one year. others start and finish in one year so that field is n/a or something without year which mean that record continue between two records but at the end finish in the year they started. records are entered by dates no empty records can be left. so connections shows there are other records from that group ( if i can say group).

[madpiet]

How are these things "connected"? What do the two numbers in the TransferNumber mean? If you can explain what these things mean in plain English, then people here can maybe help you, but without understanding what any of this means, I don't think anyone can help. Is that TransferFROM/TransferTO? (In which case that column should be split in two?)

If that's what you want, you need INSTR() to return the position of the "/" and then LEFT() to get the first number (LEFT([FromToString],INSTR(1,[FromToString],"/")-1) <--- but beware, I don't remember the exact order of arguments there, so you'll have to play with that. Then you do the same with the right side
RIGHT ( [FromToString], LEN([FromToString]) - INSTR([1,[FromToString],"/) )
(you may need to adjust by adding or subtracting 1 to from that LEN - INSTR to include/exclude a character, but that's stupid easy.

Hope this helps. (Sorry, I didn't want to do the whole thing for you... but give it a try and if you get stuck, post the query and what's wrong with it).

[guest]

doc number is something like number of the project. same doc number from same year means more documents from that project as they follow. if last record have in field transfer after slash symbol four digits it means its year and first part of transfer field is doc number. but i don't need to separate this field. see the database. query i made gives only the last record of doc number. i need to get the rest of records with same doc number. job with transfer is finished. i already have all projects that do not end in same year. but query show only last doc number of each project or first record i need other records with same doc number to add. can be other query then merge first with second with union or whatever. actually work should be done with doc numbers by years. in my original version with combo i can get all records of one project for one year but cant with other years because they are not same number and year. if i filter all years with combo i will get all same numbers of all years but they wont be necessary records which transfer from year to year. that's why i want to separate how to say group of project from different years in one group (projects about same thing with transfer that last more than year) and later i will do forms reports etc. if i work with second query how do i get results from first query to work with. also difficult because these is not one value there can be a lot of projects so how to work with each.

[Gicu]

Please have a look at Query1 in the attached file and let us know if that is what you're after.
Cheers,

[guest]

yes man that's exactly what i was looking for, just must check that all are there. Thank you.

[guest]

Gicu,
I know i said it is solved but i found a mistake. why in year 2021 doc number 19 has duplicate records. they appear only once in the table.

[Welshgasman]

Gicu,
I know i said it is solved but i found a mistake. why in year 2021 doc number 19 has duplicate records. they appear only once in the table.

I see 21 entries, but cannot find a duplicate?