one to many linking

[azwaan]

i have a report which summarises content in my employee database.i am using 6 tables
Employee ,Dept,Section,Category,Attendance and Budjet

the problem is when i include a field from the budjet table the summaries the report generates is incorrect.

i have tried using formulas and running totals too, but still the problem persists.

i think this has something to do with the structure of the budjet table, it is as follows,
Year,Category,Dept,Jan,Feb...Dec

the key is composed of year,Dept and category cos budgeting needs to be done for every dept and for each dept every category (Staff,Exec etc)

when the budget table is not included the report reads 1070 records (The correct figure) , but when the budjet table is included it gives 1354.

the link is as follows
employee.employeecode -> attendance.employeecode
employee.sectioncode - > section.sectioncode
employee.deptcode -> dept.deptcode
employee.deptcode - > budjet.deptcode
employee.categorycode - > category.categorycode
employee.categorycode - > budjet.categorycode


the report is grouped by employee.deptcode meaning..there will be atleast 3 records for each dept in a year. because there are atleast 3 categories.

how do i get abt doing this?

 

[Chasidy]

Could it be because you are linking to the budjet table twice (once on deptcode and once on categorycode)? Maybe using an alias table would be a better alternative?

 

[GJParker]

Ther is no need to link employee.categorycode to budjet.categorycode by doing this you are creating a circular refernec to the budjet table.

You only need to bring your monthly budget figures in once for each year/Dept/category you can then summarise the figures within crystal at Year and dept group level.

Hope this helps.

Gary Parker
Systems Support Analyst