Need Help in Database Design and Relationship

[ultragorgon78]

Currently i have problems with my project. The situation is i want to register new case which has many exhibits and many transactions. A transaction will have several exhibits.

So i have three tables:
1. Case
ID Case - Auto number
Case Number - Text
Date - Date/Time
Time - Date Time

2. Exhibits
ID Exhibit - Auto number
Exhibit - Text
Quantity - Text
Type - Text

3. Transaction
ID Transaction - Autonumber
Type - Text ...(In/ Out)
Exhibit - Text
Quantity - Text
Transaction Date - Date/Time
Transaction Time - Date/ Time
Recieve By - Text

I want my form Case with subform Exhibit and subform Transaction. 1st transaction for each cases will be stored in Exhibits tables and the next transaction for that case with combo box from exhibit tables

I tried several relationship for these 3 tables but i didnt get the result as i want. Hope someone help me

 

[plog]

I was looking past all the glaring issues I saw, trying my best to follow you, until this which stopped me in my tracks:

1st transaction for each cases will be stored in Exhibits tables

You have a transaction table, however you want to store the 1st transaction in the Exhibits table? Is that a typo?

Now back to the other issues that I was able to initially overlook.

1. What is [Case Number]? Is that a unique identifier? Will it ever change? How does it get assigned? Is it always a number or can it contain alpha characters?

2. Date and Time are bad field names. Both of those are reserved words in Access. You should name them something more descriptive like CaseDate and CaseTime. Or a different prefix which better describes what they are.

3. Your Date and Time fields are both Date/Time fields which means you only need one of them. A Date\Time field type can hold data like this: "10/11/2012 09:24:00 AM". You don't need 2 seperate fields.

4. What is the Exhibit field in your Exhibits table contain? Is it the name of the exhibit or a unique identifier like you have with [Case Number]?

5. Why is Quantity a text field and not numeric?

6. What are your Type field values? Most likely these should be in a different table and then you use the ID field of the Type table and store it in Exhibits instead of the text value.

7. You have two Type fields--one in exhibits and one in Transaction, I'd prefix them with what they are for (i.e. ExhibitType and TransType) to avoid confusion.

8. If Type can only have 2 values, and must have one of those 2 values, I'd change it to a Yes/No field type.

9. What does the Exhibit field in Transaction contain? Is it the same as the Exhibit field in Exhibits? If so, its redundant and you don't need it if those tables are somehow to be linked. If it needs to remain, I'd prefix it like you should with your two Type fields.

10. Why is Quantity a text field in Transaction, and I'd rename it to avoid confusion with the other Quantity field.

11. See issue #3 above about the [Transaction Date] and [Transaction Time] fields in Transaction.

12. I think [Receive By] should be handled in the same manner I suggest for the Type field in Exhibits (issue #6).

Those are just your structure errors. You still have relationship errors which I can't really help you with until you explain your process better. Can you explain how this is to work? Don't use database jargon or reference your database at all. In plain english tell me how this should work.

 

[ultragorgon78]

Thanks for your reply and i'm sorry if i cant explain it better because i'm not so good in English especially to describe something complex like this. English is my second language here

Actually what i means is exhibits from 1st transaction which is "IN transaction" stored in exhibit tables related to specific case number. One case number can have many exhibits.

The next transaction which is "Out transaction" for specific case number and i want to make a combo box from exhibits tables. I read before that i can do that by vba coding and i still don't know how to do that.

Ill try to explain bases on the issues arises.

#1. Case Number
Case Number is unique identifier for each cases which can contain numeric and alpha number.
Eg AKB-2012-0051-SAL

#2 #3 Noticed and many thanks in advance. Actually i wrote the field names with Malay language (my first language at my country)and i didnt have issues with reserve word. Right now i rename the field with better name so i can differentiate several dates.

#4 Exhibit
it is a name for exhibit and not unique identifier.

#5 Quantity field
I use text because sometime i have liquid exhibit which will use liter. Hope u can advice me to choose the best filed type here.

#6. #7. #8.
Thank you for your advice. ill create another table for exhibit type and relate the "Id exhibit type" to exhibit table.
Eg 450 liter, 1,245 unit, 2 box, 1 pallet, 3 carton.
Ill use Yes/No for trans type

#9. Exhibit.
It is the same exhibit in exhibit tables. Ill change exhibit in transaction table to ID exhibit.

#10 Quantity in transaction table.
Ill change the quantity field to "Trans Quantity". Sometimes transaction out just half from exhibit quantity. Please advice me with this issue.

#12. Received By
Ill create one more tables "Personnel" and relate Id Name in both tables.

I hope my explanation will help you to understand my problems and error. Please let me know if you need some more explanation.

 

[ultragorgon78]

Maybe i should change PK in Case table. Remove Id Case (Autonumber) and make Case Number (Text - 19 filed size) Primary Key so i will not enter same case number twice...

 

[ultragorgon78]

Hopefully this file will help... i do not know how to relate table transaction and exhibit. Any advise and help is welcomed.

I am still newbie in MS Access and currently using Ms Access 2007.

 

[jdraw]

Further to plog's specific comments, I'd like to make a few suggestions.

Before getting too deeply involved in the intricacies of Access, I suggest you tell us in simple terms WHAT exactly you are trying to do. You say English is your second language and you are not too good with it- but your English is quite understandable.

You say it is difficult to describe your situation, but if you can't describe it, I contend you can't build it, and certainly we can't offer much advice.
Tell us WHAT you are trying to do in as simple terms as you can. Tell us what these terms (CASE, EXHIBIT, TRANSACTION...) mean in your "Business". You know your environment and we don't - so if we are to help you, you'll have to communicate with us on your situation and needs.

As you are finding out, working with Forms/subforms etc before getting your tables and relationships defined/designed, is very time consuming and unrewarding. So step 1 in most database projects is getting a good definition of WHAT you are trying to do. Understanding the Entities/Tables involved and how they relate to each other.

Pretend you are in a lineup at McDonalds, turn to the person behind you - who doesn't know you, Access, database or your business - What would you tell him/her you were trying to do. Make it that simple so we all understand.

Also may readers do not have Acc2007 nor 2010 and can not open/use an accdb formatted database.