Control Source

[srpatel]

I have a back end and front end database. I do know that for changes
on forms, reports and modules that gets done in the front end and the
database objects done in the back end.

I have modified my main table by adding two new fields to it. I want
these two fields to be relate with two combo boxes within the main
form. However whenever I try and bound the combo box using the control
source, I can't seem to see the new fields in the field list.

Could anyone help me out on this please?

Thanks

Shreekant

 

[TC]

Try deleting & recreating the linked (FE) table.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[srpatel]

Hi TC,

I have tried that but still do not seem to see the newly added fields
in the field list. I have also added the table to the main query but
still cant seem to get it.

 

[TC]

What do you mean, you "added the table to the main query"? What main
query?

I assumed that you had a form, which was based on a table, and you
added some fields to that table, but they didn't appear in the list of
fields for that form. Now it sounds like that is /not/ what you are
doing. So, please describe what you are doing!

Cheers,
TC (MVP Access)
http://tc2.atspace.com

 

[srpatel]

Hi Sorry for the confusion. I managed to sort out the issue, i had to
add the field to the form's record source as it was based on a query.

I do apologise as I am new to access.

Thank you for your help though.

 

[TC]

No probs. Glad you got it working! Thanks for posting back to let me
know.

TC (MVP Access)
http://tc2.atspace.com

 

[srpatel]

I have another question wondering if you could help.

I would like some tips on
table designs.

I have 2 tables that I have added to an existing database. Table 1
contains all the description values in one the columns. Table 2
contains 5 entries which are the group or categories that the
descriptions belong to. For example:

tblDescriptions

Column Headers: -
DescID, Description

Fencing
Bridges
Lighting
Ventilators

tblGroup

Columns: -
GroupTypeID, GroupType

Grp A
Grp B
Grp C
Grp D
Grp E

The issue I have is, some of the description values appear in more than

1 group. E.g. fencing could appear in Grp A, Grp B and Grp C and not
in Grp D and E. I can't seem to get around a suitable design on how
to manage the data. Do I need to create another table or add an
existing column within one of the tables with some sort of relevant ID
code?

 

[TC]

What you have, is a so-called "many to many relationship". One group
can have many items (you've calle them "descriptions" - I think "item"
would be better), and conversely, one description can be in many
groups.

First, let's get your tables documented properly. Don't say "columns".
The things in a table, are "fields". Remember, a databas is /not/ like
a spreadsheet. List each field for the table, each one on a seperate
line.

tblDescriptions
DescID
Description

tblGroup
GroupTypeID
GroupType

Now we come to a critical thing. In a relational database such as
Access, >>> each table must have a so-called "primary key" <<<. The
primary key is the field, or combination of fields, that "uniquely
identify" each record in that table. In other words, each record in a
table has a /unique value/ of the primary key field(s).

You set the primary key field(s) for a tabe, using the key symbol in
one of the toolbars. I don't have Access here to check, so I can not
give you step by step instructions on that. Check in F1 help, or just
google the Access groups for "settting the primary key", or somesuch.

So in your case:

tblDescriptions
DescID (PK)
Description

tblGroup
GroupTypeID (PK)
GroupType

Now I can answer your question! The way to implement a "many to many"
relationship, is to create a new table. The new table must contain /at
least/ the primary key fields of the two other tables that you are
trying to create the "many to many relationship" for.

The primary key of the new table, is /both/ the primary keys from the
other two tables. That is, the primary key of th new table will always
comprise at least two fields. This is called, a "composite" primary
key. So in your case:

tblGroupDescriptionMembership
DescID (composite)
GroupTypeID (primary key)

Now, by adding records to the new table, you can put any number of
different Descriptions into a given group; and conversely, you can have
a given Description in any number of different groups.

o tblDescriptions stores data for a Description, >>> regardless of
what group(s) that description is in (if any) <<<

o tblGroup stores data for a group, >>> regardless of which
Desciptions (if any) are in that group <<<

o tblGroupDescritionMembership stores data >>> specific to a
particular Description in a particular group <<<

So, if you wanted to store the /date/ that a particular Description had
been added to a partcular Group, you'd add a date field to
tblGroupDescriptionMembership - since that date would be specific to a
particular Description in a paticular Group.

If you understand all the above, you are well on the way to
understanding basic relational theory. This is what makes a relational
database like Access, /completely different/ to a spreadsheet program
like Excel, even though Access can display its data in a grid fashion,
just like a spreadsheet.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[srpatel]

Hi thanks,

I have made changes to the database and your help has greatly made me
understand how to manage my tables/fields and understand relational
databases. It's all making sense and seems to work.

Thank you very much.
Shreekant

 

[TC]

Ok, well done for continuing!

Believe me, you will reap the benefits, if you design your table
structures properly. The table structures in a database, are like the
foundations for a building. If the foundations are sound, the building
will last, and you can add extra floors later. But if the foundations
are poor, the building will crack and fall over, before you even occupy
it!

