Joining Table

[#1]

Hi,

I have a joining question.

I have a sales & purchases table.

Each jobID in Sales could have multiple purchases.

Some Purchase invoices wont have any JobID because they are like utilities bills.

I need to calculate the number of Invoices, sum of VAT and lots more.

If I use Join method then for an Invoice that has two purchases, it double counts the invoice number and double sums the VAT. If I use the Concatenate then it doesn't link everything properly.

I also need to combine both dates so that I can link it to one calendar.

What's the best method of doing this?

Any help will be appreciated.

Regards,

[#2]

Hi Sarb,

please see attached, if you concatenate the two tables together and add a data type field to your final table, you can then use set analysis to count the correct type of data you want a total for. Still all via one date though

eg for Number of Invoices  Count({<Data_Type={'Invoice'}>} ID)

hope that helps

Joe