Help creating my first Access Database

[Guest]

I would like to create a simple Access Database and link it to Viso. I've
tried all the tutorials and templates, but they just don't provide me with
the information to get started. Any help would be greatly appreciated.

I have two pieces of information each with there own descriptions and what
not. I would like to be able to query information "A" to get information "B"
and vice-versa.

A - a unique number (1,2,3,4,...)
B - a unique letter (a,b,c,d,...)

"A" and "B" relate in that for a "B" there are "A" values associated.

a = 1,2
b = 3,4
c = 1,2,3,4
d = 5,6,7,8
e = 9,15

Eventually, I would like to create a Viso drawing were a user could click a
item "A" to get information on "A" and "B" associated with "A". Then, one
could click the "B" items associated with "A" to get all the "A" items
associtated with "B".

Thanks,
-Aaron

 

[Guest]

Hi Aaron,

Which part are you actually having trouble with? The database table design,
or the visio 'front end'
You state that "A" will be a unique number, then say that a = 1,2 , which is
either 2 (non-unique) numbers or '1,2' which is text rather than a number,
maybe I'm mis-understanding the relationship between A & B and what they
contain, can you post more sample data for values in fields A & B and any
table design you have so far please.

TonyT..

 

[Guest]

Hi Tony,

Sorry for being somewhat vague in my first post. Here are the two tables I
have so far:

Point : Table
PointID PointName MessageID StructureName
1 SpeechEncInput 0x000A6980 MM_8KHZ_FRAME_LOG_T
2 PackedEncOutput 0x000A6981 MM_AFTER_ENC_LOG_T
3 PackedDecInput 0x000A6982 MM_BEFORE_DEC_LOG_T
4 SpeechDecOutput 0x000A6983 MM_8KHZ_FRAME_LOG_T
5 ReferenceIn 0x000A6984 MM_8KHZ_FRAME_LOG_T
6 ReferenceOut 0x000A6985 MM_8KHZ_FRAME_LOG_T
7 SourceIn 0x000A6986 MM_8KHZ_FRAME_LOG_T
8 SourceOut 0x000A6987 MM_8KHZ_FRAME_LOG_T
9 MicIn 0x000A6988 MM_8KHZ_FRAME_LOG_T
10 ASDLSpeechIn 0x000A6989 MM_8KHZ_FRAME_LOG_T
11 ASDLSpeechOut 0x000A698A MM_8KHZ_FRAME_LOG_T
12 ASULB4SpeechIn 0x000A698B MM_8KHZ_FRAME_LOG_T
13 ASULB4SpeechOut 0x000A698C MM_8KHZ_FRAME_LOG_T
14 ASULAFSpeechIn 0x000A698D MM_8KHZ_FRAME_LOG_T
15 ASULAFSpeechOut 0x000A698E MM_8KHZ_FRAME_LOG_T
16 SpeakerOut 0x000A698F MM_8KHZ_FRAME_LOG_T

Port : Table
PortID PortName PortDescription
1 p/spl_audio/0 Speech encoders input and output data
2 p/spl_audio/1 Speech decoders input and output data
3 p/spl_audio/2 Full voice call input and output data
4 p/spl_audio/3 Echo Control audio signals
5 p/spl_audio/4 Audio Shaping signals (DL)

A Port (a,b,c,... in the earlier example attempt) is a collection of Points
(1,2,3... in the earlier example attempt). A Point may be in more than one
Port.

First, I would like to be able to see the Ports that a Point is in by
entering the PointName. Then I would like to be able to see all the Points
in a Port by entering the Port. This all would end up being linked to a Viso
drawing where the Points are items a user could click to get the information
on the Ports the Point is in to be able to choose the right Port for the task
at hand.

I like the report generation of Access as it would next to automatically
make a report of Ports, Points, all Points in a Port and all Ports that a
Point is in.

I find this very similar to a classroom example in that the Points are
Students and the Ports are Teachers/Classrooms. A Classroom is a collection
of Students and a Student can attend more than one class.

I appreciate the help!

Thanks,
-Aaron

 

