Extract information and merge them

[donberti]

Hello everyone,

I need to match 3 tables and merge only some values present in 4 columns of the final list I am getting.



My data structure looks as follows:

Table 1 - contracts, sales status a, b, c , status 1 , 2 , 3 , more info in several columns that I simply leave as they are
Table 2 - people´s names
Table 3 - vendors name

I managed to query all of them and get an output. I merge then with a simple statement 4 columns I am interested in but I would like to specify some dependencies a bit better, so that I do not need to correct them again and again.

I need to merge :
1. A specific sales status (sales status "a" and sales status "b") with a further status 1, 2 or 3 (sales status and status are in 2 different columns). I managed to concatenate them all (all sales status with all status) but I would like to find a way to concatenate only some of the information. Example: Contract beta has a sales status new and a status in progress so the result should look like (in a single column): beta, new, in progress. All which is not sales status a or b should not be merged and stay as it is.

2. The output of number 1. with two other columns showing a date sent to customer and date sent to finance. The columns with the dates have blanks.

So my desired merge in one merged column should look like this :

contract beta, quoted (sales status a), awaiting requestor (status 2), with date sent to customer (no need for the date, the information that beta shows a date sent to customer is enough) or
contract delta, no quoted completed (sales status b) , work in progress (status 2), with date sent to finance.

I have of course no clue how to merger in a way that I can choose the specific sales status a or b. I do believe that it is possible with a select statement but I am not sure how should I enter it in access. And, of course, I am a bloody beginner...

Any hint or help is appreciated,
Have a great week-end!!
Dona Berti

[June7]

I expect would need nested IIf() expression. If the expression in query is too lengthy then build a custom function that constructs string using conditional code structures. The 4 fields would be passed in arguments of the function. Call the function from query or textbox.

[donberti]

Thank you a ton, I will try!!! Best regards, Dona