Concatenate 2-3 field values in table

[lmceb12]

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. I hope I deliver my question clearly, and hope you could help me guys. I attaching my file for better understanding.

Thanks a lot!!!

 

[plog]

You should really read up on data normalization (http://en.wikipedia.org/wiki/Database_normalization). Its the process of laying out your tables. Additionally, I'm sure there are a lot of databases out there that will help you lay out yours.

I can't really follow you when you say you need another field to block duplicates. What exactly do you view as a duplicate? I can't see how duplicates would be introduced.

Lastly, here's some issues I see:

1. Spaces in field names. It just makes coding harder when you use non-alphanumeric characters in field and object (tables, queries, forms, etc.) names. I recommend removing the spaces and just merge the words together by capitilazing a new word.

2. [Period Year], [Period Month] should be 1 field. When working with dates, make your data dates, not numbers and/or text fields. Doing this allows you to take advantage of the built in Date functions of access and let's you more easily sort your data. If you use text for months, the first one will be April, then August, then December, all the way to the last month--September.

3. Use Yes/No datatypes, not text. Access has a Yes/No (True/False) datatype. Use it instead of text for all those Product fields.

4. You need a new table for that Product data. This is where you need to read up on normalization. When you start numerating fields (Product1, Product2, ...) it's time to break that data into a new table. Instead of expanding your data horizontally (with more columns), you need to add data vertically (with more rows).

 

[lmceb12]

Thanks plog for placing interest on this, I guess i have raised my query a little illogical. You are right about your points and i will do something about the spaces in my fields, but let me stress on the point of duplicates and why i split the fields, what exactly I am trying to do is that concatenating the dates (month and year) and Customer Code field in order to know if it is entered already. For example, if you look at the data on the file, entry series field 1 and 72 is the same customer code (18882) but was entered in different month, but there will be case that an entry is within the same month, this is the one I am trying to avoid because a customer can only be visited once in a month, which connects to another matter where I will run this program for long time, that's why i made a year field.

Thanks again for taking some time on this. I hope I made it clear this time.

Regards,

 

[mikejaytlabustro]

I agree with Plog. Maybe you should reconstruct your tables like this (See attached screenshot) to make it simple.

 

[plog]

Thanks for the duplicate explanation. My advice applies, but not to the structure you currently have. You are going to need a composite key (google this "Access Composite Key" for more explanation). Essentially, its a way to make a primary key out of more than one field. A composite key will ensure no duplicates as you defined.

That composite key would go into that new table I suggested where you store all the True/False data for specific products.

 

[lmceb12]

Thank plog and mikejaytlabustro for taking some time on this, truly appreciated. I will try to work according to your suggestions, and see what will happen, maybe I'll raise another query along the way.

Once again, thanks a lot!

Cheers.