On a weekly basis I am manually entering data into a spreadsheet for the same thirty or so people, for the same tasks, on multiple days. The information is placed on a form that I submit for them to be paid for the work.
What I want is a small dbase that will lessen the amount of time in which I spend on this task that will keep me from having to input the names, SSN, etc each time.
In my limited access capabilities here is what I can offer:
-- tbl_personnel - will contain the individuals Lname, Fname, SSN
-- tbl_AFTP - will contain the Date, Period 1 and Period 2 checkmarks for times worked. Periods 1 & 2 refer to five hour blocks of time that would be worked. i.e. 0800-1300; 1300-1700; 1700-2400 hrs (military time of course)
-- tbl_Unit - will contain the Unit name, address, city, state information
** The organization structure is this:
-- Personnel belonging to the UNIT
--- AFTP work performed by above personnel
** What I cannot figure out is how to make all of this fit together into a dbase that would track the personnel and weekly AFTP work, but also assign a unique TL number. Each week before I send this pay request forward I have to assign a unique TL number (Transmittal number) to that particular request for tracking purposes.
I would greatly appreciate anyone's help with putting this together in a working format. Attached is what I have tried to put together.
while it might not be viewed as kosher to recommend a differing user forum; over at UtterAccess.com - in the user forum, at the bottom - is one where people post their resume - - - and they are pretty much overall reliable ones...
just in case you can't get all this sorted out via forums alone....
tblUnit will have fields UID (unit id), UName, UAddress, UCity, UState. Designate UID as the Primary Key. It should be type AutoNumber.
tblPersonnel will have fields PID (personnel ID), UPK (unit primary key from tblUnit), Lname, Fname, SSN. The fields PID and UPK should be designated as Primary Key. PID should be type AutoNumber, UPK should be type Long (don't use AutoNumber; it's value is inherited from tblUnit).
tblAFTP will have fields AID, PPK, Date, P1Mark, and P2Mark. Designate AID and PPK as Primary Key. AID should be type AutoNumber; PPK should be type Long.
In the Relationships screen, set up the screen by putting tblUnit on the left, tblPersonel in the middle, and tblAFTP on the right. Create a one-to-many relationship from tblUnit's UID field to tblPersonnel's UPK field (they are always identical), and another one from tblPersonnel's PID field to tblAFTP's PPK field.
With all this done, all you need to do each week is to open tblUnit table, click on the appropriate '+' sign to select a person, and click on the next level '+' sign to add AFTP info.
Any queries and reports will use the built-in relationships to correctly identify personnel and work times.
Thanks for the quick reply and I have now redone what I had submitted earlier. Selecting the "+" signs work well and I am now going to play with creating a form in order to input the information.
For discussion, I am thinking that I could create a Main Form for AFTP with a subform showing the personnel. Would this be correct or is there a better, more functional form suggestion that you may offer.
The short answer is that you don't need a form; the relationships, that create the '+' signs, take the place of the form.
If you still want a form, all you need is a form with 2 Combo Boxes. cmbUnits would feed off tblUnits, cmbPersonnel, off tblPersonnel.
The first combo box would do a straight 'read' from tblUnits. The second Combo Box's RowSource should be a query that allows only personnel that exist in the Unit selected by cmbUnits. You would then allow for input that would save into tblAFTB. Don't forget to save the PKs.
I won't tell you how to do this now. You gotta get used to the Help files. They're good, useful, and available.
ps That doesn't mean I won't help in the future, but try getting it yourself first.