Relationship help

[Anuviel]

Hi, ok this is the setup:

Tables:

Items

Products

Purchase_Line_Items

Purchases

They are related as follows

ITEMS (Item_Code) = Products (Product_Code)

Items (Vendor) = Products (Vendor)

Purchases (PO_Number) = Purchase_Line_Items (PO Number)

Purchase_Line_Items = Items (Item_Code)

Purchase_Line_Items = Items (Vendor)

I am trying to display a portal with Purchase_Line_Items data from Products table.

I got the portal up and running and it displays for example all records of purchases for one item from one vendor - that works correct.

What I want to do is for the portal to show purchases for one item from ALL vendors that carry the item.

Please see the attached file and check Products_W table on the Items Tab.

Please note that it is work in progress and a lot of things does not work, has not been done. If you take a look at the file feel welcome to give any pointers about what is wrong or could cause issues later on.

Thank you very much.

OnLine.zip

[HunterBoss]

I think the problem is that you're linking each item to just one vendor.

The way I see you having the item set up is that there is only one vendor for that item.

So, you're not going to be able to have a list of all the vendors that sell an item because you can only have one vendor.

I think you need a self join table in between item and vendor. That should help.

Christopher

[Anuviel]

Thanks, actually if you take a look at the file you will see that the item IP001 has two vendors associated with it. Basically an item can have as many Vendors as possible so item IP001 can have Vendor A, B, C, D and so on. Product on the other hand can have only one item associated to it. In Product_W layout (Products table) there is a choice of Vendors for item IP001 as you change Vendors corresponding information about item automatically changes such as Item vendor code and or Item cost (different Vendors sell the same item at different prices). Maybe a self-join table is a solution but it did not work for me - do not know why hence the problem. I got my sales portal on the same layout to work exactly as I want it to but when I tried to apply the same relation structure to Purchase portal it did not work. Here is another copy of the file. On Product_W layout on Item Tab there are purchases and sales portals - I want Purchase portal to work like the Sales one does - self-join did not help or I have don something wrong on that part.

Thanks.,

OnLine.zip

[HunterBoss]

ok

sorry

I misunderstood your drop down list box

[Anuviel]

Any update on this? It is still bugging me as I cannot figure out how to solve the problem - been on it all night yesterday.

Thanks.,

[David Jondreau]

I think you need to store the Product ID in the Purchase Line Items table and relate that table directly to the Products table.

[Anuviel]

Got it, will try to implement now, however what is bugging me is that I purchase Items (from vendors) and I sell Products so there is no way that I could purchase a Product so having a Product ID in Purchase Line Items Table seemed illogical to me but I guess that that is the reason as to why my Sales portal works as it is related in the way you just advised.

I will implement this now and will post back the results.

Thanks.,

[Anuviel]

Well that worked when I connected it that way but then again the Purchase_Line_Items pulled the data from Products table - I need it to pull the data from Items table. I know that Products pulls data from Items table but it is a two-match relation where I need to specify Item_Code and Vendor_Name in Products table to get info from the correct Vendor. When I connected the Purchase_Line_Items like you suggested it pulled data from Products table so it will not work for me.

Products table can have only one product at any given time like IP001 while Items table can have as many IP001's as needed if they are from a different Vendor. So when I purchase items in Purchase_Line_Items I might want to order 100 pieces of IP001 from vendor A and another 500 pieces of IP001 from vendor B. If connected as you suggested it will not work as IP001 in Products table is available only from one Vendor at any given time.

[Anuviel]

Well. Imma have to give up - it has taken too much of my time and I started thinking it cannot be done the way I want it.... bummer.,

Thanks to all

[bruceR]

It can be done but you're going to need to get a better handle on several things including relationships. You might try to read up on Anchor-Buoy concepts. Right now you've kinda got everything connected to everything and it's a mess. What's an item, what's a product? Why so many tables repeated in so many places?

Also, the whole Field01, Field02, Field03 thing is a giant red flag indicating incorrect data structure.

[Anuviel]

Thanks.

  Quote

What's an item, what's a product?

The Item is the actual thing that we order from a Vendor. So basically we sell cell phone accessories. We purchase, for example, a case for a Razr and that is an item. We then need to sell it to our customers and it becomes a Product.

Item would basically have a Vendor, Cost, ItemID and a couple of other fields. Product would have a name, we call items differently then our Vendors do and a ProductID (our Inventory numbers are not the same as the ones used by our Vendors), price (what we sell the item for to our customers).

My main requirement is that when we order to Vendors I need to be able to see from how many Vendors we can get the item from at the time of ordering. So when ordering (this is how it works currently) I enter IP001 for the item code and then in the next field (a value list) I get names all of the Vendors that sell that item so when I choose a Vendor the rest of the fields gets automatically populated with the information such as item cost, vendor item code.

Now If for example I choose VendorA I can see ok it will be $1.99 to buy this item form VendorA, if I then choose VendorB I can see how much would it be if ordered from VendorB - $1.75 ,for example, which is cheaper hence better.

Hope it makes sense.

  Quote

Why so many tables repeated in so many places?

I used those relationships for my Value Lists so that they would display the related information only (do not know a better way of doing it).

  Quote

Also, the whole Field01, Field02, Field03 thing is a giant red flag indicating incorrect data structure.

Not getting that - not sure what fields you are talking about.

I will read-up on that paper.