I am building a database for a client, bringing in data from Excel job costing sheets for his quotes (he is a painter/decorator). Each job has it's own Excel file with labour and material quotes by room, then subtotals, VAT, and totals for 0% discount, 5% discount and 10% discount. Each quote can have one or more rooms - up to 18 - which are numbered from R01 to R18 with a description (e.g. lounge, kitchen, bedroom, etc.).
I currently have one table in the database with the list of possible room descriptions (lounge, kitchen, etc.), and another table for the actual jobs with the job number (this is a key linking field for the database) and the room numbers (R01-R18) as fields. For each job to be done, we will enter the job number and the room description(s) manually into the latter table, using a list box that links back to the room description list table.
Next I have a table into which I will bring the labour and material quotes by room. The fields are Job Number, Room No, Labour, Materials, Labour Hrs, Room Total and Client. Based on this table, I've created a cross-tab queries to summarize the quote amounts by job number & client (row) and room (column) - one query for labour, one for material, and one for room total.
Ultimately this data will go into a report that details the job number, and the profit/loss by room, so I need to show room number/description, quoted amount, actual amount, variance.
Although a job could potentially have up to 18 rooms, the majority don't - in fact, most are 6 or fewer. So I would prefer not to display the room columns (description, quote or actual) when there is no data in them, but I'm baffled on how to suppress that data - although I think a junction table might be the answer. I'm just not sure how to do that with the need to link both the job number and the room number. I've done some online research and haven't seen any examples using data structures like this.
Sorry for the long post, but hopefully the background info is helpful. I'm open to suggestions - and happy to provide more info if that would help. Thanks in advance for any thoughts/ideas/suggestions!!
I currently have one table in the database with the list of possible room descriptions (lounge, kitchen, etc.), and another table for the actual jobs with the job number (this is a key linking field for the database) and the room numbers (R01-R18) as fields. For each job to be done, we will enter the job number and the room description(s) manually into the latter table, using a list box that links back to the room description list table.
Next I have a table into which I will bring the labour and material quotes by room. The fields are Job Number, Room No, Labour, Materials, Labour Hrs, Room Total and Client. Based on this table, I've created a cross-tab queries to summarize the quote amounts by job number & client (row) and room (column) - one query for labour, one for material, and one for room total.
Ultimately this data will go into a report that details the job number, and the profit/loss by room, so I need to show room number/description, quoted amount, actual amount, variance.
Although a job could potentially have up to 18 rooms, the majority don't - in fact, most are 6 or fewer. So I would prefer not to display the room columns (description, quote or actual) when there is no data in them, but I'm baffled on how to suppress that data - although I think a junction table might be the answer. I'm just not sure how to do that with the need to link both the job number and the room number. I've done some online research and haven't seen any examples using data structures like this.
Sorry for the long post, but hopefully the background info is helpful. I'm open to suggestions - and happy to provide more info if that would help. Thanks in advance for any thoughts/ideas/suggestions!!