Help needed with alias table.

  • Thread starter Thread starter Frank Martin
  • Start date Start date
F

Frank Martin

I have the following tables:

tblPeople
PeopleID
People

and...

tblAddressNames
AddrNameID
AddressName


I want to use tblPeople only for the two tables above and I think this can
be done via a select query, by using the alias of tblPeople and sending the
selected value back into tblAddressNames.

Could someone put me on the right road for this?

Regards Frank
 
You need to rephrase your question. I don't understand what you want to do?
How are the two tables related? And what do you mean "send the selected
value back to tblAddressNames?
 
We are reconstructing our business application to consolidate all the
separate address types and tables into one.

The old system had separate tables for customers, suppliers, freighters,
personal, personel, consultants, contractors etc.

To do this we have designed the following tables:

tblPeople: (to store the names of all people and types.)

tblPeopleType: (to store types customers, suppliers personal etc).

tblAddressName: (to store the address names (eg a customer may have
different delivery addresses.))

tblAddressType: (Type of addresses, postal, site, FOB, headoffice,
residential etc)

tblAddressDetail: (the addresses location themselves.)

In the relationship window we have put the tblAddressDetails in the centre,
and connected all the others to this with the "many" link of all at the
tblAddressDetail table, via the appropriate IDs.

It appears to us that the tblAddressName is redundant, and that the same
effect can be achieved by creating an alias of tblPeople to do this task.

We have tested this idea on a small scale and it seems to work, in that the
query from the above tables shows the equivalent of the tblAddressName in
the alias of the tblPeople.

We need someone to OK this idea before we start the onerous job of rekeying
all the old addresses into this new system.

Also, we have adjusted the relationship window to reflect the changes above,
and although the alias tblPeople shows the usual "one" to "many"
relationship there, this is not automatically shown in the adjusted query we
make, and the alias tblPeople just shows a normal "one" to "one" link.
Why doesn't this show a 'one' to 'many' like all the others in the query?

Regsrds, Frank
 
Comments inline.....

--
Ken Snell
<MS ACCESS MVP>

Frank Martin said:
We are reconstructing our business application to consolidate all the
separate address types and tables into one.
OK.


The old system had separate tables for customers, suppliers, freighters,
personal, personel, consultants, contractors etc.
Ugh.


To do this we have designed the following tables:

tblPeople: (to store the names of all people and types.)

tblPeopleType: (to store types customers, suppliers personal etc).

tblAddressName: (to store the address names (eg a customer may have
different delivery addresses.))

tblAddressType: (Type of addresses, postal, site, FOB, headoffice,
residential etc)

tblAddressDetail: (the addresses location themselves.)
Click to expand...

I'm not sure I see what the diffference is between tblAddressName and
tblAddressDetail? Your concept of separate tables appears headed in the
right direction. Generically, I would see this table setup:

People table:
PeopleID (primary key)
PeopleTypeID (foreign key to PeopleType table)
PeopleFirstName
PeopleMiddleName
PeopleLastName
etc.

PeopleType table:
PeopleTypeID (primary key)
PeopleTypeName

AddressType table:
AddrTypeID (primary key)
AddrTypeName

PeopleAddress table:
PeopleID (composite primary key with AddrTypeID)
AddrTypeID (composite primary key with PeopleID)
AddrLocationID (foreign key to AddressLocation table)

AddressLocation table:
AddrLocationID (primary key)
AddrFirstLineOfAddress
AddrSecondLineOfAddress
AddrThirdLineOfAddress
AddrCity
AddrStateProvince
AddrMailCode
AddrCountry

Using the above, you use the PeopleAddress table as a junction table in
order to relate a person to an address for each address type. By using an
AddressLocation table, you avoid having to enter the same street address
multiple times (in case it might be used for more than one person). If you
don't want such a table, then the PeopleAddress table could be modified by
deleting the AddrLocationID field, adding in all the fields from the
AddressLocation table, and then deleting the AddressLocation table.
In the relationship window we have put the tblAddressDetails in the centre,
and connected all the others to this with the "many" link of all at the
tblAddressDetail table, via the appropriate IDs.

It appears to us that the tblAddressName is redundant, and that the same
effect can be achieved by creating an alias of tblPeople to do this task.
Click to expand...

Perhaps...can't tell from your description.
We have tested this idea on a small scale and it seems to work, in that the
query from the above tables shows the equivalent of the tblAddressName in
the alias of the tblPeople.
Click to expand...

What is an "alias of tblPeople"? Is that a second copy of the table in the
Relationships window?
We need someone to OK this idea before we start the onerous job of rekeying
all the old addresses into this new system.
Click to expand...

I don't know that anyone here on the newsgroup is going to give an "OK", but
we can suggest approaches. The "OK" will need to come from you/your
organization as you work through your details.

Also, depending upon the setup of your original database, it likely is
possible to copy the data from the old database into the new tables using a
series of manually run append queries. This would be faster than typing all
data again; but again without knowing the structure of the original db, I
can't be positively sure.
Also, we have adjusted the relationship window to reflect the changes above,
and although the alias tblPeople shows the usual "one" to "many"
relationship there, this is not automatically shown in the adjusted query we
make, and the alias tblPeople just shows a normal "one" to "one" link.
Why doesn't this show a 'one' to 'many' like all the others in the query?
Click to expand...

In the design view of a query, the join lines will not show "one-to-many" or
"one-to-one" indications; these are shown only in the Relationships window.
The join line in a query's design view will just be a solid line (no "1"
symbol nor "infinity" symbol, and if it's an "outer join" it'll have an
arrow head on one end.
 
We need someone to OK this idea before we start the onerous job of rekeying
all the old addresses into this new system.
Click to expand...

I'd suggest that you study Ken's suggestions carefully (I've always
found that to be a rewarding endeavour), and to consider using a
series of Append queries to migrate the data from your old
non-normalized tables into the new ones. Rekeying should be an
absolute LAST RESORT, and I cannot see that it will be necessary
except to correct gross errors!
 
Thank you. We have studied your example and it appears similar to ours,
except we use an address title like AddressNames, because a customer can
specify an address or a FOB carrier who has a company name.

But we ask the following question:

If we get a query dynaset which contains all required fields, and this query
can be updated, and therefore this query can be used as a Form source, does
it matter how we arrange the number and type of tables?

That is always assuming we simplify the number of tables as much as
possible, and in
the query we are careful to drag down the "many" tables IDs, and the
corresponding related "one" fields into the grid.

Regards, Frank
 
Order of fields and tables in query is not important unless the query is
being used as the Row Source for a combo box or list box, and then you want
to reference a specific column in a row. For what you want to do, the order
should be immaterial.
 
Back
Top