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
Robert MandrellAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
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 MandrellAuthor Commented:
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 UpsonPrincipal Operations EngineerCommented:
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'
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Robert MandrellAuthor Commented:
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
Nick UpsonPrincipal Operations EngineerCommented:
I don't think you need "GROUP BY ITEMCODE, QTYR, PRICE" above just "GROUP BY ITEMCODE" seems to fit your description. if so just add "sum(QTYR * PRICE)" to the select list if not I think you will need to do this in a second query (there may be other ways but they get complicated)

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Robert MandrellAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.