Table Design - Junction Table? (1 Viewer)

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!!

...and the room numbers (R01-R18) as fields

Click to expand...

That's incorrect (so is your labor and material quotes by room). You need to read up on normalization (https://en.wikipedia.org/wiki/Database_normalization) and structure your tables properly.

Tables shouldn't accomodate data horizontally (with more columns), but vertically (with more rows). So you shouldn't have a field for each possible room, instead you should have a table and you add a row for each room.

Again, I see other issues, you really need to structure your tables properly for a database, not as if this was a big spreadsheet.

KelliN,

Some of the links in this post will help with database design concepts or serve as a reference.

Good luck.

Thanks plog for the reply. After making my post, I thought more about what I'd read re. the vertical vs horizontal structure and I think I may have come up with a solution - and yes, it will be changing the table with room numbers for fields. I will post again once I've tested the theory...and I will definitely read the information you've provided via the link.

Thanks - I'll check it out.