Relationships & Database Design

[minchazo]

Using Access 2003, I've built a simple database with some tables that are related to each other. I'm pretty new at using Access, so I've just discovered that when you have a one-to-many relationship, you can drill down to a related table by hitting a plus sign at the left of the record. (e.g. 'PickList' -> 'PickList Item').

Is there any way to duplicate this format on a report?

'PickList Item' needs to have a calculated column 'tblSupplies.Cost*tblPickListItem.Number Open'. I have the relationship between them as a many-to-one 'tblPickListItem'-to-'tblSupplies' so I can't drill down to the cost from table 'tblPickListItem'

[jeffstl]

Using Access 2003, I've built a simple database with some tables that are related to each other. I'm pretty new at using Access, so I've just discovered that when you have a one-to-many relationship, you can drill down to a related table by hitting a plus sign at the left of the record. (e.g. 'PickList' -> 'PickList Item').

Is there any way to duplicate this format on a report?

'PickList Item' needs to have a calculated column 'tblSupplies.Cost*tblPickListItem.Number Open'. I have the relationship between them as a many-to-one 'tblPickListItem'-to-'tblSupplies' so I can't drill down to the cost from table 'tblPickListItem'

The reason relationships are built between tables is so that SQL can pull all related data with a single query.

What you describe happening in Access is a feature more then a "format" and its something that applications are typically built to feature rather then reports. (An application is an interactive report basically :)

However, you can get your report to display what you need using an SQL query tied to your report something like this:

SELECT (tblSupplies.Cost*tblPickListItem.Number) as MyNumber FROM MyTable, MySecondTable where tblPickListItem.Name = '" & MyParameter & "'
 

[minchazo]

The reason relationships are built between tables is so that SQL can pull all related data with a single query.

What you describe happening in Access is a feature more then a "format" and its something that applications are typically built to feature rather then reports. (An application is an interactive report basically :)

jeffstl,

Thanks! Your response got me thinking, and I was able to create something similar to what I wanted. I created a form with three listboxes that together specify one PickList. With a subform, I display the PickListItems that are related to that one PickList. Since it's in a form, I can easily add the calculated field. Using Listboxes instead of comboboxes let the users see what other options are available at each level of comparison.

I've been working on this all afternoon and it appears to work well. The only issue I can see is that users will have to choose their three criteria in series, but I don't see a way around that, except to create new forms.

minchazo

[NeoPa]

...Is there any way to duplicate this format on a report?...

Linked tables are indeed easy to create queries on.

Any report can run from such a query as easily as from a table, so the simple answer is "Yes there is".