Creating Joins from multiple tables


#1

We have a created multiple tables from our DB and we are trying to create joins for reporting.

We have each of the following data points in their own table.

Date
Location
Vehicle
Dealer

We need to match the records and add them to another table all on one row for reporting purposes. Is this possible?


#2

It sounds to me like the table you have labeled as “Date” would be your join table. What you’re interested in is logging your sales and to manage inventory, presumably.

So a date is only interesting to you in terms of its relation to a sale. So I’d change the name of that table to “Sales” or some-such, and have a “Date” field in that table. Then you’d create fields that are “Links to another record”, and link them, one to “Vehicle”, one to “Location”, and one to “Dealer”. So now each “Sale” record took place on a certain date, relates to the sale of a certain “Vehicle”, from a certain “Location”, and by a certain “Dealer”.

Then, in your “Vehicles” table, you could have a formula field that checks if a vehicle is sold or not by looking for a record in field that links to “Sales”.

IF(
   Sale,
   "Sold",
   "In Stock"
)

If the vehicle has a “Sale” record linked to it, then it will be marked as “Sold” in that field, otherwise, it will be marked as “In Stock” in that field. Then, you’ll be able to filter your view of the “Vehicles” table to show only vehicles that have “In Stock” in that field. And you could create a separate view that shows only “Vehicles” that have “Sold” in that field to keep an “archive” of sorts of your sold vehicles.