Ms-access – Concatenate 2-3 table field values in 1 table field in access

concatms access

I have a project that in Access. The problem is I am a newbie in this application and I really couldn't figure out what to do, I have checked out several ways but am still stuck.

I'll be handling a survey file that will maintain answers per customer. The visit to customer is done monthly in a year, which means that a customer can have 12 visits in a year.

My problem is that if I will maintain this project for the next five years or more, I have to maintain another field in my table that will block the duplicate entries.

It would be better to attach the file but I couldn't find a way. Here's the layout I would like to have:

From my table, field 1 will be where it concatenates the other fields. Year as field 2, Month(field 3), Customer Code (field 4) and so on for 10 surveyed products.

I need to concatenate field 2, 3 & 4 in this way I can see if the customer was entered on that particular month and year and I can arrange a warning if already exist within my form.

Best Answer

You shouldn't need to concatenate the fields at all. You can find out whether a customer has filled out the survey for a given month and year by using all 3 columns:

SELECT * FROM Surveys 
WHERE CustomerID = 1 
AND SurveyMonth = Month(Date())
AND SurveyYear = Year(Date())

The Date() function returns the current system date. The Month() and Year() functions return the month and year, respectively, of the date passed to them.

A suggestion: store the entire date a survey was taken as a Date type in the database rather than using separate columns for month and year. This will simplify lookups and storage, making the query above look more like:

SELECT * FROM Surveys 
WHERE CustomerID = 1 
AND Month(SurveyDate) = Month(Date())
AND Year(SurveyDate) = Year(Date())

where SurveyDate is a column of Date type.

Related Solutions

MS Access – Concatenate Columns Uniquely on the Same Table

Looks like you just need to add a WHERE filter to your query:

SELECT
  A, B, C, ...
FROM
  YourTable
WHERE
  D IN ('IA1', 'IA2', 'IA3')
GROUP BY
  A, B, C
;

That way those A, B, C groups that do not have 'IA1', 'IA2' or 'IA3' in column D will be excluded from the output.

Ms-access – How to calculate a field based on multiple non-numeric records

the solution that I thought involves creating a calculated column on table2 called the_level which will give you the exact level depending on the ActivityCode.

the second step is on your query, where you join table1 and table2, there you need to identify whether or not the item is entered within the timeframe set by the query, and change the the_level accordingly.

the_level has been created as PERSISTED, you did not mention indexes and performance but you said billion hours, so you will need some indexes there.

this is an example of calculated column with case and not null.

I have, in the past, create calculated columns that reference a different table, but in my environment the performance was not the best. Somehow it slowed writes, but I had an index on the calculated column. I am not stating this is a rule, but I would monitor it before doing it on a busy updated table.

    create table table2(

    [ActivityCode]  varchar(10) not null,
    [ActivityDate]  datetime,
    [ActivityOutcome] int,
    [ActivityEval?] char(3) not null default('Yes')
    )
    go

    -- add the facilityID, as a primary key at least for the exercise
    alter table table2
    add [FacilityID] int not null identity(1,1) primary key clustered
    go

    --insert the activity codes 
    insert into table2 ([ActivityCode])
     select  'EVALA' 
union all select  'EVALB'
union all select  'EVALC'
union all select  'EVALD'
union all select  'EVALE'
union all select  'EVALF'
union all select  'EVALG'
union all select  'EVALH'
union all select  'EVALI'
union all select  'EVALJ'
union all select  'NOEVALA'
union all select  'NOEVALB' 
union all select  'NOEVALC' 
union all select  'NOEVALD' 
     go

     -- have a look at the table
     select * from table2

     -- test the calculation of the the_level column
     select [ActivityCode],
            the_level = 
            CASE WHEN ActivityCode    LIKE '%EVALA%' OR
                      ActivityCode    LIKE '%EVALB%' OR
                      ActivityCode    LIKE '%EVALF%' OR
                      ActivityCode    LIKE '%EVALG%' OR
                      ActivityCode    LIKE '%EVALH%' OR
                      ActivityCode    LIKE '%EVALI%' OR 
                      ActivityCode    LIKE '%EVALJ%'THEN 1
              ELSE  
              CASE WHEN ActivityCode    LIKE '%EVALC%' OR
                        ActivityCode    LIKE '%EVALD%' OR
                        ActivityCode    LIKE '%EVALE%' THEN 2
                 ELSE 0
              END
            END
    FROM TABLE2

    ----------------------------------------------------
    -- ADDED the_level as A persisted column to table2
    ----------------------------------------------------
     alter table table2
    add  the_level AS ISNULL(
    CASE WHEN ActivityCode    LIKE '%EVALA%' OR
                      ActivityCode    LIKE '%EVALB%' OR
                      ActivityCode    LIKE '%EVALF%' OR
                      ActivityCode    LIKE '%EVALG%' OR
                      ActivityCode    LIKE '%EVALH%' OR
                      ActivityCode    LIKE '%EVALI%' OR 
                      ActivityCode    LIKE '%EVALJ%'THEN 1
              ELSE  
              CASE WHEN ActivityCode    LIKE '%EVALC%' OR
                        ActivityCode    LIKE '%EVALD%' OR
                        ActivityCode    LIKE '%EVALE%' THEN 2
                 ELSE 0
              END
    END,0) PERSISTED



    -- after this do in your query something like this
    -- maybe the case in the line 
    --  WHEN date_entered BETWEEN '2016-04-01' AND '2016-04-07' 
    -- does not need to exist

    -- you will need to add your business logic on the query below
    -- but at least you have the level calculated

    select T1.[FacilityID]
    t2.[ActivityCode], 
    T2.[ActivityOutcome], 

    the_FINAL_level = CASE 
        WHEN date_entered BETWEEN '2016-04-01' AND '2016-04-07' THEN 
             CASE WHEN T2.THE_LEVEL = 1 THEN T2.THE_LEVEL+1
                  ELSE 
                   CASE WHEN T2.THE_LEVEL = 2 THEN THE_LEVEL+1
                   ELSE -- ADD BUSINESS LOGIC HERE
                   END
              END
        ELSE 0
    END

    FROM TABLE1 T1
    INNER JOIN TABLE2 T2
            ON T1.[FacilityID] = T2.[FacilityID]
    WHERE T2.[ActivityDate] BETWEEN '2016-04-01' AND '2016-04-07'
      AND T2.[ActivityEval?] ='Yes'

Best Answer

Related Solutions

MS Access – Concatenate Columns Uniquely on the Same Table

Ms-access – How to calculate a field based on multiple non-numeric records

Related Question