Creating tables that contains fields of multiple values - Database creation HELP!

[McArthurGDM]

So I have records that have fields that contain multiple values:

For example:

|ID |------ |Agency| ------ |EntitiesImpacted| ------ |Content|
_______________________________________________
1 | ---| SEC, FED, FDIC | ---| SBNA, BSPR | -------- | Rules
2 | -- | FED | ---------------| SSLLC | ---------------| Bulletin
3 | -- | SEC, OCC | ---------| BSPR, Totta | ---------| Rules


Sorry for the terrible image. Hopefully you get the idea, though.



I originally created this table using a Relationship & Lookup field, but that caused a ton of querying problems. I'm not sure how to effectively set up a database that has fields with multiple values per record in the field!

Any advice is greatly appreciated.

[orange]

I strongly suggest you design traditional tables and relationships and not use multivaluedfields.
Also, here is a tutorial that, if you work through it, you will understand the concepts of tables and fields and relationships and normalization. It starts with a well documented description of the business the database will support.

Here is a list of free youtube videos and more that may help put some concepts into context.

You really need to identify your business rules before you start design. By identifying and verifying the business rules you will learn a lot about your business and it will help you considerably with your database design.

Good luck.

[McArthurGDM]

I understand what you are saying. I have tried a number of attempts to create various tables and relationships that just do not get the desired result.

I've tried inputting the data into my table as "SBNA, BSPR", etc... but this dosen't work when querying even with the LIKE function.

I've tried updating fields with queries and what not. I've tried a number of approaches and I'm just at my witts end. I need help and not links to tutorials - though I do appreciate the sentiment.

[orange]

Did you work through the tutorial, or watch the videos?
The most important part of an easy to use; easy to maintain database is design. It must be designed to meet your requirements. And you must know those requirements in detail in order have a successful design.

Good luck with your project.