Form to input to numerous tables

[Darryn]

Hi all

I have just finished my databse design after reading 'database design
for mere mortals' but it doesn't go into actual form design at all.

My database is an asset tracking/inventory system where I have a main
equipment category with common information stored and then subset
tables for various pieces of equipment which need to have differing
fields of information stored.

In the interests of making the user input form as simple as possible
is there any way of doing this where I can have a main form and then
open a sub form based on the type of equipment being entered into the
database

I have a main equipment classification and a sub classification table
which I was hoping to use with two combo boxes, on selecting a broad
classification in one box it will filter the subclassifications in the
second box. This would then decide which subform to show. I also need
to save the two classifications for each record

Any suggestions on how to achieve this??

Darryn
-- remove two eyes to reply!

 

[John Viescas]

Darryn-

What is so different about each equipment type that you feel you need
separate tables? You might have "over-normalized."

If you really feel that you need to do it this way, you can create an
unbound form with two combo boxes that list the main and sub
classification - and use a parameter query filtered on the first combo box
to create the sub list. Do a Requery in AfterUpdate of the first combo, and
use the AfterUpdate event of the second combo to run code to decide which
editing form to open. Code in the editing forms can pick up the
classifications to use as default values for any new rows.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

 

[Darryn]

Darryn-

What is so different about each equipment type that you feel you need
separate tables? You might have "over-normalized."

I think I have gone a bit too far in hindsight, I was trying to get a
bit too detailed in my inventory items. I had originally worked out
subset table for things like vehicles, climbing hardware, watercraft
with extra details for each but I think I am overcomplicating the
issue a bit much.

After rethinking it a bit I have got it down to three types
Items which are unique and have a record for each item
Items which are just bought in multiples and only need to be counted
Items which are used in a grouped kit of gear.

So I guess with the first two I just need a flag in my table if the
item is indivudal or bulk, if bulk is selected then the user need to
enter a quantity, but this makes it messier to do a stock take I am
thinking

I have also set up another table of kits which the user will assign
equipment to from the main equipment table. I am posting another
question on this below

If you really feel that you need to do it this way, you can create an
unbound form with two combo boxes that list the main and sub
classification - and use a parameter query filtered on the first combo box
to create the sub list. Do a Requery in AfterUpdate of the first combo, and
use the AfterUpdate event of the second combo to run code to decide which
editing form to open. Code in the editing forms can pick up the
classifications to use as default values for any new rows.

I will keep that in mind

Darryn
-- remove two eyes to reply!

 

[John Viescas]

Darryn-

What tables do you have now? Please post the general layout of each.

When I'm doing an inventory application, I create a tblProducts table that
has the following fields:

ProductID, ProductDescription, OrderBy ("Each", "Case", etc.), OrderMultiple
(if "Each", then 1, else the size of the "Case", etc.), SellBy (usually
"Each"), MinInventory, MaxInventory, EOQ (Economic Order Quantity)

Code that handles Purchase Order Details makes sure that items are ordered
by the "case", "lot", or "each", but tblInventory keeps raw counts. So, if
I order 1 case and OrderMultiple is 24, I log a receipt of 24 items in
inventory when the case is marked received. I typically also write code
that allows the user to "order for stock" - that checks the Inventory
against the Min and Max for each item and automatically generates a Purchase
Order.

Some of these issues might apply to your Asset Tracking application. You
might order a case of Staplers, but people don't use an entire case - just
one. Regardless of how you order items into assets, you should ultimately
track them by "each."

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

 

[Darryn]

Darryn-

What tables do you have now? Please post the general layout of each.

This my Main equipment table

EquipmentrecordID (PK) Autonum ( not shown)
HaleEquipNumber text
CategoryID num
SubCategID num
ItemTypeID num

Equipment Description text
StatusID num
SizeID num
ColourID num
Acqusition date/time
Disposal date/time
PurchPrice currency
IsUnique yes/no If a unique item or a bulk item
SerialNumber num
Quantitypurchased num
(On my form I will use Is Visible to show hide the two fields above
based on is unique yes/no)
HasLifespan yes/no If equipment has a fixed lifespan
Lifespan num
YearColourCoded yes/no
YearColourCodeID num
ManufacturerID num
Model text
SupplierID num
AreaNameID num Area Name from legacy system
Comments

I have five tables following to allow a heirachical structure for data
classification

Categories
Category ID (PK)
Category

Category/Subcategory link table
Category ID (PF)
SubCategory ID (PF)

Subcategories
SubCategory ID (PK)
SubCategory

Subcategory/item type link table
Category ID (PF)
Item type ID (PF)

