Can I even create this form???

[skgolden]

My husband and I own a small temporary labor company and deal with 4
major clients (A,B,C & D), each of which has about 2 dozen units in
our tristate area that we deal with (ie, Company A, units a,b,c,d,e
etc). We send temps possessing various skills (cashier, cook,
hostess, etc) to the individual units as needed. What I'm trying to
do is create a schedule we can use each day, for example:

Monday, June 1, 2004
Company A, unit e
Alice Jones cashier start time 7:00 am
Bob Smith cook start time 6:00 am
Fred Howard cook start time 8:30 am

I've created 3 separate databases

One with all the client information for each individual unit
(including their address, phone number and the contact person)
One with a list of employees and their status (they're temps, so the
status changes from active to inactive and back again often)
One with a list of job skills (cook, cashier, etc)

How do I relate the three and create a form so we can fill out the
schedule each day for the next day, and then produce a cohesive report
with all that information? Even though the form only needs the unit
name, employee name(s)/skills and start times, the finished report
should also print out the associated unit address, phone number and
contact person as well.

As far as I can tell, databases are somehow supposed to link to each
other in some unique way, but that's not the case for our need. Can
this even be done???? I've tried reading the book, looking for online
tutorials etc but I can't find an answer yet that I understand.
Please help!!! Thank you so much!

[Bernard Peek]

In message <1c**************************@posting.google.com >, skgolden
<sk*******@yahoo.com> writes

I've created 3 separate databases
I assume that you have actually created three different tables in the
same database, that's what you need.

One with all the client information for each individual unit
(including their address, phone number and the contact person)
One with a list of employees and their status (they're temps, so the
status changes from active to inactive and back again often)
One with a list of job skills (cook, cashier, etc)

How do I relate the three and create a form so we can fill out the
schedule each day for the next day, and then produce a cohesive report
with all that information? Even though the form only needs the unit
name, employee name(s)/skills and start times, the finished report
should also print out the associated unit address, phone number and
contact person as well.

As far as I can tell, databases are somehow supposed to link to each
other in some unique way, but that's not the case for our need. Can
this even be done???? I've tried reading the book, looking for online
tutorials etc but I can't find an answer yet that I understand.
Please help!!! Thank you so much!

You have three main tables but you probably need a fourth, call it
Booking or somesuch. It will be needed to link all of the other three
together. It will have a few fields.

Unit
Employee
Role
Start-Time
Stop-Time

The Unit will link to the Unit table you already have and show where the
booking is.

The Employee will link to the employee table and show who is being
booked.

The Role will link to the skills table and show what they are intended
to do.

In each of the above three fields you will create a "foreign key" in the
Booking table that has the same data type as a "Primary Key" in the
table it is linked to. The data type of the foreign key in your Bookings
table must be the same as the primary key in the linked table. You must
have a primary key for each of the tables you already have.

You could, if you wanted to, break down the Unit table into two separate
tables. One would identify the company and the second would identify the
actual unit. This would make it easier to track which company to bill
for each booking. Right now if you search for all bookings for company
'A' and you have one of the units listed as belonging to company 'a' you
might find yourself not billing for assignments to that unit. The extra
table will make the table structure a bit more complicated but that's
something I'll show you how to simplify later.

Let me know how much of what I've posted is stuff you already
understand, and then I'll talk you through the next part.


--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

[sk golden]

we actually have a completely separate software that does all our
billing, payroll, etc, so billing here isn't an issue at all. our issue
is creating a cohesive schedule that isn't just a bunch of scraps of
paper with illegible handwriting, which our software isn't capable of
doing (nor is the company interested in creating a module for us so we
can do it). what you've given me (although I haven't had much time to
digest it yet) make a lot of sense and I'm anxious to try it! I'll let
you know -- thank you!!!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[skgolden]

I did as you instructed in your earlier reply and created the fourth
table and linked the pertinent information (see earlier postings
below). Being very new at this, I know in my mind what I want to do
but I'm not sure if I'm doing it the way Access 97 wants it done...

Now that I've created the fourth table with the most pertinent info,
I'm assuming the next step is to create a scheduling form for my
husband to be able to fill in each day (he's COMPLETELY computer
illiterate so it MUST be as simple as possible). Linking the data as
you suggested, the form wizard creates a simple form with the unit
name and boxes for employee name, job skill, start time and end time.
However...we may have 10 different employees, each with a different
job skill and start/end time going to one unit, so I copied and pasted
the employee related boxes to expand the amount of employee data I
could fill in for each unit. I then went into Open Form and began
entering the data for Unit X with the first employee name/job
skill/hours and ALL the employee related boxes filled in with that
info. Then when I went to enter the info for the second employee, all
the boxes changed to that info that I started entering. I'm assuming
because a single record can only have a single piece of info? If not,
how can I create a form that allows me to enter many employees going
to a single unit?

