By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,375 Members | 3,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,375 IT Pros & Developers. It's quick & easy.

Relationships & Database Design

P: 13
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'
May 13 '08 #1
Share this Question
Share on Google+
3 Replies


jeffstl
Expert 100+
P: 432
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT (tblSupplies.Cost*tblPickListItem.Number) as MyNumber FROM MyTable, MySecondTable where tblPickListItem.Name = '" & MyParameter & "'
  2.  
May 13 '08 #2

P: 13
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
May 13 '08 #3

NeoPa
Expert Mod 15k+
P: 31,494
...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".
May 14 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.