A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access ID



 
 
Thread Tools Display Modes
  #1  
Old April 30th, 2010, 10:25 PM
shirl shirl is offline
Experienced Member
 
First recorded activity by OfficeFrustration: Mar 2005
Posts: 39
Default Access ID

Hi

I have a database that was set up very quickly a few years ago, it is for recording the booking out of equipment to staff at a college.

The database now has over 1500 records and I would like to change how it works as there is lots of duplicate data and I want to make data entry easier for the person who has to enter the data.

It consist of one table [Equipment] which has a autonumber primary key ID containing a list of equipment. Another table [Dept] with an autonumber primary key containing departments. A third table [Bookings] with an autonumber primary key ID this contains the staff names, dates of equipment booked out a field called resource which looks at the equipment table, area which looks at the department table.

My problem is that as the staff and details of resources booked out are all in one table, this data is duplicated as each time something is booked out we have to enter the staff details again.

What I would like is a form which contained the staff details and a subform which showed the equipment being booked out. I have tried to make two new tables using a make table query from the [Bookings] table, but my problem is that when I do that both new tables for the staff and the bookings both have the same ID as they came from the same table.

How can I separate the staff and bookings yet still keep them linked so that I can have a form something like the Northwind database, where you see a record for a person and all of their bookings?

I hope this makes sense, as you can guess I am not an Access expert and only use it occasionally.

Thanks
Shirl
  #2  
Old May 1st, 2010, 02:35 AM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Access ID

Hi Shirl,

You need the followin tables:

TblDepartment
DepartmentID
Department

TblStaff
StaffID
FirstName
LastName
InActive (Yes/No)

TblDepartmentStaff
DepartmentStaffID
DepartmentID
StaffID

TblEquipment
EquipmentID
EquipmentName

TblEquipmentBooking
EquipmentBookingID
EquipmentID
StaffID
DateOut
DateReturned

You can use a form/subform to enter departments and staff in the department.
Base the main form on TblDepartment and base the subform on
TblDepartmentStaff. Set the Linkmaster and LinkChild properties to
DepartmentID. In the subform, use a combobox to enter StaffID. Set the row
source of the combobox to TblStaff.

You can use a form/subform to enter equipment and equipment bookings. Base
the main form on TblEquipment and base the subform on TblEquipmentBooking.
Set the Linkmaster and LinkChild properties to EquipmentID. In the subform,
use a combobox to enter StaffID. Set the row source of the combobox to
TblStaff.

Steve



"shirl" wrote in message
...

Hi

I have a database that was set up very quickly a few years ago, it is
for recording the booking out of equipment to staff at a college.

The database now has over 1500 records and I would like to change how it
works as there is lots of duplicate data and I want to make data entry
easier for the person who has to enter the data.

It consist of one table [Equipment] which has a autonumber primary key
ID containing a list of equipment. Another table [Dept] with an
autonumber primary key containing departments. A third table [Bookings]
with an autonumber primary key ID this contains the staff names, dates
of equipment booked out a field called resource which looks at the
equipment table, area which looks at the department table.

My problem is that as the staff and details of resources booked out are
all in one table, this data is duplicated as each time something is
booked out we have to enter the staff details again.

What I would like is a form which contained the staff details and a
subform which showed the equipment being booked out. I have tried to
make two new tables using a make table query from the [Bookings] table,
but my problem is that when I do that both new tables for the staff and
the bookings both have the same ID as they came from the same table.

How can I separate the staff and bookings yet still keep them linked so
that I can have a form something like the Northwind database, where you
see a record for a person and all of their bookings?

I hope this makes sense, as you can guess I am not an Access expert and
only use it occasionally.

Thanks
Shirl




--
shirl



  #3  
Old May 1st, 2010, 02:30 PM posted to microsoft.public.access.tablesdbdesign
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default Access ID

On Fri, 30 Apr 2010 17:25:17 -0400, shirl
wrote:

