Hi,
I'm looking for some assistence with designing a data entry/modification form.
I have a database with a simplified design similar to the following:
Two tables:
##########################
STAFF:
ID
Name
##########################
##########################
SALE:
ID
SALE_date
SALE_quantity
STAFF_id
##########################
There is a 1 to many relationship between the STAFF and SALE tables - indicating the amount of SALEs for one member of STAFF for one particular day. Eg.
#101 Joe Bloggs -----> 10/02/2003 20 units
11/02/2003 15 units
14/02/3003 5 units
(As an asside, I envisage only one SALE record per STAFF member per day but that would be best implemented via the form) .
The problem I have is in the best way to design the data entry/modification form.
I've put together a calendar control to run a query using the chosen date and I was trying to use this with a simple form to work with the data returned from the join of the two tables filtered for that date. That would allow me to edit an existing SALE record to change the quantity but it wouldn't allow be to easily add a record for a STAFF member when they made their first SALE of the day.
I don't want to use a SALE record for every STAFF member for every day - with them starting off as 0 quantity. So can anyone think of a suitable way to design a form that will - for the supplied date:
1. Display existing joins between the STAFF and SALEs table, allowing for editing the quantity field.
2. For STAFF with no SALE records for that day, display just the STAFF details and allow for entry of the quantity which will create the appropriate SALE record on submission
3. It doesn't really matter if removing a sale is achived by deleting the SALE record proper or adding 0 to that particular SALE record's quantity field.
I'm fairly OK around RDBs but I'm not a coder so I'm limited to what I can design myself - using standard controls is fine etc but not VB - so I need some help with this.
I know it's really cheeking to ask someone to do my work for me but any help, pointers or references would be very much appreciated.
I'm looking for some assistence with designing a data entry/modification form.
I have a database with a simplified design similar to the following:
Two tables:
##########################
STAFF:
ID
Name
##########################
##########################
SALE:
ID
SALE_date
SALE_quantity
STAFF_id
##########################
There is a 1 to many relationship between the STAFF and SALE tables - indicating the amount of SALEs for one member of STAFF for one particular day. Eg.
#101 Joe Bloggs -----> 10/02/2003 20 units
11/02/2003 15 units
14/02/3003 5 units
(As an asside, I envisage only one SALE record per STAFF member per day but that would be best implemented via the form) .
The problem I have is in the best way to design the data entry/modification form.
I've put together a calendar control to run a query using the chosen date and I was trying to use this with a simple form to work with the data returned from the join of the two tables filtered for that date. That would allow me to edit an existing SALE record to change the quantity but it wouldn't allow be to easily add a record for a STAFF member when they made their first SALE of the day.
I don't want to use a SALE record for every STAFF member for every day - with them starting off as 0 quantity. So can anyone think of a suitable way to design a form that will - for the supplied date:
1. Display existing joins between the STAFF and SALEs table, allowing for editing the quantity field.
2. For STAFF with no SALE records for that day, display just the STAFF details and allow for entry of the quantity which will create the appropriate SALE record on submission
3. It doesn't really matter if removing a sale is achived by deleting the SALE record proper or adding 0 to that particular SALE record's quantity field.
I'm fairly OK around RDBs but I'm not a coder so I'm limited to what I can design myself - using standard controls is fine etc but not VB - so I need some help with this.
I know it's really cheeking to ask someone to do my work for me but any help, pointers or references would be very much appreciated.
