Overflowing Fields

  • Thread starter Thread starter John Squirrell
  • Start date Start date
J

John Squirrell

Hi All,

I have a report showing the following employees

Bill
John
Paul
Richard

They are trained to drive Vans, Trucks and Cars, but I only have 2 Vans and
1 Truck and unlimited Cars.
When I generate a report it lists that I have 4 Van Drivers, 4 Truck Drivers
and 4 Car Drivers.
What I want is when the Truck Drivers are more than 1 to add them to the Van
Drivers and when they are more than 2, add them to the Car Drivers so that I
end up with

Truck Driver: Bill
Van Driver: John & Paul
Car Drive: Richard

If anyone can help me, I would be very grateful


Regards

John Squirrell


John Squirrell
 
Do you have a table that describes which employees are qualified to drive
which vehicles?
Is there a priority regarding assignments ie: who gets the Vans?
Do you have a table of vehicles?
Do you understand how to write and maintain code?
 
Many thanks for your reply.

I have An Employee Table and an Employees Skills Table. I also have a Skills
table with the following:
Skill No (The number of the skill)
Skill Name (The name of the skill, ie Van Driver)
Min_Number (The minimum number of Vans ie 2 in this example)
Rank (The priority of the skill. In this example Van would be 1, Cars 3)

My knowledge of writing and maintaining code is limited.

Regards


John
 
I would start by creating a table of all possible numbers from 1 to
whatever. Then you can create a query like:
SELECT tblSkills.Rank, tblNums.Num, tblSkills.SkillNo,
tblSkills.SkillName, tblSkills.Min_Number
FROM tblNums, tblSkills
WHERE (((tblSkills.Min_Number)>=[Num]))
ORDER BY tblSkills.Rank, tblNums.Num;

This recordset can then be stepped through and an employee assigned.
 
Many thanks but I may be a little out of my depth!

How do I step through the recordset and assign an employee?

Regards

John


Duane Hookom said:
I would start by creating a table of all possible numbers from 1 to
whatever. Then you can create a query like:
SELECT tblSkills.Rank, tblNums.Num, tblSkills.SkillNo,
tblSkills.SkillName, tblSkills.Min_Number
FROM tblNums, tblSkills
WHERE (((tblSkills.Min_Number)>=[Num]))
ORDER BY tblSkills.Rank, tblNums.Num;

This recordset can then be stepped through and an employee assigned.

--
Duane Hookom
MS Access MVP


John Squirrell said:
Many thanks for your reply.

I have An Employee Table and an Employees Skills Table. I also have a Skills
table with the following:
Skill No (The number of the skill)
Skill Name (The name of the skill, ie Van Driver)
Min_Number (The minimum number of Vans ie 2 in this example)
Rank (The priority of the skill. In this example Van would be 1, Cars 3)

My knowledge of writing and maintaining code is limited.

Regards


John
so
that
Click to expand...
Click to expand...
 
Please do a little searching through help on how to create recordsets and
then come back with specific questions if you have any.

--
Duane Hookom
MS Access MVP


John Squirrell said:
Many thanks but I may be a little out of my depth!

How do I step through the recordset and assign an employee?

Regards

John


Duane Hookom said:
I would start by creating a table of all possible numbers from 1 to
whatever. Then you can create a query like:
SELECT tblSkills.Rank, tblNums.Num, tblSkills.SkillNo,
tblSkills.SkillName, tblSkills.Min_Number
FROM tblNums, tblSkills
WHERE (((tblSkills.Min_Number)>=[Num]))
ORDER BY tblSkills.Rank, tblNums.Num;

This recordset can then be stepped through and an employee assigned.

--
Duane Hookom
MS Access MVP


John Squirrell said:
Many thanks for your reply.

I have An Employee Table and an Employees Skills Table. I also have a Skills
table with the following:
Skill No (The number of the skill)
Skill Name (The name of the skill, ie Van Driver)
Min_Number (The minimum number of Vans ie 2 in this example)
Rank (The priority of the skill. In this example Van would be 1, Cars 3)

My knowledge of writing and maintaining code is limited.

Regards


John
Do you have a table that describes which employees are qualified to drive
which vehicles?
Is there a priority regarding assignments ie: who gets the Vans?
Do you have a table of vehicles?
Do you understand how to write and maintain code?

--
Duane Hookom
MS Access MVP
--

Hi All,

I have a report showing the following employees

Bill
John
Paul
Richard

They are trained to drive Vans, Trucks and Cars, but I only have 2 Vans
and
1 Truck and unlimited Cars.
When I generate a report it lists that I have 4 Van Drivers, 4 Truck
Drivers
and 4 Car Drivers.
What I want is when the Truck Drivers are more than 1 to add them
Click to expand...
to
the
Van
Drivers and when they are more than 2, add them to the Car Drivers so
that
I
end up with

Truck Driver: Bill
Van Driver: John & Paul
Car Drive: Richard

If anyone can help me, I would be very grateful


Regards

John Squirrell


John Squirrell
Click to expand...
Click to expand...
Click to expand...
 
Back
Top