Access ID
in [Tables]
|
From: shirl on 30 Apr 2010 17:25 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
From: Steve on 30 Apr 2010 21:35 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 santus(a)penn.com "shirl" <shirl.621336a(a)officefrustration.com> wrote in message news:shirl.621336a(a)officefrustration.com... > > 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
From: Tom van Stiphout on 1 May 2010 09:30 On Fri, 30 Apr 2010 17:25:17 -0400, shirl <shirl.621336a(a)officefrustration.com> 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 here: http://www.youtube.com/results?search_query=learn+access+by+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
From: shirl on 16 May 2010 08:12 Thanks for you help, have finally got it sorted. Shirl 'Steve[_77_ Wrote: > ;3718254']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 > santus(a)penn.com > > > "shirl" shirl.621336a(a)officefrustration.com wrote in message > news:shirl.621336a(a)officefrustration.com...- > > 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 - -- shirl
|
Pages: 1 Prev: No Duplicates for Full Name Next: Hyperlink field / Allowing Full Menu's |