Cheers,
TC (MVP Access)
http://tc.atspace.com

 

[TC]

Ok, well done for continuing!

Believe me, you will reap the benefits, if you design your table
structures properly. The table structures in a database, are like the
foundations for a building. If the foundations are sound, the building
will last, and you can add extra floors later. But if the foundations
are poor, the building will crack and fall over, before you even occupy
it!

Cheers,
TC (MVP Access)
http://tc2.atspace.com

 

[srpatel]

Hi TC,

I have further enhanced my database and have come to abit of a stand
still, and am sure this simple.

I have created all the relationships and they are working fine.

However I require 2 combo boxes on my form, where by CB1 has 5 fields
which are pulled from tblGroupType. I have done this.

Each field e.g. relates to different fields from another table, e.g.
red blue and green are part of field High Alerts.

So if a user select High Alerts from combo box 1 in combo box 2 he gets
the options for red, green or blue. but if he selects Medium Alerts, in
combo box2 he gets another 3 or however many options available, but not
the ones for High Alert.

This written seems quite complicated but my tables are structured to do
this, I am stuck on the SQL query for the 2nd combo box.


Your help would be much appreciated.

Cheets
Shreekant

 

[TC]

Ok, say your form has two combo boxes CB1 and CB2.

Say CB1 displays all the records from table T1. So the recordsource of
CB1, would be the table name (T1), or, the sql statement: SELECT *
FROM T1

The user selects a value in CB1.

Now you want CB2 to only display the records from table T2, where the
field X (in table T2) is equal to the value selected in CB1. The
recordsource of CB2, would just be: SELECT * FROM T2 WHERE [X] = """"
& ![CB1] & """"

You'd also need the following statement in the AfterUpdate event of
cbo1: ME![CBO2].REQUERY This ensures that CB2 is repopulated
whenever the user selects a different entry in CB1.

I haven't tested all that, but it should be fairly ok.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[srpatel]

This is exactly how my tables are created. Please have a look, I am
working on your previous most, hopefully it works.

tblLUPAssetClass
->ClassID
->Class PK

tblAssetType
->AssetTypeID PK
->ClassID
->AssetTypeCode
->AssetTypeDesc

tblAssetTypeLocation
->AssetTypeLocationID
->AssetTypeID PK
->StructureLocationID PK

tblStructuredLocations
->StructureLocationID
->StructureLocationBase PK
->StructureLocationDetail PK


******************

tblStructuredLocations - Contains the 3 types of locations we have.
e.g. Factory, Depot, Warehouse


tblAssetType - e.g. Floor Boards, Skirting, Coving

tblAssetTypeLocations - this table identifies where all assets are
kept, the items have multiple locations e.g. Floor Boards could be at
all three Locations e.g. Factory, Depot, Warehouse
e.g. Skirting only at Warehouse.

*******************
In combo Box One - I have the StructureLocations Table in the Row
Source = tblStructuredLocations.

In Combo box two - I can't seem to get the sql query right where by
when a user selects say Warehouse, only the relevant relating fields
would appear.


******************

 

[TC]

Hi again


The primary keys are not yet right. But we're getting there, so let's
perservere!


TBLUPASSETCLASS
================

tblLUPAssetClass
->ClassID
->Class PK

If Class is the PK, this means that every record in this table, has a
unique value of the Class field. No two records can have the same
Class.

So, what then is ClassID?

Show me some examples of actual records from this table.



TBLASSETTYPE
============

tblAssetType - e.g. Floor Boards, Skirting, Coving

tblAssetType
->AssetTypeID PK
->ClassID
->AssetTypeCode
->AssetTypeDesc

If this table is meant to contain one record for each "asset type",
where "floor boards" is an asset type, then, it only needs two fields -
not four:

AssetTypeID PK
AssetTypeDescription



TBLASSETTYPELOCATIONS
======================

tblAssetTypeLocations - this table identifies where all assets are
kept, the items have multiple locations e.g. Floor Boards could be at
all three Locations e.g. Factory, Depot, Warehouse
e.g. Skirting only at Warehouse.

Ok, that is very clearly explained. This is a so-called "many to many
relationship". An asset can be in many locations, and conversely, a
location can have many assets.

You express this by having an Assets table, a Locations table, and a
third table whose primary key is a composite key comprising (1) the
primary key of the Assets table, plus (2) the primary key of the
Locations table. So in your case:

tblAssetType
AssetTypeID PK
AssetTypeDescription
(other fields describing the asset)

tblStructuredLocations
StructureLocationID PK
(other fields describing the location)

tblAssetTypeLocation
AssetTypeID ( composite )
StructureLocationID ( primary key )
(fields describing THAT ASSET in THAT LOCATION)

not this:

tblAssetTypeLocation
->AssetTypeLocationID
->AssetTypeID PK
->StructureLocationID PK

APPROACH
=========