[Guest]

Hi again aaron,

Now I'm starting to understand a little better (I think ),
to re-cap, a Port can have many points, and a point can be in many ports,
which is a many-to-many relationship, which needs to be resolved with a link
table;

tblConnections
ConnectionsID
PointID
PortID

This resolves down to 2x one-to-many relationships which will prevent
duplicate data everywhere.
You can then create 2 queries, one based on Point the other Port to draw the
information back together and be used as the rowsource for the combobox to
choose PointName and PortName & to display the results for each in, probably,
a listbox.

As far as the visio part is concerned, I'm completely clueless time for a
post in visio/genereal questions when you get closer I think.

good luck,

TonyT..

 

[Guest]

Hi Tony,

Ok, so I've created a new table with a primary key and PortID and PointID.
I then used a "List Box" lookup for each to create the following Connection
table:

ConnectionID PortID PointID
1 p/spl_audio/0 SpeechEncInput
2 p/spl_audio/0 PackedEncOutput
3 p/spl_audio/1 PackedDecInput
4 p/spl_audio/1 SpeechDecOutput
5 p/spl_audio/2 SpeechEncInput
6 p/spl_audio/2 PackedEncOutput
7 p/spl_audio/2 PackedDecInput
8 p/spl_audio/2 SpeechDecOutput
9 p/spl_audio/3 ReferenceIn
10 p/spl_audio/3 ReferenceOut
11 p/spl_audio/3 SourceIn
12 p/spl_audio/3 SourceOut

I was able to use the pull downs to select both the PortName and PointName
for each entry (Not sure if I should be using ID in the names as Name is what
is being selected...). Afterwards, I was able to make a simple query to list
the Ports in ascending order by looking at the Connection table:

PortID PointID
p/spl_audio/0 SpeechEncInput
p/spl_audio/0 PackedEncOutput
p/spl_audio/1 PackedDecInput
p/spl_audio/1 SpeechDecOutput
p/spl_audio/2 SpeechEncInput
p/spl_audio/2 PackedEncOutput
p/spl_audio/2 PackedDecInput
p/spl_audio/2 SpeechDecOutput
p/spl_audio/3 ReferenceIn
p/spl_audio/3 ReferenceOut
p/spl_audio/3 SourceIn
p/spl_audio/3 SourceOut

Now when I go to generate a report, I can't get the data to ressemble a
format of all the relationships that is readable like the following:

PortID PointID
p/spl_audio/0 SpeechEncInput
PackedEncOutput

p/spl_audio/1 PackedDecInput
SpeechDecOutput

p/spl_audio/2 SpeechEncInput
PackedEncOutput
PackedDecInput
SpeechDecOutput

Instead, I always a format that list all the PortIDs and all the PointIDs
over and over again highlighting the values.

Also, I would like to be able to generate a report for a single Point and
Port if possible.

Yea, the Viso idea is somewhat of a dream. I'll be happy to be able to
generate a master report and reports for each Point and Port.

Time to run to Barns and Noble for some refference material as my emedded
software experience in assembly and C is not meshing well with this database
language...

Thanks,
-Aaron

 

[Guest]

Hi again Aaron,

Hi Tony,

Ok, so I've created a new table with a primary key and PortID and PointID.
I then used a "List Box" lookup for each to create the following Connection
table:

Is the lookup an in-table lookup, or have you created a form with a listbox
on it?
If it's a table lookup, get rid of it, they are nothing but a pain in the
ar*e. Create a join between your tables in the realtionships window if you
haven't already, and then just create 2 straight forward queries, one with
PointTbl on the left, then ConnectionTbl then PortTbl, and a second with
PortTbl on the left, then ConnectionTbl followed by PointTbl, each with an
Outer join between the right hand 2 tables (click on join line between the 2
and choose all records in PortTbl that are equal to ConnectionTbl &
vice-versa for 2nd query). This should give 1 query showing Ports in order
with each point for each port and the second showing each Point in order and
their respective Ports.