Item type table
Item type ID (PK)

I then have a kits table for kits of gear composed of multiple items
KitID (PF)
EquipmentrecordID (PF)

I also have a program equipment list table for picking a gear list for
a program
ProgramID (PF)
EquipmentRecordID (PF)
CategoryID
SubCategID
ItemTypeID
Item Quantity

A staff equipment table to track gear assigned to staff members
staff ID (PF)
EquipmentrecordID (PF)
etc
etc

I also need to implement a maintenance schedule but are not sure how
to do it, I would like to be able to set a time period ie 6 months and
then have the record flag if maintenance is required

When I'm doing an inventory application, I create a tblProducts table that
has the following fields:

ProductID, ProductDescription, OrderBy ("Each", "Case", etc.), OrderMultiple
(if "Each", then 1, else the size of the "Case", etc.), SellBy (usually
"Each"), MinInventory, MaxInventory, EOQ (Economic Order Quantity)

Code that handles Purchase Order Details makes sure that items are ordered
by the "case", "lot", or "each", but tblInventory keeps raw counts. So, if
I order 1 case and OrderMultiple is 24, I log a receipt of 24 items in
inventory when the case is marked received.

Do you have record for each indiv. item?

I typically also write code
that allows the user to "order for stock" - that checks the Inventory
against the Min and Max for each item and automatically generates a Purchase
Order.

I hope this gets across what I a trying to do, I think I have bitten
off a bit more than I can chew as I have had no previous access
experience

Thanks so far

Darryn
-- remove two eyes to reply!

 

[Darryn]

I guess from this post you can see I have gone away from trying to
input to different forms!!

This my Main equipment table

EquipmentrecordID (PK) Autonum ( not shown)
HaleEquipNumber text
CategoryID num
SubCategID num
ItemTypeID num

Equipment Description text
StatusID num
SizeID num
ColourID num
Acqusition date/time
Disposal date/time
PurchPrice currency
IsUnique yes/no If a unique item or a bulk item
SerialNumber num
Quantitypurchased num
(On my form I will use Is Visible to show hide the two fields above
based on is unique yes/no)
HasLifespan yes/no If equipment has a fixed lifespan
Lifespan num
YearColourCoded yes/no
YearColourCodeID num
ManufacturerID num
Model text
SupplierID num
AreaNameID num Area Name from legacy system
Comments

I have five tables following to allow a heirachical structure for data
classification

Categories
Category ID (PK)
Category

Category/Subcategory link table
Category ID (PF)
SubCategory ID (PF)

Subcategories
SubCategory ID (PK)
SubCategory

Subcategory/item type link table
Category ID (PF)
Item type ID (PF)

Item type table
Item type ID (PK)

I then have a kits table for kits of gear composed of multiple items
KitID (PF)
EquipmentrecordID (PF)

I also have a program equipment list table for picking a gear list for
a program
ProgramID (PF)
EquipmentRecordID (PF)
CategoryID
SubCategID
ItemTypeID
Item Quantity

A staff equipment table to track gear assigned to staff members
staff ID (PF)
EquipmentrecordID (PF)
etc
etc

I also need to implement a maintenance schedule but are not sure how
to do it, I would like to be able to set a time period ie 6 months and
then have the record flag if maintenance is required

Do you have record for each indiv. item?


I hope this gets across what I a trying to do, I think I have bitten
off a bit more than I can chew as I have had no previous access
experience

Thanks so far

Darryn
-- remove two eyes to reply!

-- remove two eyes to reply!

 

[John Viescas]

Looks like lots of category tables, but I think the basic structure is
sound. To track maintenance, you could add a RequiresMaintenance (Y/N)
field, MaintenanceInterval (perhaps in days), and LastMaintained (date/time)
to the main equipment table. This would allow you to generate maintenance
reports.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

 

[Darryn]

Looks like lots of category tables, but I think the basic structure is
sound.

Its for the Outdoor Ed department of a school, there is a really
diverse range of equipment and I was trying to break it down as much
as possible so the combo boxes would not get too unwieldy but I could
probably cull the first one out.

I sort of had the following heirachy worked out using water based
equipment as an example

Category=Water
Subcategory=Watercraft, Equipment
Item Types (assoc with watercraft) powerboat, sailboat, canadian
canoe, sea kayak

To track maintenance, you could add a RequiresMaintenance (Y/N)
field, MaintenanceInterval (perhaps in days), OK

and LastMaintained (date/time)
to the main equipment table. This would allow you to generate maintenance
reports.

OK

Thanks again

Darryn
-- remove two eyes to reply!