equipment allocation list design problem please help

[Darryn]

I have been trying to develop an equipment inventory system for the
outdoor ed deparment of a school for the last few weeks. I have the
basic equipment purchase/maintenance record stuff going pretty well
but am a bit stuck on how to implement a an equipment packing list
setup and stocktaking. I'm open to any suggestions

My inventory database has the following main fields and each item has
a separate record.
EquipmentRecordNumber (PK)
EquipmentCategory
EquipmentItemType
Equipdescription
etc
etc

I had set up a Program packing sheet table with a Program equipment
link table in a similar form to a product invoice/order setup
Program ID Number (PK)
Program Description
Location

Program ID Number (PK)
EquipmentRecordNumber (PK)

This would allow the instructors to select equipment for a program.
They would like to be able to select an item type (I will filter them
by category first) and then enter how many of each item are required
for the program.

I had started to make my forms up and have come up with the following
problem. I have referenced the equipment record number which is a
unique item but in reality most of the time it will be a case of so
many units of "item type A" and so many units of "item type B". I
then thought of creating link between 'EquipmentItemType' for the
equipment table and the program ID number but 'EquipmentItemType' is
not unique in the equipment table as there are multiple instances of
the majority of the item types.

Can anyone suggest a way around this at all please??

Thanks

Darryn

 

[Graham Mandeno]

Hi Darryn

When the user selects "three one-man kayaks", does it matter which three get
chosen?
Does your database have any way of recording the unavailability of
equipment? (Presumably the same kayak can't be used in two programs at
once)