The thing that is throwing you off-track here, is that you are trying
to design the tables "too soon". Like anything, it's easy when you know
how to do it! But it's very difficult before that point. The better
approach is usually, to describe your data in narrative english form -
without any mention of tables, records, or primary keys - then let
someone experienced, suggest the proper table structure.

For example: "We must keep track of various assets. Assets include, for
example, "skirting", and "floorboards". We also have many locations;
for example, "warehouse", and "factory". An asset might be in many
locations, and conversely, a location might have many assets." (And so
on.)

See what you can make of all that! I'm happy to help you further,
whatever way you plan to proceed.

The issue that we are talking about here, is called "database
normalization". It is probably the most difficult thing to understand,
when you start designing a database. Here's a Microsoft article about
it. You can doubtless find many other articles, by searching the web.


http://msdn.microsoft.com/library/d.../en-us/dnacc2k2/html/odc_FMSNormalization.asp

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[srpatel]

tblLUPClass - Like the group family the assets belong to.

Class ID | Class
1 | Floors
2 | Bridges
3 | Gantries


tblAssetType

AssetTypeID | AssetTypeDesc | AssetTypeCode | ClassID

1 | Access gantry | AG | Gantries
2 | OverBridge | OB | Bridges
3 | Brick Tunnel | BT | Tunnels


tblStructureLocations - *this is what comes in combo box A

StructureLocationID | StructureLocationBase | StructureLocationDetail

1 | Structures | Depot
2 | Structures | Base
3 | DeepTunnel | Base
4 | DeepTunnel | Depot


tblAssetTypeLocations

AssetTypeLocation | AssetTypeID | StructureLocationID

1 | Access gantry | Structures | Depot
2 | Access gantry | Structures | Base
3 | OverBridge | Structures | Depot
4 | Brick Tunnel | DeepTunnel | Base

This is exactly how the data is in the tables, hope this helps, I am
reading your information hopefully this gives a bit more insight on how
the information is related.
With regard to users, they are only concerned with the Structure
Locations and the Asset Descriptions that are linked with the relevant
location types. The information is required to be kept within the
database.

Thanks for your very informative help

 

[TC]

Ok! You're running me hard on this!! But it's great that you are
perservering. Believe me, the effort is well worth it. It's like
building the house on a solid foundation.

But it's 2am here, so I am off to bed! I'll get back on this in 8-10
hours. See you then

TC (MVP Access)
http://tc2.atspace.com

 

[srpatel]

Ok Cool, I will check the post early tommorrow morning on my side.
Good Nite and chat tmrw.

 

[TC]

Ok Cool, I will check the post early tommorrow morning on my side.
Good Nite and chat tmrw.

Ok; back to the fray!

====

tblLUPClass - Like the group family the assets belong to.

Class ID | Class
1 | Floors
2 | Bridges
3 | Gantries

That's fine. ClassID is the primary key (PK) of that table.

====

tblAssetType

AssetTypeID | AssetTypeDesc | AssetTypeCode | ClassID

1 | Access gantry | AG | Gantries
2 | OverBridge | OB | Bridges
3 | Brick Tunnel | BT | Tunnels

No. The previous table defined some classes, and gave a unique
identifier (ie. primary key) for each class. For example, the previous
table said that ClassID=2 is for Bridges. So, in /this/ table, if you
want to store the Class of each asset, you do that by including the
relevant /ClassID/. That's why we /have/ a ClassID - so you can use it
in other tables, to link those records to /that class/.

If you have various assets (eg. Access gantry, Overbridge, Brick
tunnel) - each of which belongs to a specified class - this is how you
would do that:

tblLUPClass - like you had before:
Class ID (PK) | Class
1 | Floors
2 | Bridges
3 | Gantries

4 | Tunnels

tblAsset - vvvvvvvvv
AssetID (PK) | Description | ClassID
1 | Access gantry | 3
2 | Overbridge | 2
3 | Brick tunnel | 4

So, AssetID=1 uniquely identifies the asset, "Access gantry"; and that
asset has ClassID=3, whch means, it is of the class "Gantries". You do
not need /two/ unique identifiers - AssetID /and/ AssetTypeCode. One is
quite enough! And there is no confusion between AssetID=1 and
ClassID=1: they key-in to /different tables/.

====

I feel that you have similar problems in the other tables.

If the above doesn't help, I feel it will be easier if you describe
your data in ordinary narratiuve english form, without making any
reference, at all, to technical terms like table, record, field, or
key. Then I can quickly tell you how the tables should be structured.
At present, I think you'd agree that we are going around in circles a
bit.

However, if you want to keep trying to finish the design yourself, I'm
more than happy to keep helping.

Cheers,
TC (MVP Access)
htp://tc2.atspace.com

 

[srpatel]

Hey,

Ok I have had a read through your work, and it does seem that we may be
going round in circles.

I think the best way you would be able to help me further is if you
have a quick look at the exel file.

Lemme know if you want to.

cheers
Shreekant