Relational vs Flat

[Karen]

I'm trying to develop a db whose primary purpose will be saving values for a report to be printed.

In the vein of being a relational devotee I have a Report Table, an Agency Table, an Agent Table. The Agent table saves the primary key of the Agency Table. On the Report form, I have a combobox with the Agencies and their indices; when an Agency is selected, another combobox is populated with the Agents from that Agency. The user selects an Agent and address and phone info from the Agent table is displayed. So in the structure is something like:

Report table
Report ID
fkAgencyID
fkAgentID

Agent Table
AgentID
fkAgencyID

In order to display the Agent info I was using DLookup but then I have to use DLookup on the form's current event otherwise nothing is displayed. I can get around this by showing the Agent data in a subform but that means I'll have lots of subforms on my Report form.

I'm beginning to think it would be better to save the Agent data in the Report Table, not just the key but that feels like one huge table----flat db instead of relational db. At the same time aligning the subforms is really a hassle. Do I take the high road and stick to the relational db or just give in and store the various fields in the Report table instead of the foreign keys.

 

[Tim Ferguson]

In the vein of being a relational devotee I have a Report Table, an
Agency Table, an Agent Table.
....

In order to display the Agent info I was using DLookup

Why not just a join? Base the form on a query that joins the Report to the
Agent to the Agency. Include whatever fields you want -- but you need to
think quite carefully about which version of the PK/ FK pairs you use.

Do I take the high road and stick to
the relational db or just give in and store the various fields in the
Report table instead of the foreign keys.

It's nothing to do with high roads or low roads; it's about business cases.
R theory is about being able to guarantee the correctness of the data. If
this is more important than development time, then follow that route. On
the other hand, if getting it up and running takes priority over the fact
that Agent A may have different addresses in two different Reports, then
you need a different development route.

Hope that helps


Tim F

 

[John Vinson]

I'm beginning to think it would be better to save the Agent data in the Report Table, not just the key but that feels like one huge table----flat db instead of relational db.

If you assume that you must have all the data in one table in order to
generate a Report, you would need to do this.

THAT ASSUMPTION IS INCORRECT!

Base your Report on a Query joining your Report table (with ID's in
it) to the related tables; pull the report data from the report table,
and the names from the name table.

 

[Karen]

Tim,

Thanks for the ideas. I'm leaning towards saving the values in the Report
table because I could preserve the values at the time of the report that
way. We'll see.

Karen

 

[Karen]

John,

I hate to say it but the query route just seems like a monster to me. I use
VBA and SQL a lot to build queries programmatically. In this report, for
instance, I have what amounts to five sections that draw from the same two
tables. In each section, one combo box chooses an Agency and that choice
defines what Agents will be available in an associated combo box then
depending on the Agent choice 10 other text boxes are populated.

I can build a query with query builder to pull all of the data from the two
tables but I still have to have a way to refine the data set depending on
five sets of combo boxes.

I literally just can't get my arms around how to do that with a source
query; I know how to do it programmatically. I'm still very much a newbie
and self-taught with the considerable help of some books and, thank
goodness, these newsgroups.

Anyway, am I making any sense? Is there some fundamental program or query
device that I could use?

Karen

Report Table, not just the key but that feels like one huge table----flat db
instead of relational db.

 

[RSGinCA]

I've been reading your posts and I'm confused about what you're trying to do.
You talk about printing a report, but you also talk about a form and combo
boxes and displaying agent data on that form. And you talk as if you have a
report that has combo boxes.

What is the purpose of the form? Is it merely a mechanism to allow the user to
select the agency/agents that are going to be on the printed report? And what
is the significance of the five sections?

Rick

 

[Karen]

Rick,

Indeed, the form collects the data for a report, actually a series of
reports but that is not significant.

Basically the report has 5 sections that are similar. In each section an
Agency is selected, based on the Agency selection a following combobox is
populated with agents associated with that Agency and then based on the
agent selection a series of textboxes in that section are populated.

These are only sections in the sense that I've put a rectangle around the
comboboxes and textboxes in a section to guide the user, nothing more.

I could have created five separate tables for each of the Agencies and 5
separate tables for Agents related to each Agency but since the basic
structure of the tables would be identical, I chose to have 1 Agency table
and 1 Agent table. The AgencyID is saved as a foreign key in the Agent
table to designate the Agent's relationship to the Agency.

Karen

 

[RSGinCA]

See comments/questions intersperced, below.

Rick,

Indeed, the form collects the data for a report, actually a series of
reports but that is not significant.

