Drill Down to other detail

[Guest]

If anyone has used Quicken or Quickbooks, one extremely helpful feature is to
drill down to the detail transactions when the report is previewed on the
screen. Is there any way to do this with Access? If not from a previewed
report perhaps from a query?

 

[Michel Walsh]

Hi,



Something like "Sales Analysis" form in Northwind?

It is a query PivotTable.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks for your response but I do not think a pivot table will work.
Basically what I have is a phone log. The fields I currently list on a report
are the date, name and code description. The code being the main reason they
called. What I don't list is the comments which is a memo field. This field
could be quite lengthy so I wanted the user to have the ability to click on
a record to see the comments rather than listing all the comments for every
call.

I will fool around with the pivot table concept but would there be another
way to do this besides using a pivot table?

 

[Michel Walsh]

Hi,


Ah, that can be done with a sub-from. The "main" form list the memo (and the
primary key field, but invisible), the sub-form list the same table, without
the memo field. Place the memo field, of the main form, below the subform.


Or do the reverse (as what is on the main and what is on the subform).


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

As you can tell from the delay in my responses I only work on this part-time
and I am still a novice at Access. Anyway, I am not sure of your solution of
using a form will work. My report is based on a parameter query, using the
Between option, which prompts for a beginning and end date. Once the fields
are populated, the report outputs to screen. Not every record has a comments
but the ones that do, the user may want to see what the comments are. A
couple of issues I want to clarify before trying your solution: 1) I don't
have a primary field, since we really don't have a need for one so can I use
a subform without a primary field. 2) Can I use a parameter query with a form?

Thanks for your help.

Alan

 

[Michel Walsh]

Hi,


You don't really need a primary key, but it makes your life easier. If you
don't have a primary key, you have to be able to "spot" a (one, 1) record
giving only VALUES for one or more of its fields. If this is impossible,
forget about the proposed solution since you need to "link" the main form to
its subform through the values in the fields (making a parent-child relation
with the form-subform). Here, the parent IS the child, but that does not
change the pattern: we need to be able to locate THE record relaying only on
fields values. So, a primary key is ideal, but if you can also do without
it, that is working too.


You can use a parameter query with a form, but it is not common to
explicitly prompt for parameters, in this case, but to use the syntax
FORMS!formName!ControlName, in the query, referring to another open form,
visible or not. Indeed, if the said form is open and the said control filled
with a value, Access will automatically get its "parameter value" from the
said controls, rather than explicitly prompting the user.


Hoping it may help,
Vanderghast, Access MVP