Extreme Newbie Confusion

  • Thread starter Thread starter DAnte
  • Start date Start date
D

DAnte

I have created a database that contains a main table that
has a many-to-many relationship with two other tables. I
am trying to create a query that consists of pretty much
all of the data in the data base, but I can't seem to get
all of the data to display. When I do a query based on
the first many-to-many relationship it works fine and the
SQL looks like below

SELECT tblInputFiles.InputNum, tblInputFiles.Portfolio,
tblInputFiles.AppNum, tblInputFiles.Application,
tblInputFiles.AppType, tblInputFiles.FileName,
tblInputFiles.UploadSnumb, tblInputFiles.DownloadSnumb,
tblInputFiles.FieldName, tblInputFiles.FieldType,
tblInputFiles.FieldLength, tblInputFiles.Frequency,
tblInputParentLink.InputNum, tblInputParentLink.PFileNum,
tblSysSourcePTFile.ParentFileName

FROM tblSysSourcePTFile INNER JOIN (tblInputFiles INNER
JOIN tblInputParentLink ON tblInputFiles.InputNum =
tblInputParentLink.InputNum) ON
tblSysSourcePTFile.PFileNum = tblInputParentLink.PFileNum;

But when I add the tables that are part of the second many-
to-many relationship I don't get any results and the SQL
in the FROM portion of the code looks like this

FROM tblSysSourceDbase INNER JOIN ((tblSysSourcePTFile
INNER JOIN (tblInputFiles INNER JOIN tblInputParentLink ON
[tblInputFiles].[InputNum]=[tblInputParentLink].
[InputNum]) ON [tblSysSourcePTFile].[PFileNum]=
[tblInputParentLink].[PFileNum]) INNER JOIN
tblInputDBaseLink ON [tblInputFiles].[InputNum]=
[tblInputDBaseLink].[InputNum]) ON [tblSysSourceDbase].
[DBFfieldNum]=[tblInputDBaseLink].[DBFieldNum];

Here are the relationships in the database

tblInputFiles---One-To-Many---tblInputParentLink
tblSysSourcePTFile---One-To_Many---tblInputParentLink

tblInputFile---One-To-Many---tblInputDBaseLink
tblSysSourceDbase---One-To-Many-tblInputDbaseLink

I am not terribly familiar with the SQL syntax in access
so dealing with joins is extremely confusing. Does any
one know if it is possible to do what I am doing? What
can I do to pull records from all three tables at the same
time?
 
You mention that you have many-to-many relationships but
at the end you show your RI to be one-to-many. Either way
you can do this. One recommendation though, create a
couple of other tables that will allow you to put some of
this repeating data there. For example, have one main
table with as much non-repeating data as possible. For
all of the repeating data create other tables (with a link
to your main one). In your case you might want to have an
Application table, File table, File Attributes table, and
so on. The Application table can be linked to the File
table by the 'AppNum', etc.

For your query, in the query builder, try linking the
tables (if they don't appear linked already). If they are
linked, right-click on the line and select the 'Join
Properites'. In this box, select item 2 or 3 which will
say something like "all records from table ### and only
matching ones in table ##'.

Hope this helps.
-----Original Message-----
I have created a database that contains a main table that
has a many-to-many relationship with two other tables. I
am trying to create a query that consists of pretty much
all of the data in the data base, but I can't seem to get
all of the data to display. When I do a query based on
the first many-to-many relationship it works fine and the
SQL looks like below

SELECT tblInputFiles.InputNum, tblInputFiles.Portfolio,
tblInputFiles.AppNum, tblInputFiles.Application,
tblInputFiles.AppType, tblInputFiles.FileName,
tblInputFiles.UploadSnumb, tblInputFiles.DownloadSnumb,
tblInputFiles.FieldName, tblInputFiles.FieldType,
tblInputFiles.FieldLength, tblInputFiles.Frequency,
tblInputParentLink.InputNum, tblInputParentLink.PFileNum,
tblSysSourcePTFile.ParentFileName

FROM tblSysSourcePTFile INNER JOIN (tblInputFiles INNER
JOIN tblInputParentLink ON tblInputFiles.InputNum =
tblInputParentLink.InputNum) ON
tblSysSourcePTFile.PFileNum = tblInputParentLink.PFileNum;

But when I add the tables that are part of the second many-
to-many relationship I don't get any results and the SQL
in the FROM portion of the code looks like this

FROM tblSysSourceDbase INNER JOIN ((tblSysSourcePTFile
INNER JOIN (tblInputFiles INNER JOIN tblInputParentLink ON
[tblInputFiles].[InputNum]=[tblInputParentLink].
[InputNum]) ON [tblSysSourcePTFile].[PFileNum]=
[tblInputParentLink].[PFileNum]) INNER JOIN
tblInputDBaseLink ON [tblInputFiles].[InputNum]=
[tblInputDBaseLink].[InputNum]) ON [tblSysSourceDbase].
[DBFfieldNum]=[tblInputDBaseLink].[DBFieldNum];

Here are the relationships in the database

tblInputFiles---One-To-Many---tblInputParentLink
tblSysSourcePTFile---One-To_Many---tblInputParentLink

tblInputFile---One-To-Many---tblInputDBaseLink
tblSysSourceDbase---One-To-Many-tblInputDbaseLink

I am not terribly familiar with the SQL syntax in access
so dealing with joins is extremely confusing. Does any
one know if it is possible to do what I am doing? What
can I do to pull records from all three tables at the same
time?
.
Click to expand...
 
Back
Top