Help with a query. See attached database

[lehi53]

I am trying to create a query that pulls people into a report if they have EPLI sold in a specific month, or Accidental death, or cyber insurance or Occupational Acc all in a specific month. The way it works is they click the box on the Contact form next to the item they sold. Enter the premium, and the date they sold the product.

Then on the "all products current month second version" query is should pull in the names that fit that criteria. RIght now it does pull someone into the report. But the issue is if we sell EPLI in February, they will be pulled into the query, But then if I sold a cyber insurance in january that also shows on the report. So I only need the policy name with the actual product that was sold in that particular month.



CRM-Version-3.zip

[June7]

The CG Info table is not normalized structure. Multiple Yes/No fields for similar data is not normalized. This makes for very difficult (nearly impossible) filtering. I have tried to help posters with this and find it too frustrating. Products purchased should be in a related table.

tblCGDetails
ID (PK)
ContactID (FK)
ProductID (EPLI, Cyber, ADD, OAI)
DateSold
Premium

[orange]

I agree that table needs to be normalized.
Here is a tutorial on designing your tables and relationships that may be helpful.

Good luck with your project.

[lehi53]

I agree that table needs to be normalized.
Here is a tutorial on designing your tables and relationships that may be helpful.

Good luck with your project.

Thank you for your tips. Ill take a look at the document you posted and see if I can figure it out. Maybe when I make some changes you can take a look? Any quick tips on normalizing?

[lehi53]

The CG Info table is not normalized structure. Multiple Yes/No fields for similar data is not normalized. This makes for very difficult (nearly impossible) filtering. I have tried to help posters with this and find it too frustrating. Products purchased should be in a related table.

tblCGDetails
ID (PK)
ContactID (FK)
ProductID (EPLI, Cyber, ADD, OAI)
DateSold
Premium

can you explain these a little bit. Are those the fields you would put into a separate table?

[June7]

Yes. That is related (dependent, child) table.

ProductID field would have values EPLI, Cyber, ADD, OAI.

I was having a problem with the link posted by orange. Here is link to that site home page http://www.rogersaccesslibrary.com/

It is a balancing act between normalization and ease of data entry/edit. Follow the rules until you need to break them. I mean there can be too much as well as too little normalization.

The Yes/No fields are actually redundant anyway. If there is data in the premium field for a product, then obviously the product was sold. Although don't see a premium field for each product.

[lehi53]

Yes. That is related (dependent, child) table.

ProductID field would have values EPLI, Cyber, ADD, OAI.

I was having a problem with the link posted by orange. Here is link to that site home page http://www.rogersaccesslibrary.com/

It is a balancing act between normalization and ease of data entry/edit. Follow the rules until you need to break them. I mean there can be too much as well as too little normalization.

The Yes/No fields are actually redundant anyway. If there is data in the premium field for a product, then obviously the product was sold. Although don't see a premium field for each product.

So I am still lost on how to do this. I am a real beginner. For me to be able to create those queries I was referring to what do I need to pull out of the CG info table and put into its own table. I have a check box for products sold. For each product. Then I need to attach a date to the sale and a premium amount that was sold. Then I need to connect that to an contact in the database.

[June7]

Post 2 suggests the related table.

[lehi53]

So I create that table with those details you mentioned.

tblCGDetails - Name of the table
ID (PK) - This is the primary Key.
ContactID (FK) -Foreign key.
ProductID (EPLI, Cyber, ADD, OAI) This one I am confused about. Is this a third key. Or is the column name just product ID?
DateSold
Premium

[June7]

Field named ProductID (or ProductCode if you prefer). It will have values of EPLI, Cyber, ADD, OAI which means up to 4 records for each ContactID, unless you expand to more products.

[lehi53]

Field named ProductID (or ProductCode if you prefer). It will have values of EPLI, Cyber, ADD, OAI which means up to 4 records for each ContactID, unless you expand to more products.

SO will EPLI cyber, add and OAI be a drop down box with values that come from a different table?

[June7]

A combobox would be useful. The dropdown RowSource can be a static Value List or can be table source. The table allows more flexibility for product expansion.

[lehi53]

I added all the columns but the Contact ID. To add that to the table do I do a lookup and relationship?

[June7]

I NEVER create lookups in tables. I build comboboxes on forms. Regardless, create the field.

If you want to build relationship in Relationships window, do so.

[lehi53]

Im still confused and hopefully this is the last question. How do I add the ContactID field to the new table as a foreign key without doing a lookup & relationship and make the records connected.