One to Many table relationship

[ericb1]

I'm using CR10 and MS Access db.

I have 2 tables, one is a list of users, the other is a list of software they have installed, so there's a 1 to many relationship where there's 1 user in the table but the software table has the same user listed multiple times.

My report is grouped on the user name in the user table, but whenever I try to add the software from the software table, it lists the entire table, not just for that user. I have the tables linked on user name.

Any help is appreciated, I want to just list the software for each user in the detail of the report. Not sure if I need a separate subreport? Any help is appreciated, thanks!

 

[synapsevampire]

Are you certain that the user field is linked? And is that the right column to use?

You might simplify things if you understand Access well enough by creating the Query in Access and then using the query as the data souce for the report.

It sounds like you're doing it correctly, providing you understand the fields to join correctly.

-k

 

[kskid]

Have you check Database->Select Distinct Records

If you have an inner join on userid between the tables and grouping by userid, then the detail section should only show the software for that userid. You must have other tables involved that is causing a row inflation.

Also, I could use a bit more clarification on what "user" means.

Is this "user" really a PC so I have a history of what is installed on each PC? How do you handle reinstallation of the software? Is that aadditional hit?

Is the user restricted to only one PC or is he/she like a Help Desk person that installs software on any or all PC's?

-LW

 

[smcnulty2000]

Silly question,

Why don't you just use the one table with the software, and group on the 'username'?

Normally this wouldn't be the solution, but since you describe the username as existing in that table it might be an okay choice.

As for why Access is giving you every record...Did you join the two inside of Crystal or inside of Access? If you did the join inside of Access then Crystal won't be aware of it.

Hope some of this helps.

Scottm.

 

[ericb1]

I should be clearer: It only shows the software for that user, but it creates a new detail section/ or new page for each record in the software table. So if there's 6 software records for a user, it creates 6 pages/ or 6 detail sections (depending on how I have it formatted).

I'm trying to have it list the 6 records in the same detail section, right in a little list. That's why I thought/think I need a sub-report in the details section, or a formula to loop through these records and list them in the same detail section?

Any help is greatly appreciated, thanks!

 

[Madawc]

Just what sort of link do you have between the two tables? If the link is name-to-name, then only records for that name should appear - unless the link is something other than 'equals'. Check the links under Database Expert. Or post the SQL code, Database>Show SQL Query

Madawc Williams (East Anglia, UK)

 

[ericb1]

I've got the linking right, as only those records for that name are appearing. It's that, for each record, they're creating a new detail section or new page for each one.

So if my report has:

Name
phone
dept
software

Where now there's multiple records for software, it's generating a new detail section for each software record, it's reprinting the name, phone, and dept. for each software record.

I'd like it to just loop through and list the software inside the detail section of everything else.

 

[lbass]

You should have only one group, on user. Then make sure your name, department fields, etc. are in the group header. Then go to format->section->details and make sure that you do not have "new page after" or "print at bottom of page" checked.

-LB

 

[ericb1]

Thanks everyone. I added a sub-report linked by the username in both tables, and this seems to work fine. Thanks again!