ConnectionID PortID PointID
1 p/spl_audio/0 SpeechEncInput
2 p/spl_audio/0 PackedEncOutput
3 p/spl_audio/1 PackedDecInput
4 p/spl_audio/1 SpeechDecOutput
5 p/spl_audio/2 SpeechEncInput
6 p/spl_audio/2 PackedEncOutput
7 p/spl_audio/2 PackedDecInput
8 p/spl_audio/2 SpeechDecOutput
9 p/spl_audio/3 ReferenceIn
10 p/spl_audio/3 ReferenceOut
11 p/spl_audio/3 SourceIn
12 p/spl_audio/3 SourceOut

I was able to use the pull downs to select both the PortName and PointName
for each entry (Not sure if I should be using ID in the names as Name is what
is being selected...). Afterwards, I was able to make a simple query to list
the Ports in ascending order by looking at the Connection table:

PortID PointID
p/spl_audio/0 SpeechEncInput
p/spl_audio/0 PackedEncOutput
p/spl_audio/1 PackedDecInput
p/spl_audio/1 SpeechDecOutput
p/spl_audio/2 SpeechEncInput
p/spl_audio/2 PackedEncOutput
p/spl_audio/2 PackedDecInput
p/spl_audio/2 SpeechDecOutput
p/spl_audio/3 ReferenceIn
p/spl_audio/3 ReferenceOut
p/spl_audio/3 SourceIn
p/spl_audio/3 SourceOut

Now when I go to generate a report, I can't get the data to ressemble a
format of all the relationships that is readable like the following:

Get rid of the lookups in the tables as per above, and use each query for
each report and you will get what you are after below, you just have to make
sure you choose your grouping correctly.

PortID PointID
p/spl_audio/0 SpeechEncInput
PackedEncOutput

p/spl_audio/1 PackedDecInput
SpeechDecOutput

p/spl_audio/2 SpeechEncInput
PackedEncOutput
PackedDecInput
SpeechDecOutput

Instead, I always a format that list all the PortIDs and all the PointIDs
over and over again highlighting the values.

Also, I would like to be able to generate a report for a single Point and
Port if possible.

If you create a form, and have a combobox each for port and point, you can
open the report from there, including a Where clause for printing only the
selection in either combobox.

Yea, the Viso idea is somewhat of a dream. I'll be happy to be able to
generate a master report and reports for each Point and Port.

Time to run to Barns and Noble for some refference material as my emedded
software experience in assembly and C is not meshing well with this database
language...

heheh, steep learning curve on new languages isn't it....

good luck

TonyT..

 

[Guest]

Hi Tony,

Ok, so the connection table is now just a table of the three IDs all of
number type with the relationships made between the IDs.

First, I made a relationship between Connection.PortID and Port.PortID along
with Connection.PointID and Point.PointID using option 2 for each join type.

Then, I made two queries: Port->Connection->Point and Point->Connection->Port.

The concept of Outer Join is loosing me at this time as I found no way of
performing this with the gui. Regardless, the SQL for each query is as
follows:

SELECT Point.PointName, Point.MessageID, Point.StructureName
FROM Port INNER JOIN (Point LEFT JOIN [Connection] ON Point.PointID =
Connection.PointID) ON Port.PortID = Connection.PortID;

SELECT Port.PortName, Port.PortDescription
FROM Point INNER JOIN (Port LEFT JOIN [Connection] ON Port.PortID =
Connection.PortID) ON Point.PointID = Connection.PointID;

I've had no luck getting these two queries to work due to the following error:

"The SQL statement could not be exectuted because it contains ambiguous
outer joins. To force one of the joins to be performed first, create a
seperate query that performs the first join and then include that query in
your SQL statement."

So to the help menu I went... The help menu stated I need to make two joins
by first making a query for the first join and then join the two into one.
This was of no help as the same error appeared.

I was able to make two queries: Point->Connection and Port->Connection.
Both would list all the information, but I could not find out how to actually
associate a Point with a Port.

Yea, learning curve! I'll stick to my speech coders, HiFi decoder, video
decoders and low level drivers. This little project is suppose to be the
future of documenting our debugging capabilities, but I have to wonder if an
embedded software engineer can be expected to get very far with Access
without some formal training.