You have the right instincts. Indeed the previous developer did nobody
any favors with this very bad design.
MakeTable queries may work, but I would rather first create the new
tables manually, with their correct fields, primary keys, and
relationships. This is the part that is most difficult for beginners,
but there is a lot of help out there if you want to learn. For example
he
http://www.youtube.com/results?searc...y+crystal&aq=0
is a series of videos by a fellow MVP.

Once the database design is in place, create some Append and perhaps
Update queries to copy the data from the old design to the new.

-Tom.
Microsoft Access MVP



Hi

I have a database that was set up very quickly a few years ago, it is
for recording the booking out of equipment to staff at a college.

The database now has over 1500 records and I would like to change how it
works as there is lots of duplicate data and I want to make data entry
easier for the person who has to enter the data.

It consist of one table [Equipment] which has a autonumber primary key
ID containing a list of equipment. Another table [Dept] with an
autonumber primary key containing departments. A third table [Bookings]
with an autonumber primary key ID this contains the staff names, dates
of equipment booked out a field called resource which looks at the
equipment table, area which looks at the department table.

My problem is that as the staff and details of resources booked out are
all in one table, this data is duplicated as each time something is
booked out we have to enter the staff details again.

What I would like is a form which contained the staff details and a
subform which showed the equipment being booked out. I have tried to
make two new tables using a make table query from the [Bookings] table,
but my problem is that when I do that both new tables for the staff and
the bookings both have the same ID as they came from the same table.

How can I separate the staff and bookings yet still keep them linked so
that I can have a form something like the Northwind database, where you
see a record for a person and all of their bookings?

I hope this makes sense, as you can guess I am not an Access expert and
only use it occasionally.

Thanks
Shirl

  #4  
Old May 16th, 2010, 01:12 PM
shirl shirl is offline
Experienced Member
 
First recorded activity by OfficeFrustration: Mar 2005
Posts: 39
Default

Thanks for you help, have finally got it sorted.
Shirl

Quote:
Originally Posted by Steve[_77_] View Post
Hi Shirl,

You need the followin tables:

TblDepartment
DepartmentID
Department

TblStaff
StaffID
FirstName
LastName
InActive (Yes/No)

TblDepartmentStaff
DepartmentStaffID
DepartmentID
StaffID

TblEquipment
EquipmentID
EquipmentName

TblEquipmentBooking
EquipmentBookingID
EquipmentID
StaffID
DateOut
DateReturned

You can use a form/subform to enter departments and staff in the department.
Base the main form on TblDepartment and base the subform on
TblDepartmentStaff. Set the Linkmaster and LinkChild properties to
DepartmentID. In the subform, use a combobox to enter StaffID. Set the row
source of the combobox to TblStaff.

You can use a form/subform to enter equipment and equipment bookings. Base
the main form on TblEquipment and base the subform on TblEquipmentBooking.
Set the Linkmaster and LinkChild properties to EquipmentID. In the subform,
use a combobox to enter StaffID. Set the row source of the combobox to
TblStaff.

Steve



"shirl"
wrote in message
...

Hi

I have a database that was set up very quickly a few years ago, it is
for recording the booking out of equipment to staff at a college.

The database now has over 1500 records and I would like to change how it
works as there is lots of duplicate data and I want to make data entry
easier for the person who has to enter the data.

It consist of one table [Equipment] which has a autonumber primary key
ID containing a list of equipment. Another table [Dept] with an
autonumber primary key containing departments. A third table [Bookings]
with an autonumber primary key ID this contains the staff names, dates
of equipment booked out a field called resource which looks at the
equipment table, area which looks at the department table.

My problem is that as the staff and details of resources booked out are
all in one table, this data is duplicated as each time something is
booked out we have to enter the staff details again.

What I would like is a form which contained the staff details and a
subform which showed the equipment being booked out. I have tried to
make two new tables using a make table query from the [Bookings] table,
but my problem is that when I do that both new tables for the staff and
the bookings both have the same ID as they came from the same table.

How can I separate the staff and bookings yet still keep them linked so
that I can have a form something like the Northwind database, where you
see a record for a person and all of their bookings?

I hope this makes sense, as you can guess I am not an Access expert and
only use it occasionally.

Thanks
Shirl




--
shirl
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:31 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.