access 2007 database table design

Dear all,
i want to create a database, purpose is user will request some items, each request they may ask 30 or more items, these items details such as itemcode, description, unit of measurement,
qty, price, they have to enter manually not from master data meaning not dropdown or selectable. but some fields like their department, their name, items category they can select from different master tables. Now my question is each request we have one reference number, and request number. these number can be different, user should not enter request number it should be auto generated number, and reference number user should enter but each request one number. how can i accomplish this target. i mean how to design for these numbers fields, what properties i need to give.
nivasnetAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
If I'm understanding your question correctly, it sounds like your requests will have some top-level (master) information, and each request may have many detail records.  This is a one-to-many relationship that should be stored in two tables:

tblRequest (this contains 'master' information about the request)
-----
RequestNumber: AutoNumber/PrimaryKey
RequestDate : Date/time
'etc





tblRequestDetails (This contains 'detail' records associated with each request)
------
RequestDetailID     'Autonumber, Primary Key
Qty
Price
RequestNumber ' Number, this links the detail records to the parent records
RequestorID  '<--- Users ID looked up from Employees table
ReferenceNumber '<-- I'm not sure if this belongs in the Master or Detail table (it depends on what this number represents)
' etc





To display this type of data, you would use a mainform with the "parent" information and a subform with the "detail" information, linked by the RequestID which appears in both tables.  If this is set up correctly, the RequestNumber will automatically populate in the subform as detail records get created.

See this tutorial for details:

http://office.microsoft.com/en-us/access-help/create-a-form-that-contains-a-subform-a-one-to-many-form-HA010098674.aspx



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted>

You can see more info on "One to Many relationships" by simply Googling the term

You can also see a good example of this with the Northwind Sample Database

(Orders-->OrderDetails)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.