If the answer to both these is "no" then you could go with the idea of
linking to EquipmentType (I assume you have an EquipmentType table with an
EquipmentTypeID PK.

Your ProgramEquipment table then needs the following fields:
ProgramID (PK)
EquipmentTypeID (PK)
NumberRequired

If you want the user to be able to select the blue kayak and the yellow
kayak, then you need to stick with the structure you have. However, to make
the selection process easier, you could have a popup dialog form to add
items, where the user selects an EquipmentType from an unbound combo and
this populates a multi-select listbox with all the available items of that
type. The user can then select the requires number of items (a textbox
displaying [lstSelectItems].ItemsSelected.Count would be a good idea) and
then click a button to add these items to the program inventory.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[Darryn]

Hi Darryn

When the user selects "three one-man kayaks", does it matter which three get
chosen?

From looking at some of the packing lists which are just excel
spreadsheets, there is a bit of both of both but I would say the
majority is just an item type with a quantity next to it.

Does your database have any way of recording the unavailability of
equipment? (Presumably the same kayak can't be used in two programs at
once)

I don't have that functionality as the users had not requested it, I
have a status flag on each item to tell if it is active or inactive
thats all. The users have said they don't want to actually sit at the
PC and move items in and out of the inventory so this is really just a
selection sheet that they will check off the items taken out and then
when they finish the program make sure they return the same amount of
gear to the shed

If there is a discrepancy between the number which was taken and the
number returned on the sheet, then they have to go into the main
equipment list and edit the status of the records.

If the answer to both these is "no" then you could go with the idea of
linking to EquipmentType

Ok I was trying to link to the Equipment Type in the Main equipment
table, I should be linking there

(I assume you have an EquipmentType table with an
EquipmentTypeID PK.

I did have an autonumber ID and then a description in an earlier
incantation and then got hung up with the bound/unbound column types
and ended up just making the actual values of the Equipment type the
PK. I guess I can still go down that route.

Your ProgramEquipment table then needs the following fields:
ProgramID (PK)
EquipmentTypeID (PK)
NumberRequired

OK I might try that again

Just as a side note I have an Equipment Area table and an Equipment
item type table, Correct me if I am wrong please but I should have a
PF link from the Area table to the Item table so each Item type is
related to an Area (I want to do this for data entry integrity so
certain equipment types will only be assigned to certain areas)

If you want the user to be able to select the blue kayak and the yellow
kayak, then you need to stick with the structure you have. However, to make
the selection process easier, you could have a popup dialog form to add
items, where the user selects an EquipmentType from an unbound combo and
this populates a multi-select listbox with all the available items of that
type. The user can then select the requires number of items (a textbox
displaying [lstSelectItems].ItemsSelected.Count would be a good idea) and
then click a button to add these items to the program inventory.

I like that idea as it would be better for knowing exactly which item
has gone missing, but at the moment I am in way over my head and just
need to produce a finished product. It is something I would like to
work on a bit more when time is not so much of a constraint tho

As another side note what sort of code would I need to transfer these
items into the program inventory table?

I undertook to make an inventory database for a friend to complete a
professional practice unit of a Leisure science degree so I could
learn more about access and databases. I think my friend and my
lecturer both thought it woud be a simple case of importing the
spreadsheet into access, using a few wizards and then voila "a
database"

Man I have learnt so much especially about data normalisiation and are
still only scratching the surface
And learning about Access is just another layer on top!

Thanks for clearing things up

Darryn

 

[Graham Mandeno]

Hi Darryn

Answers inline - heavily snipped...

Just as a side note I have an Equipment Area table and an Equipment
item type table, Correct me if I am wrong please but I should have a
PF link from the Area table to the Item table so each Item type is
related to an Area (I want to do this for data entry integrity so
certain equipment types will only be assigned to certain areas)

Yes, you should relate the PK of your Area table (use an autonumber unless
there is a suitable natural key) to a foreign key (FK) field of the same
type in your Equipment table (if the PK is an autonumber then for "same
type" read "long integer").

Do the same with your EquipmentType table.

In the "Equipment Items" form, you can use combo boxes to select and display
the Area/EquipmentType by name, rather than ID number, while the ID number
is what is actually stored in your table.

If you want the user to be able to select the blue kayak and the yellow
kayak, then you need to stick with the structure you have. However, to make
the selection process easier, you could have a popup dialog form to add
items, where the user selects an EquipmentType from an unbound combo and
this populates a multi-select listbox with all the available items of that
type. The user can then select the requires number of items (a textbox
displaying [lstSelectItems].ItemsSelected.Count would be a good idea) and
then click a button to add these items to the program inventory.

I like that idea as it would be better for knowing exactly which item
has gone missing, but at the moment I am in way over my head and just
need to produce a finished product. It is something I would like to
work on a bit more when time is not so much of a constraint tho

As another side note what sort of code would I need to transfer these
items into the program inventory table?

The code is fairly simple. Something like this:

Dim vItem as Variant, db as DAO.Database
Set db = CurrentDb
For Each vItem in lstSelectItems.ItemsSelected
db.Execute "Insert into ProgramEquipment (ProgramID, ItemID) " _
& "values (" & lProgramID & ", " & lstSelectItems.Itemdata(vItem) _
& ")", dbFailOnError
Next vItem

Here, lProgramID is a long variable containing the ID of the program for
which you are adding equipment.

I undertook to make an inventory database for a friend to complete a
professional practice unit of a Leisure science degree so I could
learn more about access and databases. I think my friend and my
lecturer both thought it woud be a simple case of importing the
spreadsheet into access, using a few wizards and then voila "a
database"

Don't worry - this is an almost universal misconception among Access
newbies!

Man I have learnt so much especially about data normalisiation and are
still only scratching the surface
And learning about Access is just another layer on top!

Well, every cloud has a silver lining as they say

--
Cheers!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[Darryn]

On Fri, 24 Oct 2003 13:24:11 +1300, "Graham Mandeno"

Hi Graham

Yes, you should relate the PK of your Area table (use an autonumber unless
there is a suitable natural key) to a foreign key (FK) field of the same
type in your Equipment table (if the PK is an autonumber then for "same
type" read "long integer").

Do the same with your EquipmentType table.

Ok so far so good

type. The user can then select the requires number of items (a textbox
displaying [lstSelectItems].ItemsSelected.Count would be a good idea) and
then click a button to add these items to the program inventory.

I like that idea as it would be better for knowing exactly which item
has gone missing, but at the moment I am in way over my head and just
need to produce a finished product. It is something I would like to
work on a bit more when time is not so much of a constraint tho

As another side note what sort of code would I need to transfer these
items into the program inventory table?

The code is fairly simple. Something like this:

Dim vItem as Variant, db as DAO.Database
Set db = CurrentDb
For Each vItem in lstSelectItems.ItemsSelected
db.Execute "Insert into ProgramEquipment (ProgramID, ItemID) " _
& "values (" & lProgramID & ", " & lstSelectItems.Itemdata(vItem) _
& ")", dbFailOnError
Next vItem

I think I will give this a go as I setup the other system and was not
very happy with it

Here, lProgramID is a long variable containing the ID of the program for
which you are adding equipment.

Don't worry - this is an almost universal misconception among Access
newbies!

I didn't have that misconception and had a hard time convincing my
lecturer that I could spend 160 hours on the project, half of that has
probably just been in reading about design, normalisation and trying
to get my head around access!!

Well, every cloud has a silver lining as they say

It has definately been a learning experience for me

Thanks again

Darryn
-- remove two eyes to reply!

 

[Darryn]

Hi Darryn

Answers inline - heavily snipped...

Ok so far so good I have got that

The user can then select the requires number of items (a textbox
displaying [lstSelectItems].ItemsSelected.Count would be a good idea)
and
then click a button to add these items to the program inventory.

Sorry I am completely thick, where do i put that to get the count to
show up in the text box

The code is fairly simple. Something like this:

Dim vItem as Variant, db as DAO.Database
Set db = CurrentDb
For Each vItem in lstSelectItems.ItemsSelected
db.Execute "Insert into ProgramEquipment (ProgramID, ItemID) " _
& "values (" & lProgramID & ", " & lstSelectItems.Itemdata(vItem) _
& ")", dbFailOnError
Next vItem

Here, lProgramID is a long variable containing the ID of the program for
which you are adding equipment.

I have a couple more questions, I created an unbound form with the
multi select list box on it.

Does this need to be based on the program equipment table or are we
just interested in getting the ItemID numbers from it ?

I have based it on my main equipment table as I would like the list
box to have a short line description in it as well as size and colour
which are only in the main equipment table

Darryn

 

[Graham Mandeno]

Hi Darryn

Sorry - I've been away for a few days.

where do i put that to get the count to
show up in the text box

Add a textbox to your form, named txtSelectionCount, and an attached label
"Number of items selected:". Set its Enabled property to No and Locked to
Yes. Set its ControlSource to:
=[lstSelectItems].ItemsSelected.Count
(where "lstSelectItems" is the name of your listbox)

I have a couple more questions, I created an unbound form with the
multi select list box on it.

Does this need to be based on the program equipment table or are we
just interested in getting the ItemID numbers from it ?

I have based it on my main equipment table as I would like the list
box to have a short line description in it as well as size and colour
which are only in the main equipment table

I guess you already have a bound form, based on your Programs table, showing
program information and including either a subform or listbox displaying the
equipment inventory for that program. Your form should also have a "Select
Equipment" button which opens your unbound form.

On the unbound form, add a combo box cboEquipmentType listing the equipment
types, and your multi-select listbox. You might also want a combo listing
locations. Leave the RowSource of the listbox blank. Make a query
including the ItemID and ItemDescription, ItemSize, ItemColor (and maybe
LocationName), and also add columns for EquipTypeID (and maybe
EquipLocationID). Save the query as qrylstSelectItems.

Now, in your form's code module, add the following function:

Private Function FillListBox()
Dim sSql as string, vItem as variant
with lstSelectItems
For each vItem in .ItemsSelected
.Selected(vItem) = False
next vItem
sSql = "Select * from qrylstSelectItems where EquipTypeID=" _
& Nz(cboEquipmentType, 0)
If not IsNull(cboLocation) then
sSql = sSql & " and EquipLocationID=" & cboSelectLocationID
End If
.RowSource = sSql
End With
End Function

Set the AfterUpdate properties for both combo boxes to:
=FillListBox()

Now, when you select an Equipment Type or Location, your list will be
filtered according to that selection.

In the Click event code for your "Add Items" button, you can get the
ProgramID value from the calling form (Forms!frmPrograms!ProgramID), or you
can pass it to your unbound form via the OpenArgs mechanism when you open
the unbound form.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.