Thanks,
-Aaron

 

[John Vinson]

The concept of Outer Join is loosing me at this time as I found no way of
performing this with the gui.

PMFJI...

Doubleclick the Join line in the query design grid. You'll get a "join
properties" dialog box.

Select Option 2 (or 3); "include all records in <one table> and
matching records in <the other table>" for a Left (Right) join.

John W. Vinson[MVP]

 

[Guest]

Hi Aaron,

Your first set of relationship connections should be in the 'Relationships'
window of your database, if you haven't done so already (no option 1/2/3 just
Enforce Referential Integrity, cascade update/delete - tick all 3).

If the Outer joins won't provide the data you want, you will have to create
a query between Connection Table & Point Table, use option 2/3 (all records
from connection Table and only those from Point Table Where The Joined Fields
are equal) which is an outer join. (could be option 2 OR 3 depending which
table is the on the left).

Now use this query in another query - add table Port & 1stQuery (above
created one) and join as option 1 - (only incluse rows where the joined
fields are equal) join on PortID <-> PortID.

This should give the records you want as first query - All Ports and each
Point for each Port. The use of 2 queries resolves the outer join issue.

Repeat in reverse order for Point Table queries.

Keep going, I'm sure the result is going to be worth the effort!!

TonyT..

Hi Tony,

Ok, so the connection table is now just a table of the three IDs all of
number type with the relationships made between the IDs.

First, I made a relationship between Connection.PortID and Port.PortID along
with Connection.PointID and Point.PointID using option 2 for each join type.

Then, I made two queries: Port->Connection->Point and Point->Connection->Port.

The concept of Outer Join is loosing me at this time as I found no way of
performing this with the gui. Regardless, the SQL for each query is as
follows:

SELECT Point.PointName, Point.MessageID, Point.StructureName
FROM Port INNER JOIN (Point LEFT JOIN [Connection] ON Point.PointID =
Connection.PointID) ON Port.PortID = Connection.PortID;

SELECT Port.PortName, Port.PortDescription
FROM Point INNER JOIN (Port LEFT JOIN [Connection] ON Port.PortID =
Connection.PortID) ON Point.PointID = Connection.PointID;

I've had no luck getting these two queries to work due to the following error:

"The SQL statement could not be exectuted because it contains ambiguous
outer joins. To force one of the joins to be performed first, create a
seperate query that performs the first join and then include that query in
your SQL statement."

So to the help menu I went... The help menu stated I need to make two joins
by first making a query for the first join and then join the two into one.
This was of no help as the same error appeared.

I was able to make two queries: Point->Connection and Port->Connection.
Both would list all the information, but I could not find out how to actually
associate a Point with a Port.

Yea, learning curve! I'll stick to my speech coders, HiFi decoder, video
decoders and low level drivers. This little project is suppose to be the
future of documenting our debugging capabilities, but I have to wonder if an
embedded software engineer can be expected to get very far with Access
without some formal training.

Thanks,
-Aaron

Hi again Aaron,




Is the lookup an in-table lookup, or have you created a form with a listbox
on it?
If it's a table lookup, get rid of it, they are nothing but a pain in the
ar*e. Create a join between your tables in the realtionships window if you
haven't already, and then just create 2 straight forward queries, one with
PointTbl on the left, then ConnectionTbl then PortTbl, and a second with
PortTbl on the left, then ConnectionTbl followed by PointTbl, each with an
Outer join between the right hand 2 tables (click on join line between the 2
and choose all records in PortTbl that are equal to ConnectionTbl &
vice-versa for 2nd query). This should give 1 query showing Ports in order
with each point for each port and the second showing each Point in order and
their respective Ports.
Get rid of the lookups in the tables as per above, and use each query for
each report and you will get what you are after below, you just have to make
sure you choose your grouping correctly.


If you create a form, and have a combobox each for port and point, you can
open the report from there, including a Where clause for printing only the
selection in either combobox.
heheh, steep learning curve on new languages isn't it....

good luck

TonyT..