Combine two records in a single (1 Viewer)

[r621]

@r621: I have shown a suggestion above that shows all the steps and implements them in a query. If the imaginary table name TableX is replaced by the existing one, the query should work immediately. Replace the name, accept the query and try it out, you should be able to do that, if not, you better find a service provider.

The statement to store all single steps in single queries and everything will be fine and suggests ten times as fast I think is nonsense. Serious investigations would confirm that, Josef showed approaches and results to it. But you can test this yourself as a second attempt.
Sometimes you have to see, feel, experience to have an answer to believe.

Hello , sorry for the delay of the responde but I was out, Yes I solved with query of EBS in post #5 very useful also the demo of post #6 of arnelgp. Thanks to all for your support, really appreciate! In not so familiar with database and SQL , I've just start to follow online tutorial and courses because I want to improve my knowledge and also need it for my Job, in any case I'm analyzing what you posted to me in order to understand what you did and in future I will apply similar or better solution

 

[Pat Hartman]

If you want to have a big performance debate, you should start splitting and indexing field contents from TAG in the table. Then one saves the calculated splitting in the query and gets into the position to use indexes.

I think you keep forgetting that Colin proved that the subselect is less efficient than the join. Granted, if you are retrieving 5 records, it makes absolutely no difference whatsoever what you do. But why would you want to always start with the solution that potentially has a problem in Access (I think we are working with Access here) rather than the solution that is known to be superior - except that you love your subselect format

 

[ebs17]

At least the subqueries show quite clearly that you have to decompose the given field tag, because you need parts of it for JOIN as well as for filters. The teaching character is thereby clearly higher, than if so far not existing fields ID and Type fall from the sky.

The teaching character is also shown by the fact that the query can be taken over and executed immediately after the correction of the field names.

the solution that potentially has a problem in Access

I obviously have fewer problems there than others, and I am not ashamed of it. I am then also much less filled with horror when there is functionally no other viable solution than subqueries.

You have written performance in small letters and talk about potential problems. But the bigger problem is the necessary normalization step and the indexing of the resulting single fields. Because index usage is a huge key for performance, and the separation of individual information from data conglomerates is, as many forum contributions show, a frequent problem.

So: Before you sweep the floor with the broom, you should first remove the big piles with the shovel.

 

[Pat Hartman]

I am then also much less filled with horror when there is functionally no other viable solution than subqueries.

I'm not sure why you think I'm filled with horror at the thought of writing a subquery. "Clear" is in the eyes of the beholder. If a left join solves the problem, why would I ever want to over complicate the solution by creating a subquery? Do you write a hundred lines of code when one will do? I prefer the simplest. most efficient solution whatever that is. I start from simple and move only as far into the complex as I need to. You seem to think the hundred lines of code must be better because they're beautifully formatted and color coded.

I was pretty sure we finished the efficiency discussion last time Colin showed you the stats but I guess you've forgotten. But let me say it again. If you are retrieving 5 records it makes absolutely no difference whatsoever what technique you use or whether Access has trouble optimizing your choice or not. If it ever matters, you can rethink the query.

 

[ebs17]

I prefer the simplest. most efficient solution

To repeat and recall: Your solution was not yet a solution, because it comes with unknown fields and does not really deal with the initial situation. Without functioning, any talk about efficiency is futile.

One line of code against 100 lines of code: This is a pathetic and exaggerated representation. One or the other will notice that I format SQL statements instead of just throwing the bunch in like this. There are inevitably more lines, but these offer thereby a better readability of the statement towards understanding. The latter points also belong to categories like simplicity.

 

[r621]

Dears, I've solved my matter as I told you , but for curiosity I've tried also wit this code:

select
T1.*, T2.*
from
(select * from InputT where TAG = 'A') as T1
inner join
(select * from InputT where TAG = 'B') as T2 ON T2.X = T1.X;

But this query is returning a popup requiring to insert T2.X and T1.X

the records are empty the column has all the fields required as per image

What I miss?

 

[Josef P.]

What I miss?

X (SCNR)

Which data field indicates that the records should be side by side? => Join T2.ThisField = T1.ThisField

 

[r621]

Sorry I didn't got the point...

 

[ebs17]

In order to use a field, it must exist in the specified table. In your attempt, the subqueries simply take the fields from the InputT table, where the X field does not exist.