Make a list of fields that aren't null

[kirkm1976]

I have a table that has several fields that contain a date if a user is licenced to use a certain piece of equipment and is null if not. Each recor has other data in it such as name, and a unique identifier (numeric). What I want to do is make a list of the fields which have a date in them to create a licence for what they can operate. Any help on which way to go...pretty new at access.

 

[rainman89]

if you use the query design wizard and select the appropriate fields that you want to have the list of, and in the criteria for the date field if you put is not null, this should return all entries with a value in that field

 

[kirkm1976]

Thanks, but I need a little more

That will indeed give me all the data in those fields, but what I am after is the field titles themselves

 

[Golfer]

sounds very likely that you have more than one field containing dates ??? WHY ?

If it's only the field titles you are after just click on design view for the table that contains your data and they will all be listed there.

 

[kirkm1976]

What I am doing is printing a licence for them (report prints on a card). I want to list the fields that have a date in them on the back.

 

[Rabbie]

In a well designed (and properly Normalised) database you should only have one field for dates for the licence. It sounds like you need to check your design is correct.

 

[kirkm1976]

hmm, but I have about 15 pieces of equipment that can all have a different date. How would I get it all in one field?

 

[Rabbie]

hmm, but I have about 15 pieces of equipment that can all have a different date. How would I get it all in one field?

This suggests that your data is not normalised. Unless you normalise the data, everything will be much harder for you.

See this link for more info. There is a lot of info about normalisation on the Web and in Access Help. It will be well worth your while to read up on it.

 

[kirkm1976]

hmm

So I should have a seperate table for each piece of equipment then consisting of the unique identifier and the date of certification?

 

[Rabbie]

So I should have a seperate table for each piece of equipment then consisting of the unique identifier and the date of certification?

Not necessarily. You could have an Equipment table which would contain the Unique identifier, a field defining the equipment type, and the date of certification. The choice is yours.

What you shouldn,t have is a table which has fields for 15 dates. What happens if you need to add 5 more equipment types later.

 

[boblarson]

No, I don't think you're understanding normalization. You would have a SINGLE table for all of the equipment and then you would have a table for certification dates for that equipment because a piece of equipment could have many cert dates and so with a one-to-many relationship, you would need to reflect that.

The thing is, you need to think thin and tall when thinking in relational database terms as opposed to short and wide when thinking in spreadsheet terms.

Here's another normalization link that might help:
http://support.microsoft.com/kb/283878

 

[boblarson]

So to fly with Rabbie's suggestion:

Table - tblEquipment
EquipmentID - Autonumber (or serial number is good)(PK)
EquipmentDescription - Text
Other specific attributes can be here too as long as they are a one-to-one relationship

Table - tblCertTypes
CertTypeID - Autonumber (PK)
CertTypeDescription

Table - tblCertifications
CertificationID - Autonumber (PK)
EquipmentID - Long Integer (FK)
CertTypeID - Long Integer (FK)
CertDate - Short Date

 

[kirkm1976]

No, I don't think you're understanding normalization. You would have a SINGLE table for all of the equipment and then you would have a table for certification dates for that equipment because a piece of equipment could have many cert dates and so with a one-to-many relationship, you would need to reflect that.

The thing is, you need to think thin and tall when thinking in relational database terms as opposed to short and wide when thinking in spreadsheet terms.

Here's another normalization link that might help:
http://support.microsoft.com/kb/283878

OK, I think I am starting to follow, but how would these many dates be connected to a single person (The unique ID is the Employee ID)

 

[boblarson]

Can the employee be entered many times for different equipment?

 

[kirkm1976]

Can the employee be entered many times for different equipment?

Yes. The employee can be certified on any or all of the equipment and could thus be entered multiple times

 

[boblarson]

Yes. The employee can be certified on any or all of the equipment and could thus be entered multiple times

Then the employee ID is NOT the unique identifier. You can add these to the schema I gave earlier:

Table - tblEmployees
EmpID - Autonumber (PK)
EmpNumber - If this is a number you can substitute it for the autonumber key
EmpFName - Text
EmpLName - Text
more emp info too

Table - tblCertifications
CertificationID - Autonumber (PK)
EquipmentID - Long Integer (FK)
CertTypeID - Long Integer (FK)
EmpID - Long Integer (FK)
CertDate - Short Date

 

[kirkm1976]

Then the employee ID is NOT the unique identifier. You can add these to the schema I gave earlier:

Table - tblEmployees
EmpID - Autonumber (PK)
EmpNumber - If this is a number you can substitute it for the autonumber key
EmpFName - Text
EmpLName - Text
more emp info too

Table - tblCertifications
CertificationID - Autonumber (PK)
EquipmentID - Long Integer (FK)
CertTypeID - Long Integer (FK)
EmpID - Long Integer (FK)
CertDate - Short Date

OK, this is pretty outside the box from what I was thinking, but it looks like I was going "tall and fat" as you said. I am going to redo some of this from the ground up as mine isn't going to be terribly practical it would appear.

So back to the original issue, if my data is normalised as you describe above, I should be able to get an employees certifications by a simple select query using their employee id

 

[boblarson]

I should be able to get an employees certifications by a simple select query using their employee id

Yep, that you would.

 

[kirkm1976]

One last thing...I am importing the certification info from a spreadsheet (excel). Should I import it to one table and then split it out or have several imports run grabbing only the info I need for each table

 

[rsmonkey]

Personally i would import the excel spreadsheet into a Temp table in your db and then slice'n'dice it how you want rather than trying to import specific information every time. your queries/vba modules etc.. will be alot quicker if you important the whole thing aswell!