Create a report with comprehensive data from two tables

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered

I have two tables--one called Orders and one called Forecast. The orders table contains actual orders received and the Forecast table contains data that was entered previously forecasting sales for the time period (week). Each table contains a Customer Field, an Item field and a Qty field (among others). I want to generate a report that lists the Item, Qty Forecasted and Qty Ordered for a given time period so I can compare the numbers. Sometimes there will be Orders that have not forecasted and also forecasted entries that never get ordered. (The purpose of this report is to gauge how well our forecasting is). The report should include a combined list of all items in the Order and Forecast tables. I am trying to avoid making an intermediate table with a master list of all items that needs to be maintained because the Items are so dynamic (ever changing).

Photo of Greg

Greg

  • 0 Points

Posted 5 years ago

  • 0
  • 1
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
Hi Greg,

I'm in a similar situation and am contemplating pulling my information as lookup fields into one of the two tables and then always running reports from that table since it will contain ALL the information along with my parent data that links the info. For me, my tables are pretty linear, you need to fill out a forecast sheet before an order can be placed, but I'm still torn on which table to set up all the lookup fields. Did you ever come up with a solution? And is it working? Thanks!
Photo of Eric

Eric

  • 40 Points
What you've described is known as a JOIN statement from databases like SQL. Please see this for more details: http://www.w3schools.com/sql/sql_join.asp. Unfortunately QuickBase doesn't have this ability. If you have a relationship between your two tables however I recommend creating lookup fields within it as outlined here: http://www.quickbase.com/user-assistance/default.html#create_lookup_field.html. Once these fields are on your child table, you may create a report which contains details from your two tables.
Photo of Greg

Greg

  • 0 Points
I ended up having to manage an items table that has all of the products.  It would be nice if there was some sort of "join" functionality.