Access: Field or fields to contain multiple certifications

[jeffreybrown]

I'm hoping somebody can walk me thru the best way to achieve this result.

In my database are multiple people who can have up to 4 certs

	A​	B​	C​	D​	E​
1​	User​	Cert1​	Cert2​	Cert3​	Cert4​
2​	Person A​	0101​	0201​	0695​	0923​
3​	Person B​	0101​	0301​	0545​	​
4​	Person C​	0151​	0545​	​	​
5​	Person D​	0151​	​	​	​

Data will be entered via a form which has 4 field names: Cert1, Cert2, Cert3, and Cert4 (record source is a table)

In the end, I would like to pull a report which will tell me all the people that have a certain cert.

If the report is going to show cert 0101, then I should get Person A and Person B

If the report is going to show cert 0545, then I should get Person C and Person D

I'm good with the report and the queries, but my question is about the procedure.

I would like to have one column which would show the series and the persons name, but I built four field names in the table.

Is this the only logical way to do it?

Edit: I've only used the table above to display how the data is contained in the access table.

 

[RoryA]

Your table really ought to only have two columns rather than 5, then the report would be simple.

 

[jeffreybrown]

Hi Rory,

But the problem, if the employee will have more than one cert, then how can I show those certs in one column?

I know it could be entered as a string, separated by say a comma, but then I could not build a report to only show who holds a certain cert.

 

[RoryA]

You'd have one row per employee/cert combination. So any employee might have up to 4 rows.

You can do it with what you have, using a query with 4 OR criteria, but it would be better design to use just 2 columns IMO.

 

[jeffreybrown]

but it would be better design to use just 2 columns IMO.

Unfortunately the design of this database is not mine (and believe me there are many other things that could be fixed, but I don't have the time for those).

I am just helping a co-worker who is piecing this together, but really doesn't have a grasp for Access. Not saying I do either, but can get by in a pinch. Maybe

I'll play around a little more to see what I can come up with. Thanks.

 

[RoryA]

Your SQL would look something like this (I've used a parameter but you could just use a control on a form):

PARAMETERS [Enter cert no] Text ( 255 );
SELECT T.User, [Enter cert no] AS Cert
FROM Tablename T
WHERE (((T.Cert1)=[Enter cert no])) OR (((T.Cert2)=[Enter cert no])) OR (((T.Cert3)=[Enter cert no])) OR (((T.Cert4)=[Enter cert no]));

 

[jeffreybrown]

Hi Rory,

That is basically what I did.

I designed a parameter query, designed the report, and finally added a cmd button to the form. All is working fine. Thank you for the help and guidance.