I've got three tables, 1 is a lookup table with a list of Descriptions, the second table is a daily cost table storing the values for the items in the descriptions table and the third is a table for jobs. They look like this:
BudgetItems: Daily Costs Jobs
ID int PK ID int PK ID int PK
Description varchar(50) BudgetID int FK -> BudgetItems.ID JobName varchar(50)
JobID int FK -> Jobs.ID
Cost numeric 9,2
I need a query that will produce a list of ALL budgetItems for a particular Day from DailyCosts even if there is no value for that particular Item from BudgetItems.
For example: For a particular day:
Item DailyCost TotalAcrossAllDays
Labor 123.25 1003.32
Steel 500.00 500.00
Cross Beams NULL 10,259.36
Footings NULL 3200.25
Pilings 300.00 6952.36
I need a query that will produce that same list of ALL budgetItems with/without the cost for a particular day with a total value for that particular item. For example, Labor would be a Item in BudgetItems and there'd be entries in DailyCosts that pertain to Labor. Need to be able to total the value for Labor at the same time the entire list is produced for that particular day. So the user should be able to select a day, get the value for the labor entered that day and in the next column have the total labour across all days.