Best Ideas for Form Design???

[helpwithforms]

Hi,

I am having issues with figuring out the best way to set up a database. I
work for a distribution company and am designing a database to track daily
operations. I have to set up forms to allow users to enter information,
Most of my Access experience has been with queries and reports and I am used
to pulling data from external sources rather than entering it, so I haven't
done this really since college.

I have 2 examples from other databases other people built that I am basing my
tables on, one is more simple, one is very complicated. I can't use either
to adapt for that reason, and would prefer to build my own anyway. I have
takes some ideas from each of them with my table setup.

We have four buildings and up to 3 shifts for each building. I have a
control table set up that has autonumber(field called ControlID), date,
building, and shift. Then I have separate tables for receiving and shipping.
The receiving table has ControlID, Cartons, Trucks. The shipping table has
ControlID, CustomerID, Loads, Cartons, and Full Pallets. The idea is to have
the ControlID one for one for each receiving or shipping record, and in some
cases a shift could receive AND ship.
Receiving is easier and simpler, but shipping has up to two customers, so for
instance say Control ID 5, 10/11/06, Building 4, and shift 1 is in the
control table. The shipping table could have two entries: Control ID 5,
CustomerID 1, Loads 10, Cartons 10,000, and Full Pallets 20 AND ControlID 5,
CustomerID 2, Loads 20, Cartons 20,000, and Full Pallets 30.

So, what I've been trying to do is have a front page where you select the
building and shift, and it also has the date. What I can't figure out is the
best way to "feed" the receiving and shipping forms the ControlID. Also, I
cannot figure the best way to enter the data on a form for shipping, if I
want to enter it for each customer at the same time (I don't want to enter
different records after another).

Any help appreciated - Sorry if this is a bit long. Let me know and I can
clarify something if needed.

 

[Guest]

I have 2 examples from other databases other people built that I am basing my
tables on, one is more simple, one is very complicated. I can't use either
to adapt for that reason, and would prefer to build my own anyway. I have
takes some ideas from each of them with my table setup.

We have four buildings and up to 3 shifts for each building. I have a
control table set up that has autonumber(field called ControlID), date,
building, and shift. Then I have separate tables for receiving and shipping.
The receiving table has ControlID, Cartons, Trucks. The shipping table has
ControlID, CustomerID, Loads, Cartons, and Full Pallets. The idea is to have
the ControlID one for one for each receiving or shipping record, and in some
cases a shift could receive AND ship.
Receiving is easier and simpler, but shipping has up to two customers, so for
instance say Control ID 5, 10/11/06, Building 4, and shift 1 is in the
control table. The shipping table could have two entries: Control ID 5,
CustomerID 1, Loads 10, Cartons 10,000, and Full Pallets 20 AND ControlID 5,
CustomerID 2, Loads 20, Cartons 20,000, and Full Pallets 30.

So, what I've been trying to do is have a front page where you select the
building and shift, and it also has the date. What I can't figure out is the
best way to "feed" the receiving and shipping forms the ControlID. Also, I
cannot figure the best way to enter the data on a form for shipping, if I
want to enter it for each customer at the same time (I don't want to enter
different records after another).

Since the relationship between the control table and the shipping table
seems to be hierarchical (one controlId to one or more shipping records), I
would suggest a form/subform arrangement. You have the parent form bound to
the control table and the shipping subform bound to the shipping table. Then
you can link the form to the subform on the controlId field. You could also
create another form for receiving that would be a second subform, also linked
via the controlId.

Form/Subform is a very common technique, If you haven't worked wth it
before, there are scads of examples available. Northwind would be a good
place to start.

Barry

 

[helpwithforms via AccessMonster.com]

Barry-thank you. I think this is going to work. However, I still have one
issue. For my shipping table, because we have 2 different customers we ship
to the shipping table can have 2 records with the same controlID, just with
different customers. I gave this example in my first post:
The shipping table could have two entries: Control ID 5,
CustomerID 1, Loads 10, Cartons 10,000, and Full Pallets 20 AND ControlID 5,
CustomerID 2, Loads 20, Cartons 20,000, and Full Pallets 30.

So, the problem I am having is setting this up so that I can have the
ControlID used for two separate entries (one shipping shift could ship to 2
different customers).

Thank you for your help!

Mike