Query issue

[ashu.doc]

Hi everyone
I am trying to make a report from a query. The query which I have made contains several tables, the main table which is personaldetails is related to 5 other tables each by one-many relationship. So if I am using one main and one other table only in query then the query is populating data, but if I am using more than one other table in query, the list of data in query is not populating. I don't understand why it is happening?
What can I do then to get all the data in one query because I need all 5 other tables to show in my report/query relating with main table?

 

[Alansidman]

Without seeing the query it is difficult to determine the cause. Please post your SQL statement or upload your db with confidential data removed or dummied up. Run a compact and repair before uploading.

 

[ashu.doc]

Ok, the query in question is Portfolio and main table is Personal details, and other tables are Conferences, courses, publications, presentations, and research.
View attachment Surgi-log 1.0.zip
thanks

 

[mahenkj2]

Double click on each join line and select 2 Include all data from personel details.
then first input some data in table personel details. Should work.

 

[ashu.doc]

View attachment Surgi-log 1.0.zip

Hi there
I have attached the application after doing what you have said. When I am running that query there is a problem, that if I add more than one set of data in one table eg. in conferences - if I have added 3 conference details and if I have added only one set of data in other tables, the other tables data is triplicating in the list.
So the report created on the basis of this query is getting same problem, the report called Build CV. It does not look even near to any CV
please help.
Thanks

 

[mahenkj2]

If a person has attended three conferences (You fill three records in conference table) then when you are going to make a portfolio query, it should result that one person has attended three conferences and three records shall be generated. Since, records are having some differences like date started or other fields, so even if you group, it will give you same result. I think it is no wrong but it is what it should be.

If you intend to show result in some other way, present some examples.

regards.

 

[ashu.doc]

Hi
What I am trying to show on the protfolio is something like this:

Surgeon details
Name DOB
Medical college Name of hopital

------------------------------------------------------------------

Conferences
Date started Date ended Name of conference Type
1.....
2.....
3.....
4......

Courses
Date started Date ended Name of course Type
1.....
2...
3....

Publications
Date published Type Journal
1....
2....
3.....
4....
5....

Presentations
Date presented venue
1....
2,....

and so on.

The report which is coming out in my application at the moment, is showing each set of conference, course, presentation etc separately not in group like I have shown above. That looks more like portfolio/CV.

Thanks

 

[mahenkj2]

So, in this case, query output is OK, you just need to arrange the report. for this, I feel you should use group and sort. In your report, you have used sorting, try to play with group as well.

 

[ashu.doc]

yeah I have tried a lot to play with grouping, if you see the other report operation list I have arranged well but this one is nightmare, I have been trying to sort out the grouping only since yesterday but its not working by any means.
I had designed the operation list in access 2003 which was much easier but now I am using 2007, I dont know how can I deal with it.

 

[RainLover]

It might be time to look at a series of Sub Reports. It would help make the formating of each group easier.

 

[ashu.doc]

I thought about it earlier, and I also think thats the way ahead.
So what do you suggest? Shall I make 5 different queries , each of Surgeon detail and one other table like conference, publication etc and reports of each query and then make subreport in one main report?

 

[RainLover]

I think you have 4 Sub Tables so a query for each would be the go. They all have a Foreign Key to the Main table so you have a link. Sounds good to me.

The best thing is that you could have all Sub Reports with the same appearance or they could be totally different.

Sing out if you need help.

 

[ashu.doc]

Its working ..)