Referencing queries

  • Thread starter Thread starter sundergj
  • Start date Start date
S

sundergj

First question here but I think I am well on my way with this project!

I have a deal logging database that tracks which people are working on
what deals (many to many joined by a junction. Within the deals, there
are 2 main types.

I want the report to show the names of the team-members and then each
of the deals they are working on. However, I want the deals of type X
to be shown in the first column, and then deals of type Y to be in the
next column (and so on) In the table, there is a column for deal name,
and then another column that identifies what type of deal it is.

I initially tried to create queries for each type of deal that just
showed all the same data as the master table (that I would ordinarily
run the report off) but I just added a sort in each (=X; =Y, etc,).
However, I found that after creating a report which pulled each of the
deal names from their respective "sorted by type" queries, the report
would only list team members who had a deal of both types. I want to
show every team member regardless of deal staffing, and then show
whatever they might be working on.


Really appreciate your insight

Thanks
 
Make a main report based on team member

~~~~~~~~~~~~~~~

make a (sub)report based on team deals.

use that same subreport twice in your main report -- in the detail
section -- one subreport for each column, Make the height small and
allow it to grow.

make two calculated controls on your main report for the parent link of
deal type

ie:
Name --> DealTypeX
Controlsource --> ="X"

Name --> DealTypeY
Controlsource --> ="Y"

link subreport by team member ID and deal type:

Name --> Sub1
LinkMasterFields --> MemberID; DealtTypeX
LinkChildFields --> MemberID; DealtType

Name --> Sub2
LinkMasterFields --> MemberID; DealtTypeY
LinkChildFields --> MemberID; DealtType

make sure the controls for master and child are on each repective
report/subreport

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Make a main report based on team member

~~~~~~~~~~~~~~~

make a (sub)report based on team deals.

use that same subreport twice in your main report -- in the detail
section -- one subreport for each column, Make the height small and
allow it to grow.

make two calculated controls on your main report for the parent link of
deal type

ie:
Name --> DealTypeX
Controlsource --> ="X"

Name --> DealTypeY
Controlsource --> ="Y"

link subreport by team member ID and deal type:

Name --> Sub1
LinkMasterFields --> MemberID; DealtTypeX
LinkChildFields --> MemberID; DealtType

Name --> Sub2
LinkMasterFields --> MemberID; DealtTypeY
LinkChildFields --> MemberID; DealtType

make sure the controls for master and child are on each repective
report/subreport

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Sorry for the delay in response, and thanks for your initial feedback.

I'm unclear as to how a subreport can be used in a main report? I did
not realize a report could be a source for another report.

What are calculated controls?

Finally, in creating a form to access these tables, I have found that
when I change the name in the form to access the deals of a different
person - the relevant deals appear, but the program doesn't allow me to
save ("The changes you have made cannot be saved because they would
create a duplicate entry in the data" - cascading entries)

Any insight on a solution to this?

Regards


Sunder
 
Hi Sunder,

"Any insight on a solution to this?"

yes, but you need to see it too...

send me an email and request my 30-page Word document on Access Basics
(for Programming) -- it doesn't cover VBA, but prepares you for it
because it covers essentials in Access.

Be sure to put at least "Access Basics" in the subject line so that I
see your message...

Then, once you have a better foundation, we can discuss how to implement
the solution you desire :)

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Back
Top