Table Joins 1

[MrArmstrong]

I have four tables that I have data in.
I have my 1Stock Inventory table
2Purchase hold table
3Purchase Request Table
4Purchase Order Table
i am linking the tables from #4 down , they each convert to the other. On the Purchase Order Table I link conv doc to Purchase request doc #,then i link conv doc # on Puchase request Purchase Hold document #, then I link Purchase hold table to inventory by part number. My problem is that I can see All orders that either started from inventory and went all the way through to a Purchase order or inventory items that have no action at all.

I need to know what kind or where do i fix my joins to show the few that Have a Purchase order and Purchase Request but didn't come from a Purchase hold document or part in the inventory.

 

[lbass]

Use left joins starting with the link FROM the Purchase Request to the Purchase Hold table, and from the Purchase Hold table to the Inventory table. Once you start using left joins, all tables to the right of the left join must also be left joined.

-LB

 

[MrArmstrong]

thanks I'll try it

 

[MrArmstrong]

Sorry for the delay I had to go back to the drawing board I noticed a lot of items were missing when I compared it to our database.
O.K. (---->=Linked)

The Purchase Order Line Partnumber--->Stock Table by Partnumber with Right Outer Join

The Puchase order Conv Line---->Purchase request Line
by Line Full Outer Join

The Purchase Request Line------>Purchase Hold Line by line Left Outer Join

O.K this seems to be working the best except it doesn't capture when a Line on the Purchase order is linked directly to the Purchase Hold Line. How can I capture those few that skip over the Purchase request.

 

[lbass]

Can't really help without knowing the fields you have available for linking on each table. Also, you appear to have changed your requirements from your first post. Why are you using a right outer from the inventory table?

-LB

 

[MrArmstrong]

It was missing several orders that didn't meet all three tables of criteria.I still have the old one this rework just seemed to produce more of what was missing.
it's kind of difficult, because I need Part numbers to show if they don't have any activity, if they have one of any of the three documents , and also to show if they have all of the documents.I am only get the ones that have all or none, finding those few that don't have all three connections if whats difficult.

 

[lbass]

But there are FOUR tables. Try showing some sample data that demonstrates the variation in results that you expect to see. Also please show the fields available for linking in each of the four tables.

Also please identify your CR version. It might be that for your purposes you would be better off using a command that does a union on the tables.

-LB

 

[MrArmstrong]

Version is Crystal reports XI

Field Names are:

PARTNUMBER/DESCRIPTION/O/H/MIN/MAX/Hold Doc#/QTY REQ/ADDdate/Pur Req Doc #/Qty ORD/P.O./QTYORD/QTYBK

1) 004-351-0/TIRE/1/0/1/2369/5/6-12-07/2563/5/4500/5/3

Example 1 shows a full buy from inventory all the way to P.O.

2)100-400-01/WINGBLAD/2/3/6/ / / /2563/5/4500/5/3

Example 2 shows a buy that has a Purchase request and a Purchase order, with no hold doc associated.

3)2-100-400-02/DRILL/2/3/6/ / / / / /4500/5/3

Example 3 shows what I get when there is a Hold document attached straight to P.O.


The top row would be all of the datafields that I am using. Part Number,Description,O/H,Min,Max, are all from the inventory table. The Pur Request Doc, Hold ,and Purchase Order all have Qty, Partnumber and Description Fields.( That I'm Looking For).


( I have the P.O. Table attached to the Purchase request Table because this is the norm. Every time I try to attach it to the Purchase hold table it says it is already linked)

I need to attach P.O. to both tables so that i can ask,show me the Purchase Req attached to this P.O or the Hold Doc attached to this P.O..
Sorry so lengthy, hope this is detailed enough.

 

[lbass]

You are not showing data for all the fields you mention, so I'm having trouble following your sample data and relating it to the headers. I think you only have data like this (I've substituted one digit or one letter for the first fields). Also can't tell what tables belong to what field.

I.PN I.DESC I.O I.H I.MIN I.MAX H.Hold Doc# QTY REQ ADDdate
1 A 1 0 1 2369 5 6-12-07 563 5 4500 5/3
2 B 2 3 6 2563 5 4500 5/3
3 C 2 3 6 4500 5/3

Please instead show the tables and fields like this and indicate which ones you want to be able to be null:

TableA
Field1 - null
Field2
Field3 - null

TableB
Field1
Field2
Field3

//etc., but using actual table and field names.

-LB

 

[MrArmstrong]

