Many to many relationship problem 1

[tekaccess]

Hi To all
I am working on this from last two week but no sucess, is any body help me out?

tblSupplier
supplierid(pk)
name
address
---
---
tblinvoice

invoiceid (pk)
purchasedate
supplierid

tblproduct

productid (pk)
name
---
----
tblinv-prod
inv-id (fk)
prod_id (fk)

What i want to do is that after selectinf the supplier id from supplier through combo, I want to store all record in product table under one invoice and so on, I just want to add invoice number only once.
Any detailed answer will be appreciated. I have already spend alot tome on this.
Thanks

 

[fneily]

You have a supplier table and you have a product table. One supplier can offer many products, one product can be offered by many suppliers. A many-to-many relationship. So you have a junction table - invoice table. So, tblSupplier has supplier info, tblProduct has product info. The tblInvoice has these fields:
InvoiceID Primary Key
supplierID Foreign key from tblSupplier
productID Foreign key from tblProduct
PurchaseDate
Quantity
Any other COMMON fields.

So tblSupplier connects to tblInvoice that connects to tblProduct.

That's the starting point. Now everything else falls out.

 

[tekaccess]

Thank you very much, can u assist me little bit more from query to main for and sub form(supplier,invoice and product) that will be nice, because i am so confused and many tables and relatioships in my database.
Once again thanks

 

[fneily]

You need to be more specific in what you want. Let's say you have your tblSuppliers table and tblProduct table built. Now you want to put in the transactions (tblInvoice). You can build a form from tblInvoice. Then, in design view, delete the supplierID and productID controls and using the COMBOBOX WIZARD recreate them (make sure to show both ID and description). So when you go to form view, you'll put in an InvoiceID (this could be an autonumber thus no typing), select a supplier and product from the dropdowns, default the date to today's in the property sheet, and just have to type in quantity.

 

[tekaccess]

Thanks for your prompt response.
Actually i am keeping the record of all the products which i received from different suppliers under different invoice numbers.
First i select the supplier through combo box, then enter the invoice number of that supplier after that i record all the items in product table(subform). Again if i have to put the remaining items under that invoice i just enter the invoice number and rest of all the product should go under that invoice.
The same case with other suppliers and invoice so on.
If u need any further detail please let me know
ThaNKS

 

[fneily]

I'm not sure what you're doing. If you're using my three tables, I'd make the primary key of tblInvoice to be InvoiceId, SupplierID, ProductID. (multifield primary key). So in tblInvoice, you'd have:
Inv1, Att, Phone
Inv1, Att, Computer
Inv1, Att, etc.
Inv2, General Foods, Apples
Inv2, General Foods, Blueberries
Inv2, General Foods, etc.
etc.
Again, supplierID and ProductID are dropdowns. You'd type in the invoiceId.
First you'd populate the supplier table and product table.

 

[tekaccess]

Again
1. I select supplier from combo
2. Enter Invoice Number for that supplier(items which i recevied under that invoice number)
3. on a sub form i entered all the item i have recived from that supplier and under that invoice number

I do hope this will clear u my situation

 

[lespaul]

and what difficulty are you having implementing this process? Sounds pretty straight forward.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[tekaccess]

I am confused, I cannot enter many items under one invoice number. How can i do that, I mean i just have to enter invoice number only once.
Is any body there to help me out?

 

[lespaul]

Create a form that has a combo box that lists your suppliers. Add a text box that you enter the invoice number in. Add the subform that allows you to enter the items. When you add the record from that area capture the invoice number from the text box.

What part of this are you having trouble with? Have you followed all the previously given instructions? Do you get an error message? Are the records not added to the table?

Have you made the recommended changes to your table structure? What is the final table structure that you are inserting records into? Do you have the master/child relationship set up in the form for the subform to correctly identify what child records to display?

There are plenty of people here ready and willing to help you, but you don't seem to want to share enough information for us to help you.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[tekaccess]

Thank U very Much lespaul
I am doing the same thing with 1 :n relation, the only problem is that i cannot save many item under one invoice number. Here u said that capture invoice number from the text box, Please explain it little bit more, I think i am almost at the finishing line.
Thank u.
There is nothing secrete that i am hiding from others. Here is again alist and relation of my tables.
tblSupplier
supplierid(pk)
name
address
---
---
tblinvoice

invoiceid (pk)
purchasedate
supplierid

tblproduct

productid (pk)
name
---
----
tblinv-prod
inv-id (fk)
prod_id (fk)

Waiting for reply

 

[lespaul]

the only problem is that i cannot save many item under one invoice number

what have you tried? How are you inserting the records from the subform?

 

[tekaccess]

OK
i am at inventory / stock end. Mean I have to enter the one invoice number against many items provided by the suppliers.
If i build a master to child link (invoice to subform(product) the invoice number generated auto against many items. where as i need to enter the invoice number provided by the supplier.
so in this scenerio what should i do.

thanks

 

[lespaul]

you still don't have a table structure that supports a many to many relationship.
[tt]
Suppliers
SupplierID
Name
Address
(any other information about the SUPPLIER)

Products
ProductID
ProductDescription

Invoices
InvoiceID
InvoiceNumber (from supplier's invoice)
InvoiceDate

below is the many to many relationship table - it has both the invoiceID and the productID
InvoiceItems
InvoiceItemID
InvoiceID (FK to Invoices)
ProductID (FK To Products)
Qty[/tt]



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[tekaccess]

Thank You Very much, Know I under stand the Northwind too.
In northwind
invoice=orders
invoiceitems=orderdetail
Once again thanks

 

[tekaccess]

Now when i enter the invoice number and after that when i click in the subform(product) a error message comes up.
"You cannot add or change record because a related required is required in table customers"
My tables relationship is exactly like northwind now.

Any Suggestion?