Basically the report has 5 sections that are similar. In each section an
Agency is selected, based on the Agency selection a following combobox is
populated with agents associated with that Agency and then based on the
agent selection a series of textboxes in that section are populated.

These are only sections in the sense that I've put a rectangle around the
comboboxes and textboxes in a section to guide the user, nothing more.

I could have created five separate tables for each of the Agencies and 5
separate tables for Agents related to each Agency but since the basic
structure of the tables would be identical, I chose to have 1 Agency table
and 1 Agent table. The AgencyID is saved as a foreign key in the Agent
table to designate the Agent's relationship to the Agency.

Karen

Rick G

 

[RSGinCA]

Sorry about the previous post... hit the enter key by mistake before I had
written anything.

See comments/questions interspersed, below.

Rick,

Indeed, the form collects the data for a report, actually a series of
reports but that is not significant.

I'm still confused. When you say "collects the data", what does that mean?
Does that mean it's being input by the user? Or does it mean the form is
collecting existing data from existing database table in order to be printed on
the report later? Or what?

Basically the report has 5 sections that are similar. In each section an
Agency is selected, based on the Agency selection a following combobox is
populated with agents associated with that Agency and then based on the
agent selection a series of textboxes in that section are populated.

These are only sections in the sense that I've put a rectangle around the
comboboxes and textboxes in a section to guide the user, nothing more.

In each section of the form, it looks like you have 3 steps:

1) The AgencyComboBox needs a query associated with it. This would be in the
combo box's Rowsource property. The query would look something like the
following: "SELECT Agency.AgencyID, Agency.Name FROM Agency ORDERED BY
Agency.Name". The combo box would display every agancy. This would be
identical for the Agency combo box in each section.

2) Once a selection were made in an AgencyComboBox, you would ReQuery the
AgentComboBox in the SAME section so that it would list the Agents associated
with that Agency. Each AgentComboBox would also have a query associated with
it. The query would be SOMETHING LIKE the following: "SELECT Agent.Name
Agent.AgentID FROM Agent ORDERED BY Agent.Name WHERE Agent.AgencyID =
AgencyComboBox1.??SelectedItem??.AgencyID". (Please note that off the top of
my head I'm not familiar with how to refer to the selected item in the
AgencyComboBox.) Note that this query is specifically refering to the selected
item in the specific Agency combo box in the same section - AgencyComboBox1.
The queries in each section would be a little different in that they would
refer to different combo boxes... AgencyComboBox2/3/4/5 (or whatever you named
them).

3) Once a selection is made in an AgentComboBox, use that combo box's selected
agent (AgentID) in the DLookups to get the information from the database to
display on in that section of the form.

I could have created five separate tables for each of the Agencies and 5
separate tables for Agents related to each Agency but since the basic
structure of the tables would be identical, I chose to have 1 Agency table
and 1 Agent table. The AgencyID is saved as a foreign key in the Agent
table to designate the Agent's relationship to the Agency.

That's definately the wat to go - 1 Agency table and 1 Agent table.

Karen

Rick G

 

[Karen]

Thanks Rick,

Yes, I'm talking about a form in Access where the user makes selections.
Those selections are put in a Report table and later, in Word, those values
are merged into a report.

I have done everything you suggested although the Agency combos are slightly
different as each displays Agencies where the AgencyType is Investigation or
Law or Insurance.....that sort of thing.

I have using the AgencyID to pull the applicable Agents with a Select query
on the Agent's combobox's rowsource.

I am using DLookup to populate the textboxes after the Agent selection is
made.

What piques my interest is John's suggestion that I just use a query to base
the Report Access form on that would pull from the Report table and the
associated Agency and Agent table. I can figure this out in code per
combobox but I just fall down when I try to figure out how to use one
reasonable query for the form.

Thanks for help.

Karen

 

[RSGinCA]

This should be easy and the Report wizard should take care of everything for
you.

However, I'm assuming that, in Access, you have actually defined the
relationships between the tables.

If you have done that, then all you need to do is run the Report wizard, and
specify the fields/tables that you want, and the report will be generated with
an appropriate SQL statement in the report's Record Source property. That SQL
statement will combine the records in your 3 tables appropriately to create the
report.

What piques my interest is John's suggestion that I just use a query to base
the Report Access form on that would pull from the Report table and the
associated Agency and Agent table. I can figure this out in code per
combobox but I just fall down when I try to figure out how to use one
reasonable query for the form.

Rick G