TableA-Stock/Inventory
Field1-Part Number
Field2-Description
Field3-O/H Quantity
Field4-Min Quantity
Field5-Max Quantity

TableB-Parts Requestor/Hold Table
Field1-Doc_No
Field2-Quantity_Req
Field3-Date_Requested

TableC-Purchase Requests
Field1-Doc_No
Field2-Quantity_Req

TableD-Purchase Orders
Field1-Doc_No
Field2-Quantity Order
Field3-Quantity Backorder

There is none that I want to be null, I just want it to show whatever is out. If there is a Purchase order with a hold Doc_No with a Request Doc number then I want it to show this being that the Purchase request should be blank and the Hold Doc and P.O. Doc shout show up.

 

[MrArmstrong]

FYI

TableA-Stock/Inventory
Field1-Part Number
Field2-Description
Field3-O/H Quantity
Field4-Min Quantity
Field5-Max Quantity

TableB-Parts Requestor/Hold Table
Field1-Doc_No
Field2-Quantity_Req
Field3-Date_Requested STEP 1 OF ORDER PROCESS

TableC-Purchase Requests
Field1-Doc_No
Field2-Quantity_Req STEP 2 OF ORDER PROCESS
(SOMETIMES SKIPPED OVER)

TableD-Purchase Orders
Field1-Doc_No
Field2-Quantity Order
Field3-Quantity Backorder STEP 3 OF ORDER PROCESS

 

[lbass]

I wasn't asking what you WANTED to be null, but what could be null in the results you wanted. You started out implying that there were always records in PO and Purch Request, but not necessarily in Inventory and Hold. I am confused about whether you only want records that are in certain tables, but maybe not in others, and if so which they are, or whether you want all records from all tables, regardless of matching records. Could you please clarify that?

-LB

 

[MrArmstrong]

I apologize,
I want all records from the Inventory Table.

I want all records from the Parts Hold Table.

I want all records from the Purchase Request Table that were converted from a Parts Hold Doc.

I want all records from the Purchase Order Table that were converted from either a Purchase hold Doc or Purchase Request.

 

[lbass]

A few more questions. I don't see any fields in the inventory table for linking with the other tables. Does it have fields that match any of the other tables? What are they?

Are all records in the parts hold table also represented in the Inventory table?

-LB

 

[MrArmstrong]

yES THE PART NUMBER AND DESCRIPTION ARE ALL FROM THE INVENTORY.MY MAIN PURPOSE FOR THE INVENTORY LINK IS SO THAT WHEN ALL DATA IS CORRECT , I CAN USE SELECT EXPERT TO SHOW ME ONLY THOSE ORDERS THAT ARE FOR STOCKED PARTS.

YES EACK PART HOLD DOC IS CREATED FROM WITHIN OUR INVENTORY SCREEN.

 

[lbass]

Ok, please don't yell. You didn't really answer about what fields in inventory are available for linking, and I'm not sure what that should be. So try this linking set up:

Inventory--left join to-->Hold---left join---> Purch Request
| |
left join left join
| |
| |
V V
PO PO_1

You have to add PO twice in order to capture the two PO situations. If this still doesn't meet your needs, please explain what it doesn't do.

The other approach you could take (which was what I was trying to determine the need for) is to create a command that does a series of unions between the tables. Then you could get all records from all tables.

-LB

 

[MrArmstrong]

The only field I can think of in Inventory for linking would be Part Number. On every document the partnumber has to be in inventory first.

I'll try it , sorry for the long delay , I have had a bunch of meetings today. I will get back to you on this outcome. Hope you enjoyed your holiday.

 

[MrArmstrong]

OK, i got a question before I can see if it works. Right now one of my fields on my report is the P.O. that is attached to the Purchase Request. Now, without having 2 P.O. columns how can I show which ever P.O. needs to show.Example.

Inventory/Hold Doc/Purchase Request/P.O.doc #
this has a P.O. number if it was a match for Purchase Request and PO_Table

Inventory/Hold Doc/Purchase Request/........ It is blank if the match was in Hold and PO_1 table.

Do I need two P.O. Doc# columns.

 

[MrArmstrong]

Sorry I'm filling this thing up so quickly, let me know if this is O.K. I went ahead and put PO_1 over the PO_ column and it seemed to fill in the gaps with out overlapping, is this O.K.

 

[lbass]

You could try a formula like:

if isnull({PO_1.PO_Doc}) then
{PO.PO_Doc} else
if isnull({PO.PO_Doc}) then
{PO_1.PO_Doc} else
{PO.PO_Doc}

-LB