0

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.

2

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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.