Forms and Tables

[Nixx1401]

I am creating this database and need some assistance I know the basics in access but need help with the complex stuff. I would like to be able to receive items with description particulars for an individual and issue these items on request by the individual.

So far I know I have to have a table with the the individuals last name, first name, and other info related to the individual, and item table, an issues table and receiving table.

I would have an issue and receiving form to enter the data.

If a person brought in 4 orange and green tshirts and 1 is issued on a given date how would i be able to record this information being able to display what was issued on any given day, the balance of that particular item for an individual.

[NassauBob]

Could you give a working example of the problem? for example: George brought in 3 blue sweatshirts, two ball caps, and a sport coat. What then happens to the items George brought in? Are those items then requested back by George, or someone else? Also, what is the setting? Is it a consignment store, a prison, convalescent center? Seeing the data in its intended operation will go a long way to visualizing the task, and solving it!

[Nixx1401]

The setting is a prison. The person is bringing in the items as part of their property and will be requesting some of those items at intervals. I cannot seem to figure out how i will be able to identify that particular item when it is time to issue. eg. Tom brought in 3 polo shirts color: 1 red, 1 blue and 1 white, on later date he request one or these polos how will issue this one shirt (colour in particular) I hope this helps

[MAF4Fam6]

Personally, I would allow only ONE item per record in the item Table (i.e. 3 identical white polo shirts would result in 3 individual records uniquely identified by ItemID (Primary Key).

Allowing duplicate items in one record via a quantity field, for instance, would make things extremely difficult to allocate what has been issued and what is available for issue.

-RC

[Nixx1401]

In the item table I would have the following eg. Polo Shirt, Tee Shirt, Dress shirt, Long Pants, Short Pants, Vests, Boxer Shorts etc. 1 inmate may be admitted with 2 polo shirts different colors another inmate admitted with 1 polo shirt color pink. When its time for distribution how can i retrieve that persons particular polo shirt with the specified description (color) so I am making a check on an inmate available property that it would indicate that tom brown has 1 polo shirt green in color still in his property and jane henry has a polo shirt muliti colored in his property. I hope this explains my dilemma better?

[MAF4Fam6]

So far I know I have to have a table with the the individuals last name, first name, and other info related to the individual, and item table, an issues table and receiving table.

Recommend using ONE Table for issuing and receiving items. This way, you will completely avoid having to append records back and forth between two Tables. Instead, make ONE Table for issuing and receiving items and call it something like tblItemsReceived. Add a field called something like dtmIssued. When you issue an item, you will update this date field as being received. When someone returns an item, you will do the opposite, by removing the date from the date field. You can program Access to update the date fields for you automatically using Command Buttons. You can then create TWO Queries from your ONE Table:

qryIssueAvailableItems - Select Criteria: dtmIssued field Is Null
qryReturnIssuedItems - Select Criteria: dtmIssued field Is Not Null

Both of these Queries will be used as the Record Source for your Issuing and Returning Forms respectively.

The following is an image of an Access 2007 database:

Attachment 720

1. Burgandy lines denote a Form's Record Source or Combo Box's Row Source.
2. Blue lines denote Open Forms.
3. The tblItemsList Table has only ONE field (Primary Key) to use as the values of the txtItem's Combo Box.
4. If you notice, ALL the Queries with the exception of the qryInmates Query are tied to the tblItemsReceived Table (ONE Table).
5. All Queries were designed using only ONE Table each.
6. As you Add items and Close, those records should appear in your frmIssueAvailableItems Form since the dtmIssue field "Is Null" initially.
7. As you start updating Issue dates in the frmIssueAvailableItems Form and Close, those records should appear in the frmReturnIssuedItems since the dtmIssue field will be "Is Not Null".
8. The small Form you see over the Main Form is a pop up Form as a result of Clicking the "Receive New Items" Command Button from the Main Form.

LOTS!!!...of information.
Hope it helps with your issue.

-RC

[Nixx1401]

thank you can you send me the link to the sample 2007 database listed above

[MAF4Fam6]

thank you can you send me the link to the sample 2007 database listed above

The sample 2007 database is attached (inside zip file) per your request.

-RC