I have a problem regarding the relationship among table that just has many to many records inside that.
right now I have 3 table:
1) table transaction
2) table sts_pick (which just many to many status of transaction)
3) table product.
field name for transaction table are :-
from warehouse,to warehouse and document number (where assigned as primary)
field name for sts_pick table are :-
document number,product code,release quantity and line number - where each of field name has redundance data.
field name for product table are :-
product code(assigned primary key),product_description, and product weight.
my problem right now is when I create relashionship between field document_number with table transaction and table sts_pick are still can't.
So for the result is at my form..
when I insert the number in text document_number all record in table sts_pick will appear such as all related document number,release quantity,product code and product description.But all those field name from table sts_pick can't be appear at my form.
So, I'm really need your help for my complicated problem.Your help is fully appreciated.
notes: it will be easier for u if i can attach my sample of problem.
I have a problem regarding the relationship among table that just has many to many records inside that.
it will be easier for u if i can attach my sample of problem.
Thank you for your help and advice...
Could you attach a Zip Copy of your database so that I may look at it and see if I can fix it for you? Remove any pertinent data and just add some dummy stuff. Then explain the logic of your database as to where you are wanting to start from and what relates to what and so forth. If you Post it here, either myself or others might have a chance to possibly solve it for you.
Here about my sample of problem attatched for you guys to view it so that something can be solve or discussed..thanks for you all for help me..
Then explain the logic of your database as to where you are wanting to start from and what relates to what and so forth.
Brieftly, if you look in my table, there has table trans1, table sts_pick, and table product. the problem is how to relate table trans1 and sts_pick with field name document number. Based on relashion that I've been done, the data from table sts_pick can't be appear after the number of document_number(field name) entered.
So the output is from table sts_pick, -field name- (list of line_number,product_code,actual_release_qty,and (product_description-from table product))
Any help is fully appreciated or any suggestion please tell me.
From what i can see from the database you have provide there is one question I have for you. You have no data in the tran1 table. You have the document_number as your primary key for that table so when you try to create a relation between sts_pick and tran1, access is looking for corresponding document numbers between each table. You really need the trans1 table to list all the document numbers in order for you to enforce referential Integrity between the tables.
I've been try to put all document number record in table trans1, but the result is still can't coz of the document number in sts_pick table is redundant data. so if I try to put it in table trans1, theres no primary key. then the relationship can't be done. User also can enter document number many time, so make it many to many relationship status..
So do u have any ideas to make it relate each other?
I just d/l your database. OK, let's take it a bit at a time. Table structure is not clear to me so let's do it a little at a time.
(1) In sts_BT table, the fields "type_print_loc" has the EXACT same data as "type_book" such at this: Type_print_loc = AGR and Type_book = AGR. Why? What does either field really mean as type_print_loc is the PrimaryKey for this table. Are you inputting a different code in there manually each time? How do you derive at it?
(2) In the sts_BT table, the "type_code"field, all the entries are BT. Is that the ONLY type of TypeCode you will have in that field? Will there be others? If so, then a ComboBox would be a good choice to use for that purpose.
(3) In the same table "type_description" ALL entries are "Branch Transfer". Same as above, will there be any other choices to make for that field? If so, ComboBox.
(4) Now, for another table. The Trans1 table has "from_warehouse" as a link to "warehouse_code" in Warehouse table. I take it that you wanted to be able to pull in the Warehouse_code into that field in the Trans1 table? I did that for you and made it a ComboBox based on the Warehouse_code. That way you can select the WarehouseCode from the Warehouse table without having to manually type it in. Also did the same for the product_codefield. Now, it can be selected from a ComboBox without having to manually input it as well.
Now for the "to_warehouse" field in the same table (Trans1), will that be the same code as the "from_warehouse" above? Will they ever be one in the same? Or totally different warehouses?
(5) In the Trans1 table, what does the field Number mean? It isn't self explained and being the way it is now would not mean anything logically to another user. You have Document_Number as the PrimaryKey for this table and that is labeled correctly.
So for now let's take a look at the above and then work our way on through it. Bear with me because you MUST have properly structured tables to start with. Just a question out of curiosity....have you been inputting all this redundant data yourself? Then if another user was to be inputting a NewRecord, they would not have any idea as to what would go in those fields without somehow being able to see a choice for them. Follow my point there? So therefore something to Select from would be needed, like a ComboBox or anything that shows a choice of options.
I will look at the rest of your tables and see what else I can do for you but I think we might get it to working in the proper manner before long. AFTER we get all the tables done properly, THEN we move on to the forms. I did relate your Trans1 table to the Product table using the Document_number but there is still more to be done. Looking forward to more communication with you on this.
have a nice one,
I attached what I have worked on so far for you. The rest is to come later unless you get it all worked out before I get back...or someone else gets it done. Talk again soon.
based on the attachment from u reply back to me, I saw has no changes with the relationship..so I hope u can reply back to me. Thanks.
I hope tomorrow we can talk again tomorrow..and also I'll tell u how the flow system so that u'll more understand.thanks again.
c u friends..
While in your tables, go to your toolbar and select Tools/Relationships.
Look at the Trans1 and Product tables. Those both have relationships, but I haven't finished with that part yet. The part that is done is in the Trans1 table on the from_warehouse and product_code fields. Look that them and select those fields. You will see that they now get their values from the Warehouse and Product tables. To see how that was done, in the table DesignView click on the Lookup tab at the bottom of the table in the Properties section. Now click in each of the fields mentioned above. When you get to both of those notice that I set it to ComboBox and then Click in the RowSource field and then on the ... out to the side of that field. The query builder will open up and show you what it is based on. OR, instead of clicking on the ... while in the RowSource field just hold down Shift and hit F2 to open that window all the way to see a different view...similar to normal SQL. That is how you get the fields in as ComboBoxes.
But I haven't done the DocumentNumber field in that table just yet. Right now about to head to bed and maybe get to this sometime possibly early tomorrow. Are you following how I am trying to show you about your table structure??
glad to be of whatever help I can.
have a nice day,
Yes I'm looking for what ur try to tell me. thanks..ok lets I explain for your quest about my table yesterday. ok for first question.about table sts_pick right.field name 'type_print_lock' and type_book has the same data..actually I still dont organize that table yet..coz my main problem right now is table trans1 and sts_pick. what I'm trying to do is to relate field name document_number.. so user can enter document number based on the record in table sts_pick and all the list from table sts_pick will be appear.the prob is how to relate it.it because if I assigning document_number field from table trans1, then the user can't enter the number many time because its a primary key.So if I dont set any primary key between them, access can't find the record.So do u has any ideas?please help me..
In status of table sts_BT is after user key in the warehouse code from table trans1 (field-from warehouse), in the field name type_last_no_used from table sts_BT will increase to 1. E.g when user wanna print document, the type_last_no_used will increase like from 1000 will change to 1001.it according to the warehouse code entered in the 'from_warehouse' (in table trans1).
the answer of question 2 is about type description from table sts_BT. Yes, all the data status are BT in that field name. it because the printed document are all of document number, product code, quantity release etc from table sts_pick is will be BT status (branch transfer)
3rd quest - thanks for ur ideas...
4th quest - to_warehouse field name from table trans1 is need same data as from_warehouse field name. it because from_warehouse refer to from where the record is made and to_warehouse is refer to the data (from table sts_pick) to be transfered to..it is another warehouse.
lets me explain about the flow of user interface:
example ; from warehouse - HQA-from where user make the transaction- address is ignore. To warehouse - HQB
After code warehouse entered ('to_warehouse' field name) the address HQB will be appear.
then user need to enter document_number, then list of document number related (from table sts_pick) will be listed in the form.
after that the total of quantity will be automatically calculated.
then to print these record, user need to click a command button to print whatever list of document number that they want.
here I will attach sample copy from BT status based on existing system. but it using SDB/400 software.