Multiple search only returning records I want (1 Viewer)

[mounty76]

Hello!

I have a db which has amongst others 5 columns with expiry dates in for certificates, all the dates are different.

I've made a query that searches all the expiry dates which are <Now()+60, this is fine, however it then returns all the columns for that record. Example:

Name: Paul
Cert 1 : 12/7/19 (within 60 days of today)
Cert 2 : 13/8/19 (within 60 days of today)
Cert 3 : 25/12/19
Cert 4 : 01/09/21

When the query runs because cert 1 and 2 are within the 60 days it picks this record from the table, but it also shows certs 3 and 4 because they are in the same record.

Is there anyway that the query can run and only show cert 1 and 2 in the above example, although cert 3 and 4 are in the same record they aren't within the <Now()+60.

But I want the query to search all these dates and then only return the fields that are true to the criteria, not every column for that record.

Does that make sense!

Thanks very much in advance!

 

[plog]

Time to set up your tables properly. When you start numbering field names (Cert1, Cert2, Cert3, ...) it's time for a new table for that data. Instead of jamming all cert data into the table you have, you need a new one:

Certifications
IDCert, autonumber primary key
FKExistingTable, number, foreign key to the table you currently have
CertDAte, date, date the certification expires
CertNumber, number, will hold the number of the cert which is currently in the field name

That's it, that table will now hold every cert for everyone. For every certification you have it now becomes a row in this table. So, instead of filling out 5 columns in the table you now have you will have 5 rows in this new table.

Do that and you query becomes trivial, and you avoid a lot of other issues you are heading towards..

 

[mounty76]

That makes sense, however there is much more data in the table than just certificates. I understand I could link the tables together in a sense that the primary key in the main table for one persons record could be expressed in the cert table for each of that persons certificates which then ties them together.

My question is.....when I create a new record in a form how do I then enter certificates into a different table but are tied to the primary key in the main table for that person.

Hope that makes sense!

 

[theDBguy]

That makes sense, however there is much more data in the table than just certificates. I understand I could link the tables together in a sense that the primary key in the main table for one persons record could be expressed in the cert table for each of that persons certificates which then ties them together.

My question is.....when I create a new record in a form how do I then enter certificates into a different table but are tied to the primary key in the main table for that person.

Hope that makes sense!

Hi. Pardon me for jumping in... When you have a child table related to a parent table, the usual approach for entering related data is by using a form/subform setup. The main form will be bound to the parent table and the subform will be bound to the child table.

 

[plog]

DBguy is correct about forms/subforms. That's how you input data into tables in a one to many relationship. However, I think you need to put down forms and reports for right now:

there is much more data in the table than just certificates.

You need to focus on your tables and fields right now. The process of setting them up properly in a database is called normalization (https://en.wikipedia.org/wiki/Database_normalization). Check that link out, read up on it and work through a few tutorials. Then apply those principles to your data. Finally, set up the relationship tool in Access and post a screenshot of it back here. We can help you get things correct for your data.