Completly lost...

[fairytalesrcute]

I am in the process of setting up a basic database for my company and am stuck on a feature I am trying to acheive. I am a novice Access user and do not know where to go from here.



I am trying to acheive the most current information from all columns for the input records with no duplicates.

For example:
Table Name: Insurance
Fields: Vendor, BrokerDate, GLLimit, GLEXP, GLEND, AUTOLimit, AUTOEXP, AUTOEND, AdditionalInsureds

Each different certificate we get is entered in. The problem is we may receive a certificate from Vendor for only the GLLimit and GLEXP and not GLEND. We may also get a certificate that lists off the GL items and not the Auto items. To complicate it the two certificates might name different additional insureds.

What I am trying to retreive is only the certificates that are not expired and are endorsed correctly for each vendor.

Right now I have one large table with the above fields, plus other types of insurance, and filter out each type of insurance in its own query with conditions meeting what I want. The queries come out fine, but I want something in one table or query, not 5 different queries for each type of insurance.

Is this even possible?

[CraigDolphin]

I think this comes down to a failure to normalize your database tabe design.

Now, I know nothing about your business or insurance types, so this advice may be missing something but it seems to me that you have an entity called certificates that are supplied to you by another entity called vendors. Each certificate can have one or more insurance types (another entity) associated with it. Correct so far?

If so, then you need at least four tables.

tblCertificates
CertificateID (auto,pk)
CertificateDate
VendorID (fk)

tblVendors
VendorID (auto,pk)
VendorName
VendorStreetAddress
VendorCityAddress
etc

tblInsuranceTypes
InsuranceTypeID (auto,pk)
InsuranceTYpeName (text)

tblCertificateDetails
CertificateDetailID (auto, pk)
CertificateID (fk)
InsuranceTypeID (fk)

(Note: you do not say what values are stored in the table in each of the columns so if there's some kind of meaningful value that you need to sum, average, whatever, you may need to add an extra field in tblCertificateDetails to hold that information)

With this kind of table structure, you would have a main form in single form view bound to tblCertificates, and a subform in continuous form view bound to tblCertificateDetails (linked by the CertificateID field in both)

Whenever you receive a new certificate, you would open the main form to a new record, enter the certificate information (like the vendor and date), then enter in the types of insurance in the subform that are associated with that certificate (and any relevant values associated with them if applicable).

Then, to create a list of the most recent entry for each insurance type by vendor, you'd simply build a query that groups by the vendorname and the insurance type, and uses the max certificate date.