Relationship Question

[sconnors]

I have two tables PurchaseOrders & Vendors. In my Vendors table the primary key is ID which is an AutoNumber field. I also have InvoiceNumber, InvoiceDate, and InvoiceAmt, my InvoiceNumber has to be defined as a text field since some invoice numbers from vendors contain text.



In my PurchaseOrders table I've created a key called InvoiceIDFK as I want it to be a foreign key from the Invoices table. When I attempt to make a one to many relationship and enforce referential integrity Access is telling me I can't have two different data types.

What is the best way to overcome this? If InvoiceNumber was strictly a number field it would work, but I have invoices with text in them. Any help would be greatly appreciated

[orange]

Tell us more about your business set up. Normally you would see an Order table and a OrderDetail or OrderLineItem table.

My first reaction to the text and numeric InvoiceNumber, is can you have your own InvoiceNumber and relate that to Customer/Vendor InvoiceNumber. Not sure if it's practical/relevant to your situation.

For your own benefit, I recommend you build a data model and get it set up to support your requirements before getting too deeply into physical Access. The info at this link may be helpful.

This free model from Barry Williams' site may be useful.


Good luck.

[sconnors]

Thank you very much I will check out the links you've suggested.

[ArviLaanemets]

Something is messy in your setup. Do you have vendor's invoice number in Vendors table or what? This is possible only, when vendor uses same invoice number for all his invoices!!! Somehow I dont buy this!

Let us start from process description.

1. You need some items/articles. So you need a registry of them. tblArticles: ArticleID, ArticleName, ...

2. You buy articles from various vendors, so you have to store information about them in some registry - let it be a table tblVendors: VendorID, VendorName, ... . (You may have more info like contacts, status etc. here too, or you can have separate tables for them when you need to store several values or keep history for those parameters)

2. You want to buy some articles from vendor, so you send an order. Probably you need to have some overwiew, what you have ordered, so the first table (I differ tables and registries here) you need is tblOrders: OrderID, OrderDate, DeliveryDate, VendorID, ...

3. In Order, you list one or several articles you want to buy, and how much you want them. It is better to have separate table for them (otherwise you have to make separate order for every article): tblOrderRows: OrderRowID, OrderID, OrderRowNo, ArticleID, Qty, ...

4. The vendor sends you some or all ordered goods and invoice for them. Here the order something happens can vary depending your relationship with vendor. Maybe you get ordered goods and invoice with them. Maybe you get at first invoice, and you get goods after you have payd for them. The vendor can sell all ordered goods at once, or he can do this in several steps. You may get the invoice for whole order, and the goods are sent by some predefined shedule. So probaly you have to store invoice for bookkeeping, and you have to store info about arrived goods - it means you need at least 2 tables more.

4.a tblInvoices: InvoiceID, InvoiceDate, PaymentTerm, VendorID, VendorInvoiceID, OrderID, OrderSum, VAT... . You can have here also invoice status (paid or not). You can have a table tblInvoiceRows too, but for bookkeeping is this not obligatory.

4.b tblArrivalLog: ArrivalID, ArrivalDate, InvoiceID, VendorID, OrderID, ArticleID, Qty, Unit, UnitPrice, VAT, ... . When not all ordered/invoiced goods are arriving at once, probably you need some party id too here.