HELP!!! MY HEAD HURTS!

[Randy]

Ok so here is the problem....I am using Access to track the keys we issue to
employees from request to issuance to return. I have the employee fill out an
adobe form which then gets imported into access and populated into an access
form as well as my core access table. What I need is to create a report or a
query or something that would find every instance of a key being issued and
spit out a report of it. Here is the problem every record may contain up to 6
different keys being issued so there are potentially 6 fields that it would
have to search through for each record. Any ideas? Need clarification? Just
let me know.

Thanks

 

[Duane Hookom]

HEAD HURTING is the result of committing spreadsheet. You can start by
normalizing your table with a union query like:

SELECT EmployeeID, [Key1] As Key
FROM tblKeySpreadsheet
WHERE [Key1] Is Not Null
UNION ALL
SELECT EmployeeID, [Key2]
FROM tblKeySpreadsheet
WHERE [Key2] Is Not Null
UNION ALL
SELECT EmployeeID, [Key3]
FROM tblKeySpreadsheet
WHERE [Key3] Is Not Null
UNION ALL
SELECT EmployeeID, [Key4]
FROM tblKeySpreadsheet
WHERE [Key4] Is Not Null
UNION ALL
SELECT EmployeeID, [Key5]
FROM tblKeySpreadsheet
WHERE [Key5] Is Not Null
UNION ALL
SELECT EmployeeID, [Key6]
FROM tblKeySpreadsheet
WHERE [Key6] Is Not Null;

You can now use this union query in other queries. You might be best off in
the long run normalizing your table structure so you can issue any number of
keys at a time.

 

[Randy]

Is there a way to write into my form to create a new record for each
individual key? I don't want to have to force the employees to fill out a
new form for each key...

HEAD HURTING is the result of committing spreadsheet. You can start by
normalizing your table with a union query like:

SELECT EmployeeID, [Key1] As Key
FROM tblKeySpreadsheet
WHERE [Key1] Is Not Null
UNION ALL
SELECT EmployeeID, [Key2]
FROM tblKeySpreadsheet
WHERE [Key2] Is Not Null
UNION ALL
SELECT EmployeeID, [Key3]
FROM tblKeySpreadsheet
WHERE [Key3] Is Not Null
UNION ALL
SELECT EmployeeID, [Key4]
FROM tblKeySpreadsheet
WHERE [Key4] Is Not Null
UNION ALL
SELECT EmployeeID, [Key5]
FROM tblKeySpreadsheet
WHERE [Key5] Is Not Null
UNION ALL
SELECT EmployeeID, [Key6]
FROM tblKeySpreadsheet
WHERE [Key6] Is Not Null;

You can now use this union query in other queries. You might be best off in
the long run normalizing your table structure so you can issue any number of
keys at a time.
--
Duane Hookom
Microsoft Access MVP

Ok so here is the problem....I am using Access to track the keys we issue to
employees from request to issuance to return. I have the employee fill out an
adobe form which then gets imported into access and populated into an access
form as well as my core access table. What I need is to create a report or a
query or something that would find every instance of a key being issued and
spit out a report of it. Here is the problem every record may contain up to 6
different keys being issued so there are potentially 6 fields that it would
have to search through for each record. Any ideas? Need clarification? Just
let me know.

Thanks

 

[Duane Hookom]

You would generally use a continuous subform for the individual keys.
--
Duane Hookom
Microsoft Access MVP

Is there a way to write into my form to create a new record for each
individual key? I don't want to have to force the employees to fill out a
new form for each key...

HEAD HURTING is the result of committing spreadsheet. You can start by
normalizing your table with a union query like:

SELECT EmployeeID, [Key1] As Key
FROM tblKeySpreadsheet
WHERE [Key1] Is Not Null
UNION ALL
SELECT EmployeeID, [Key2]
FROM tblKeySpreadsheet
WHERE [Key2] Is Not Null
UNION ALL
SELECT EmployeeID, [Key3]
FROM tblKeySpreadsheet
WHERE [Key3] Is Not Null
UNION ALL
SELECT EmployeeID, [Key4]
FROM tblKeySpreadsheet
WHERE [Key4] Is Not Null
UNION ALL
SELECT EmployeeID, [Key5]
FROM tblKeySpreadsheet
WHERE [Key5] Is Not Null
UNION ALL
SELECT EmployeeID, [Key6]
FROM tblKeySpreadsheet
WHERE [Key6] Is Not Null;

You can now use this union query in other queries. You might be best off in
the long run normalizing your table structure so you can issue any number of
keys at a time.
--
Duane Hookom
Microsoft Access MVP

Ok so here is the problem....I am using Access to track the keys we issue to
employees from request to issuance to return. I have the employee fill out an
adobe form which then gets imported into access and populated into an access
form as well as my core access table. What I need is to create a report or a
query or something that would find every instance of a key being issued and
spit out a report of it. Here is the problem every record may contain up to 6
different keys being issued so there are potentially 6 fields that it would
have to search through for each record. Any ideas? Need clarification? Just
let me know.

Thanks