Totals items from two databases

[wileyboy]

I have two tables in my databse

Table # 1 Item | PriceK | PriceM |Price H | Price T
This is populated with items and four prices, these prices are based on a group that the user belongs to(k,m,h, or t)
Table #2 Username | Group | Dec3 | Dec4 | Dec5 | Total
In Dec3 Dec4 etc will be an item that corresponds to the items in table#1.

I need to take the item in Dec3 and retrieve the price based on the group that the user is in and add it to the same for Dec4 and Dec5 and place that total in Table #2 Total.

I can do this by redoing the tables or by having several submits and using a seperate table , but Id rather it be done right. If you can help Id greatly appreciate it.

[dreaken667]

Honestly table #1 (and possibly #2) should be redesigned a bit in my opinion. Something like this would be more beneficial for table #1:

Item | Price | Group

Then you could get the total a lot easier by joining the two tables on the item and group columns and then making a cross-tab selection from the result using your date columns. I'd be more helpful and provide an example of such a query, but I don't know what type of database you have.

A slight change to table #2 can futher simplify things:

Username | Group | Date | Total

This would make the cross-table selection unecessary as it could then be replaced by a group by clause using the Date column instead. Again, the syntax will be different based on the database in question or I'd provide an example.