help with understanding a small piece of code

[Rustaferd]

I am working on a database issue and need to resolve this piece of code. Can you or anyone else understand what is happening here? I need to take out certian parts, but if I do, I get syntax errors. How can I remove anything to do with Facility, or department? I don't need these,but the code I am "borrowing" has them in it. Any ideas?


SELECT [valPerson].[PersonName] AS Name, [valPerson].[PersonID] AS ID, [valCompany].[CompanyName], [valCompanyType].[CompanyTypeDescription], [valPerson].[PersonPhoneNumber], [valPerson].[PersonPhoneExt], [valPerson].[PersonEmailAddress1], [valPersonDepartment].[PersonDepartmentDescription], [valPersonFacility].[PersonFacilityDescription] FROM valPersonFacility INNER JOIN (valPersonDepartment INNER JOIN ((valCompanyType INNER JOIN valCompany ON [valCompanyType].[CompanyTypeID]=[valCompany].[CompanyTypeID]) INNER JOIN valPerson ON [valCompany].[CompanyID]=[valPerson].[PersonCompanyID]) ON [valPersonDepartment].[PersonDepartmentID]=[valPerson].[PersonDepartmentID]) ON [valPersonFacility].[PersonFacilityID]=[valPerson].[PersonFacilityID] ORDER BY [valPerson].[PersonName];

 

[zemp]

This is an SQL statement that nis used to retrieve specific related data from 5 different tables in the database. Each piece of data (to be retrieved) is found in the select statement and are separated with commas. For example,

...,[valPerson].[PersonID] AS ID,...

shows one piece of data in the format [tablename].[fieldname]. The 'AS ID' just gives that column of data an alias of 'ID' rather than the default '[fieldname]' in this case it would be 'PersonID'.

The from clause is more complex as it states the table relationships in this case primary and foreign key relationships. That is the way the statement will know which data belongs to what. 'INNER JOIN' basically says that there is a primary/foreign key relationship and to return data if is exists in both tables. For example,

...valCompanyType INNER JOIN valCompany ON [valCompanyType].[CompanyTypeID]=[valCompany].[CompanyTypeID])...

states that 'valcompanyType' is related to 'valCompany' and then gives the fields that the relation ship is based on, hence the keyword 'ON'. In this statement the relationships are nested to make sure all the tables are related together.

The Order By clause just orders the records alphabetically by the specified field.

To eliminate what you want to eliminate you will need to restructure the joins. It may be easier to rebuild the entire query (new query in Access, or new view in SQL server). I would try something like this,

SELECT [valPerson].[PersonName] AS Name,[valPerson].[PersonID] AS ID,[valCompany].[CompanyName],[valCompanyType].[CompanyTypeDescription],[valPerson].[PersonPhoneNumber],[valPerson].[PersonPhoneExt], [valPerson].[PersonEmailAddress1] 
FROM [valPerson] INNER JOIN 
   [valCompany] ON [valPerson].[PersonCompanyID]= [valCompany].[CompanyID] INNER JOIN 
   [valCompanyType] ON [valCompany].[CompanyTypeID] = [valCompanyType].[CompanyTypeID] 
ORDER BY [valPerson].[PersonName];

Take Care,

zemp

"If the grass looks greener... it's probably because there is more manure."

 

[Rustaferd]

Zemp,

Thanks so much for helping me understand a little better about sql. I know very little but am trying to learn,thus the reason for pulling some code and disecting it.

The one thing I forgot to mention was there there is also no company type. So would the above sql statement be written somthing like this:

SELECT [valPerson].[PersonName] AS Name,[valPerson].[PersonID] AS ID,[valCompany].[CompanyName],[valPerson].[PersonPhoneNumber],[valPerson].[PersonPhoneExt], [valPerson].[PersonEmailAddress1]
FROM [valPerson] INNER JOIN
[valCompany] ON [valPerson].[PersonCompanyID]= [valCompany].[CompanyID]ORDER BY [valPerson].[PersonName];

Thanks,
for your help and tips!

 

[Rustaferd]

Here is my attempt at the code I am trying to achieve:

SELECT [valPerson].[PersonName] AS Name,[valPerson].[PersonID] AS ID, [valCompany].[CompanyName], [valPerson].[PersonPhoneNumber], [valPerson].[PersonPhoneExt], [valPerson].[PersonTypeDescription], [valPerson].[PersonIntlPhoneNumber], [valPerson].[PersonEmailAddress1] FROM [valPerson] INNER JOIN [valCompany] ON [valPerson].[PersonCompanyID]= [valCompany].[CompanyID] INNER JOIN [valPersonType] ON [valPerson].[PersonTypeID]= [valPerson].[PersonTypeID] ORDER BY [valPerson].[PersonName];

I must be off just a bit because I am still getting syntax errors.

I would attach my data files if you would care to take a look, but this site doesn't seem to have that function. In the Main form there is a subform call MainCaller. If you go into properties of this subform you will see the Record source. This is were I am getting syntax errors when trying to run this by switching to Form view.

I also have some other strange occurences if you care to tackle this msyter - the subform is suppose to display the data for whichever caller was selected from the list, but this doesn't occur. The subform disappears...of course because I've been playing with teh above sql code other things are not working...

It is indeed a learning process *whew*

Thanks for your help!

 

[zemp]

What is your syntax error? I think it could be in the following section

...INNER JOIN [valPersonType] ON [valPersonType].[PersonTypeID]= [valPerson].[PersonTypeID] ORDER...

You are using Access VBA. Unfortunatly I can't help you much there. You would probably be best to post the second part of your question in one of the Access forums. Try forum705.

Take Care,

zemp

"If the grass looks greener... it's probably because there is more manure."