Just one other thing (I'm sorry, I know this is a lot!) Does Access
have the same text fill in ability like, say, Excel -- where you just
have to type in a few letters for it to complete the rest of the word?
The only way I've been able to accomplish that is by making certain
fields Lookup fields, but with about 100 individual units and over 200
employees, there must be something more efficient to do.

Thank you so much again for your time!

You have three main tables but you probably need a fourth, call it

Booking or somesuch. It will be needed to link all of the other three
together. It will have a few fields.

Unit
Employee
Role
Start-Time
Stop-Time

The Unit will link to the Unit table you already have and show where the
booking is.
The Employee will link to the employee table and show who is being
booked.
The Role will link to the skills table and show what they are intended
to do.

In each of the above three fields you will create a "foreign key" in the
Booking table that has the same data type as a "Primary Key" in the
table it is linked to. The data type of the foreign key in your Bookings
table must be the same as the primary key in the linked table. You must
have a primary key for each of the tables you already have.

[Paul]

If you are still have lots of problems, hire a programmer to quickly do this
for you and learn from what they did.
It would probably save you a lot of time and effort (and frustration too).
This sounds/looks like it can be done in a very short (1 hour or so) amount
of time.
"skgolden" <sk*******@yahoo.com> wrote in message
news:1c**************************@posting.google.c om...

I did as you instructed in your earlier reply and created the fourth
table and linked the pertinent information (see earlier postings
below). Being very new at this, I know in my mind what I want to do
but I'm not sure if I'm doing it the way Access 97 wants it done...

Now that I've created the fourth table with the most pertinent info,
I'm assuming the next step is to create a scheduling form for my
husband to be able to fill in each day (he's COMPLETELY computer
illiterate so it MUST be as simple as possible). Linking the data as
you suggested, the form wizard creates a simple form with the unit
name and boxes for employee name, job skill, start time and end time.
However...we may have 10 different employees, each with a different
job skill and start/end time going to one unit, so I copied and pasted
the employee related boxes to expand the amount of employee data I
could fill in for each unit. I then went into Open Form and began
entering the data for Unit X with the first employee name/job
skill/hours and ALL the employee related boxes filled in with that
info. Then when I went to enter the info for the second employee, all
the boxes changed to that info that I started entering. I'm assuming
because a single record can only have a single piece of info? If not,
how can I create a form that allows me to enter many employees going
to a single unit?

Just one other thing (I'm sorry, I know this is a lot!) Does Access
have the same text fill in ability like, say, Excel -- where you just
have to type in a few letters for it to complete the rest of the word?
The only way I've been able to accomplish that is by making certain
fields Lookup fields, but with about 100 individual units and over 200
employees, there must be something more efficient to do.

Thank you so much again for your time!

You have three main tables but you probably need a fourth, call it

Booking or somesuch. It will be needed to link all of the other three
together. It will have a few fields.

Unit
Employee
Role
Start-Time
Stop-Time

The Unit will link to the Unit table you already have and show where the
booking is.
The Employee will link to the employee table and show who is being
booked.
The Role will link to the skills table and show what they are intended
to do.

In each of the above three fields you will create a "foreign key" in the
Booking table that has the same data type as a "Primary Key" in the
table it is linked to. The data type of the foreign key in your Bookings
table must be the same as the primary key in the linked table. You must
have a primary key for each of the tables you already have.

[Bernard Peek]

In message <1c**************************@posting.google.com >, skgolden
<sk*******@yahoo.com> writes

I did as you instructed in your earlier reply and created the fourth
table and linked the pertinent information (see earlier postings
below). Being very new at this, I know in my mind what I want to do
but I'm not sure if I'm doing it the way Access 97 wants it done...

Now that I've created the fourth table with the most pertinent info,
I'm assuming the next step is to create a scheduling form for my
husband to be able to fill in each day (he's COMPLETELY computer
illiterate so it MUST be as simple as possible). Linking the data as
you suggested, the form wizard creates a simple form with the unit
name and boxes for employee name, job skill, start time and end time.
However...we may have 10 different employees, each with a different
job skill and start/end time going to one unit, so I copied and pasted
the employee related boxes to expand the amount of employee data I
could fill in for each unit. I then went into Open Form and began
entering the data for Unit X with the first employee name/job
skill/hours and ALL the employee related boxes filled in with that
info. Then when I went to enter the info for the second employee, all
the boxes changed to that info that I started entering. I'm assuming
because a single record can only have a single piece of info? If not,
how can I create a form that allows me to enter many employees going
to a single unit?
It sounds as if you duplicated the fields on the form, that isn't the
way Access works. Each copy of the form holds the data for just one
record in the underlying table. If you have two identical controls on
the form then both of them point to the same field in the same record.
They must therefore have identical data.

Just one other thing (I'm sorry, I know this is a lot!) Does Access
have the same text fill in ability like, say, Excel -- where you just
have to type in a few letters for it to complete the rest of the word?
The only way I've been able to accomplish that is by making certain
fields Lookup fields, but with about 100 individual units and over 200
employees, there must be something more efficient to do.

Yes, Access does have that facility. What you need to do is to use Combo
Box controls on your forms. These combine a text-box and a drop-down
list. They allow you to look up information from your linked tables. If
you start typing into the box then the drop-down list will show matching
records.

The form will be based on the Bookings table. It should have one combo
box for the Unit, one for the Employee and one for the Role. There
should also be one text-box for the Start-Time and one for the
Stop-Time.

Paul has suggested that you hire a programmer to do the design for you.
If time is important then that's the best option. I'm assuming that
because you took the time to post to a newsgroup and wait for a response
that you don't need an instant answer. Hiring a programmer will get this
job done but if you expect to learn how to design systems in Access then
it's probably better to make your own mistakes, but this will take time.
--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

[Paul]

If you find a local programmer, you can watch them work while they create
your DB. This way you will have things explained to you and get the job
done at the same time (so you get a db and training together). I have done
this many times to help my clients understand the programming end. They
don't ever take on the jobs themselves, but the feel much better knowing
what is involved.

Paul

"Bernard Peek" <ba*@shrdlu.com> wrote in message
news:y0**************@shrdlu.com...

In message <1c**************************@posting.google.com >, skgolden
<sk*******@yahoo.com> writes

I did as you instructed in your earlier reply and created the fourth
table and linked the pertinent information (see earlier postings
below). Being very new at this, I know in my mind what I want to do
but I'm not sure if I'm doing it the way Access 97 wants it done...

Now that I've created the fourth table with the most pertinent info,
I'm assuming the next step is to create a scheduling form for my
husband to be able to fill in each day (he's COMPLETELY computer
illiterate so it MUST be as simple as possible). Linking the data as
you suggested, the form wizard creates a simple form with the unit
name and boxes for employee name, job skill, start time and end time.
However...we may have 10 different employees, each with a different
job skill and start/end time going to one unit, so I copied and pasted
the employee related boxes to expand the amount of employee data I
could fill in for each unit. I then went into Open Form and began
entering the data for Unit X with the first employee name/job
skill/hours and ALL the employee related boxes filled in with that
info. Then when I went to enter the info for the second employee, all
the boxes changed to that info that I started entering. I'm assuming
because a single record can only have a single piece of info? If not,
how can I create a form that allows me to enter many employees going
to a single unit?

It sounds as if you duplicated the fields on the form, that isn't the way
Access works. Each copy of the form holds the data for just one record in
the underlying table. If you have two identical controls on the form then
both of them point to the same field in the same record. They must
therefore have identical data.

Just one other thing (I'm sorry, I know this is a lot!) Does Access
have the same text fill in ability like, say, Excel -- where you just
have to type in a few letters for it to complete the rest of the word?
The only way I've been able to accomplish that is by making certain
fields Lookup fields, but with about 100 individual units and over 200
employees, there must be something more efficient to do.

Yes, Access does have that facility. What you need to do is to use Combo
Box controls on your forms. These combine a text-box and a drop-down list.
They allow you to look up information from your linked tables. If you
start typing into the box then the drop-down list will show matching
records.

The form will be based on the Bookings table. It should have one combo box
for the Unit, one for the Employee and one for the Role. There should also
be one text-box for the Start-Time and one for the Stop-Time.

Paul has suggested that you hire a programmer to do the design for you. If
time is important then that's the best option. I'm assuming that because
you took the time to post to a newsgroup and wait for a response that you
don't need an instant answer. Hiring a programmer will get this job done
but if you expect to learn how to design systems in Access then it's
probably better to make your own mistakes, but this will take time.
--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.