I have posted this query before, but I didn't explain myself very well last time.
I have a problem with duplications in a report I am producing. After a bit of investigative work I now know my issue is to do with linking / joining tables, but I just can't quite get what I need.
All I need to do is link the tables in such a way that will associate a billing cycle correctly to the person & date without getting the duplications. Joining tables is blowing my mind, no matter how many times I try to get it.
I have attached a file to demonstrate what I need. Please note there are more than 2 fields in each of my real tables, the attached is for demo.
Any help appreciated.
I think, the problem with duplicates is primarily arising because of the BillingCycleDates table.
You are using BillingCycleName from this table in your chart, the join for this table results in multiple cycle names for the same date. Do you need this table for analysis?
If you comment out this table and in its place use the CycleName column present in BillingCycle table.
You should get the desired records..
I do need this table as I will be using the other field dates within it to determine which one of the billing cycles is the latest billing date and publish date.
I realise this is the table that is causing the grief, but I do need it.
As mentioned earlier, the BillingCycleName field in the BillingCycleDates table results in multiple cycle names for the same date, as there are more than one BillingCycleName entries for any given date.
You can keep this table for determining which one of the billing cycles is latest, however while using fields in charts, you should use it independent of the other tables, i.e. for the chart that you have in the sample you should not use fields from this table, in place of BillingCycleName use the CycleName column present in BillingCycle table.
For charts where you plan to use the Fields in BillingCycleDates table, use only fields from this table, as they are not uniquely related to the other tables, and using fields from other tables will result in duplicate rows.
Michael ... OK. Uploaded.
In answer to KD. I do understand, however I will be using both in a chart, as the report needs to show if it is published billing based on the date and the division they are in. The report will be generated to be sent to all divisions based on their own division's balance as at the latest billing date, so doing this doesn't work.
I did previously manage to do this in a chart however the calculations caused 'out of virtual memory'
Thanks, it would be helpful, if you can also provide a snapshot of the expected outcome you would like to achieve, where you would be using data from both sets of tables..
I don't fully understand what you have done (a bit of a novice), however despite it doesn't seem to have worked as there this there is no data for coming through from the RosterCycleDates table. i.e. if I add the publish date, they are all blank.
I feel like I am missing something really simple here. Can this not be done by simply joining the tables in the correct way?