My database just a mess...

[vincentt]

Just want to share with everyone with my database...

It is a database consists of tables:
customer info
order info
receipt info
project info

Congratulations to me that I could link customer, order, receipts together in
a form, however, each order could have very different project info (some
fields might not be in some projects but appear in another, and each project
may have up to 20 - 30 fields).

So I am wondeing how could I connect the table/field on the form, so that,
when I enter the project ID on the form, it will turn out to the suitable
fields/table to me?

I really have no ideas and I have saw some database design and access book,
but don't know how could it work.

Thanks in advance! (And I hope I could understand your suggestions)

 

[mnature]

tbl_Orders
OrderID

tbl_Projects
ProjectID

tbl_OrderProjects
OrderProjectID
OrderID
ProjectID

Your description is a little vague, but this should give you an idea of how
to make the tables you need. The table, tbl_OrderProjects, can link any
Order with any Project. In this way, an order can be linked to multiple
projects, and a project can be linked to multiple orders. You will probably
want to include some fields in tbl_OrderProjects to give more information,
such as dates and memos.

This is a very basic way of setting up tables in a relational database,
where your orders and projects are listed only once in their respective
tables, but can be referenced multiple times by the linking table. Hope this
helps.