asked on

DBA Manufacturing SQL Firebird Database Join

I am using a Firebird Database called DBA Manufacturing. I am working on a query to do an "ABC" classification of our purchased and manufactured parts. I need to join two tables in the query among other things to accomplish this. I know I can join them using the UNIQFORREC column but I have tried many different syntax and combinations of SQL statements without good results. I have no experience with SQL but I have managed to extract some data out and organize it in Excel and Access.

The two tables have the following columns:
Inventory Transactions - Table 1
Item ID                                     ITEMH                  ITEMCODE
Transaction Date                         ITEMH                  DAT_
Quantity                               ITEMH                  QTY
Location                               ITEMH                  ITEMLOC
Cost                                     ITEMH                  COST
Transaction Type                         ITEMH                  TYPES
Reference                               ITEMH                  REFERENCE
Characteristic Qty                   ITEMH                  CHARQTY

System Default Characteristics
Serial Number                               ITEMH                  SERIAL
Lot Number                               ITEMH                  LOT
Expiration                               ITEMH                  EXPIRATION

Internal Table Links
Transaction Unique ID                         ITEMH                  ITEMHUNO
Receipt Transaction ID                         ITEMH                  UNIQUEFORREC (Link to POREC)

PO Receipts - Table 2
PO Number                               POREC                  PONUM
Receipt Date                               POREC                  RDATE
PO Receipt Header Note             POREC                  NOTE
Document No                         POREC                  DOCREF
Qty Received                         POREC                  QTYR
Reference ID                               POREC                  REFERENCE
Location                               POREC                  LOCATION
Cost                                     POREC                  PRICE
Foreign Cost                               POREC                  FXPRICE
Batch No                               POREC                  PORECEIPTNO

Internal Table Links
Receipt line ID                               POREC                  UNIQU_
Receipt Transaction ID                        POREC                  UNIQFORREC (Link to ITEMH)
Multiple lines per batch receipt                   POREC                  PORECEIPTNO

Mike Eghtebas

re> I am working on a query to do an "ABC" classification of our purchased and manufactured parts.
Q1: What does this mean?

re:> I need to join two tables
Q2: what are the name of these two tables?

re:. among other things to accomplish this
Q3: What do you mean by among other things?

re: managed to extract some data out and organize
Q4: Could you provide mock/sample data like:

Serial Number Receipt Date Document No Location Cost
abc12345 7/1/2015 doc345 LA 120
xyz12345 7/3/2015 doc346 SF 110
etc.

Also see Top 10 Ways to Ask Better Questions to get quick and accurate solutions.

Robert Mandrell

ASKER

Q1: An ABC classification divides inventory into three groups; A, B and C. The A group represents the top 80% of the value of the inventory which is commonly 20% of the items, the B group represents the next 15% of the value which is commonly 30% of the items and the C group is the last 5% of the value and is commonly about 50% of the items. The classification is used to determine cycle counting plans, order quantities and safety stock levels.

Q2: Inventory Transactions (ITEMH) and PO Receipts (POREC)

Q3: I need columns out of both of these tables to retrieve the PO purchases, particularly the quantity (QTYR) and the cost (PRICE) between the present date and the date one year prior to the present date. Then I need to sum them for that period and order the result by Item ID (ITEMCODE). Finally, I need to identify the value group and add a column to identify them or group them by the classification.

Q4: I have some tables that I exported to Excel from DBA Mfg and then imported into Access using SQL query. I am just learning how to do this so it was very basic. This data came from the Item Master table.

ITEMCODE      DESCRIPT        UOM
105-P1160S      1" X 23`6" SCH160 SMLS PIPE (.250 WALL)      Ft
10-6K045      MOTOR, 1 HP, 56HC        Ea

Nick Upson

for example

select a.column1, b.column2 from tablea a join tableb b on a.column4 = b.column4

to this you can add

select a.column1, b.column2, sum (a.column7) from tablea a join tableb b on a.column4 = b.column4 group by 1, 2 where a.column12 > '01-01-2014'

Robert Mandrell

ASKER

My original question is answered. I ran the query:

SELECT ITEMH.ITEMCODE, PORECS.QTYR, PORECS.PRICE
FROM ITEMH JOIN PORECS ON ITEMH.UNIQFORREC = PORECS.UNIQFORREC

and it returned good results. The table name that I copied out of the DBA Mfg "Data Dictionary" was not correct - so POREC should have been PORECS. That didn't help but I also had the syntax wrong before I got the answer from Nick Upson.

So should I mark this as answered and start a new question? If I need to do that please tell me and I will.

After the join ran successfully I added the " WHERE PORECS.RDATE > ' 7/10/2014' " at the end of the statement and it also ran successfully. Using your guidance and some trial and error I now have the following query:

SELECT ITEMH.ITEMCODE, PORECS.QTYR, PORECS.PRICE, QTYR * PRICE AS TTLYRLYPRC
FROM ITEMH JOIN PORECS ON ITEMH.UNIQFORREC = PORECS.UNIQFORREC WHERE PORECS.RDATE > ' 7/10/2014' GROUP BY ITEMCODE, QTYR, PRICE

That gets the results shown in the screen shot file attached. So now I have each part number from the past year grouped by part number and the price and quantity have been expanded in the TTLYRLYPRC column. I need to sum the orders to give me a yearly investment per part number. I will be trying to do this but at this moment I really don't have a clue other than I know I need to use "SUM".
QR-SS-07122015.docx

ASKER CERTIFIED SOLUTION

Nick Upson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Mandrell

ASKER

Thanks for all the help Nick. I see how that works now but it will be a long time before I get comfortable with this.

This will help me move on with my current project.