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

Question

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.

Answer 1

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.

Answer 2

You need a unique index on the SurveyMonth and SurveyYear fields, that will prevent duplicate entries.

You should really have a constraint on the month and year fields to avoid invalid numbers being entered.

To restrict these values, create additional tables storing permitted month and year values and then add a foreign key relationship to your main table.

This can be simpler to implement than a single date field, as you will end up splitting out the month & year and having to handle the day part of the date which could be tricky in Access.