Linking Tables and Queries

  • Thread starter Thread starter Scott Viney
  • Start date Start date
S

Scott Viney

Afternoon All,

After reading on the Access Web about lookup tables and their evils I have
removed them. Now I have a table for Titles (IE Mr, Mrs etc) and another
table for Departments (IE Cundinamarca, Meta, Cordoba etc)

In my relationships my tblEmployees is connected 1 to many with
tblDepartments and also with tblTitles, also my tblCustomers is connected 1
to many with tblDepartments and also with tblTitles.

Now when I go to create a select query with all my tables including
tblDepartments and tblTitles I get no records, but if I remove both of these
tables from the query, it works fine.

Can someone explain where Iam going wrong please, if I cant use them in my
query how do I get the department name and title?

Have a good one,
Scott V
 
Scott,

There is absolutely nothing wrong with lookup tables. In fact they are
an essential element in the design of many databases. What many object
to is lookup *fields*, which is an entirely different matter! It sounds
like you might have been using lookup fields for the Title and
Department fields in your tblEmployees table. This means the actual
data that has been entered into these fields might be a "hidden" ID
number or some such, rather than the data which corresponds with the
data in your lookup tables. What are the fields and data in the
tblTitles and tblDepartments tables? What is the data in the Title and
Department fields in the tblEmployees table?
 
Steve,

Yup I was using lookup fields for the departments and titles. But removed
them.

tblDepartment
DepartmentID Auto
DepartmentName Text

tblTitle
TitleID Auto
Title Text

tblEmployee (There are more fields but you get the idea)
EmployeeID Auto
TitleID Number
FName Text
LName Text
OfficeID Number

tblOffice (There are more fields but you get the idea)
OfficeID Auto
Address Text
City City
Department DepartmentID

tblCustomer (There are more fields but you get the idea)
CustomerID Auto
CustomerType Number
CompanyName Text
TitleID Number
FName Text
LName Text
DepartmentID Number

Thanks,

Scott V
 
Scott,

Well, I wouldn't do it like that myself. Are there more than one record
in the tblTitle table with the same entry in the Title field? No? So I
wouldn't have a TitleID field, what's the point? The Title data is
unique, I imagine. So that would mean having the Title text data in the
Title field in the tblEmployee table, etc. Same goes for Department.
But anyway, don't want to confuse the issue! As far as I can see, the
way you have it at the moment should work. For example, if the actual
data in the tblTitle table is like this:
TitleID Title
1 Mr
2 Mrs
etc

.... and the actual data in the TitleID field in the tblEmployee table is
1 or 2 or etc., then making a query that includes both the tblTitle and
tblEmployee tables, joined on the TitleID field from each, should work
to return the matching records. Can you check with this simple example
first? Then we can track down the source of the problem step by step.
 
Steve,

No worries, only way to learn is make mistakes. This is the second
database. If there is a better way I would be glad to hear it.

Ok I have checked tblCustomer-TitleID = Number, DepartamentID=Number
tblEmployee-TitleID=Number
tblOffice-DepartmentID=Number

Just to be sure I went to the form for each and changed each to see that it
updates in the tables. Yup, no probs.

Cheers,
Scott
 
Scott,

Very good. What I was trying to suggest before was to make a query, as
an experiment, that includes both the tblTitle and tblEmployee tables,
joined on the TitleID field from each, and see if it returns the
expected records.
 
Steve,

This has me stumped. I have done the suggested experiment using those two
tables, not worry. When I go further it stuffs up.

Is it possible to sent a jpg of my links etc.

Cheers,
Scott V
 
Scott,

Rather than a jpg, if you could go to the SQL view of the query (via the
View menu in query design) and copy/paste the SQL into your post here.
I assume "stuffs up" means no data returned?
 
Steve,

Thanks for the help, I really think its a fundamental problem in the way i
have the tables connected now.

Here is the sql for the query, its a query to get all the information I need
to do the report

SELECT tblCustomer.CustomerID, tblCustomer.CustomerType,
tblCustomer.CompanyName, tblCustomer.CompanyNIT, tblCustomer.TitleID,
tblCustomer.FirstName, tblCustomer.MiddleName, tblCustomer.LastName,
tblCustomer.Cedula, tblCustomer.BillingAddress, tblCustomer.City,
tblCustomer.DepartamentoID, tblCustomer.[Country/Region],
tblCustomer.PhoneNumber, tblCustomer.Extension, tblCustomer.FaxNumber,
tblCustomer.MobilePhone, tblCustomer.EmailAddress,
tblQuotations.QuotationID, tblQuotations.EmployeeID, tblEmployee.TitleID,
tblEmployee.FirstName, tblEmployee.MiddleName, tblEmployee.LastName,
tblEmployee.OfficeID, tblOffice.Address, tblOffice.City,
tblOffice.DepartamentoID, tblOffice.[Country/Region], tblOffice.PhoneNumber,
tblOffice.FaxNumber, tblOffice.EmailAddress, tblQuotations.QuotationDate,
tblQuotations.QuotationValidity, tblQuotations.QuotationPayment,
tblQuotations.QuotationDelivery, tblQuotationDetails.QuotationDetailID,
tblQuotationDetails.ProductID, tblBrand.BrandName,
tblProducts.ProductDescription, tblPresentation.PresentationType,
tblProducts.ProductSize, tblProducts.ProductBrandCode,
tblProducts.ProductSapiensCode, tblQuotationDetails.Quantity,
tblQuotationDetails.QuotationProductVRUnit,
tblQuotationDetails.QuotationProductIVA,
([QuotationProductVRUnit]+[QuotationProductIVA]) AS [VRUnit+IVA],
tblQuotationDetails!Quantity*([QuotationProductVRUnit]+[QuotationProductIVA]
) AS TotalPrice, tblTitle.*, tblDepartmentos.*
FROM tblTitle INNER JOIN ((tblEmployee INNER JOIN ((tblDepartmentos INNER
JOIN (tblCustomer INNER JOIN tblQuotations ON tblCustomer.CustomerID =
tblQuotations.CustomerID) ON tblDepartmentos.DepartamentoID =
tblCustomer.DepartamentoID) INNER JOIN tblOffice ON
tblDepartmentos.DepartamentoID = tblOffice.DepartamentoID) ON
(tblOffice.OfficeID = tblEmployee.OfficeID) AND (tblEmployee.EmployeeID =
tblQuotations.EmployeeID)) INNER JOIN ((tblPresentation INNER JOIN (tblBrand
INNER JOIN tblProducts ON tblBrand.BrandID = tblProducts.BrandID) ON
tblPresentation.PresentationID = tblProducts.PresentationID) INNER JOIN
tblQuotationDetails ON tblProducts.ProductID =
tblQuotationDetails.ProductID) ON tblQuotations.QuotationID =
tblQuotationDetails.QuotationID) ON (tblTitle.TitleID = tblEmployee.TitleID)
AND (tblTitle.TitleID = tblCustomer.TitleID)
WHERE (((tblCustomer.CustomerID)=[Forms]![frmCustomer]![CustomerID]) AND
((tblQuotations.QuotationID)=[Forms]![frmCustomer]![sfrmQuotations].[Form]![
QuotationID]));

I hope reading all this doesnt make your eyes bugout,

Scott
 
Scott,

I don't know if this is the only problem, but if you go to the design
view of your query, you will need to add a separate instance of the
tblTitle and tblDepartmentos tables for each time you want to use it.
The way it is at the moment, it assumes the Title of the Employee will
always be the same as the Title of the Customer, and that the Department
of the Employee's Office will always be the same as the Department of
the Customer. Er, are you sure the Customer has a Department?

Of course, following by initial suggestion of totally scrapping the idea
of the redundant TitleID and DepartamentoID fileds would immediately
eliminate the problem anyway :-)

--
Steve Schapel, Microsoft Access MVP

Scott said:
Steve,

Thanks for the help, I really think its a fundamental problem in the way i
have the tables connected now.

Here is the sql for the query, its a query to get all the information I need
to do the report

SELECT tblCustomer.CustomerID, tblCustomer.CustomerType,
tblCustomer.CompanyName, tblCustomer.CompanyNIT, tblCustomer.TitleID,
tblCustomer.FirstName, tblCustomer.MiddleName, tblCustomer.LastName,
tblCustomer.Cedula, tblCustomer.BillingAddress, tblCustomer.City,
tblCustomer.DepartamentoID, tblCustomer.[Country/Region],
tblCustomer.PhoneNumber, tblCustomer.Extension, tblCustomer.FaxNumber,
tblCustomer.MobilePhone, tblCustomer.EmailAddress,
tblQuotations.QuotationID, tblQuotations.EmployeeID, tblEmployee.TitleID,
tblEmployee.FirstName, tblEmployee.MiddleName, tblEmployee.LastName,
tblEmployee.OfficeID, tblOffice.Address, tblOffice.City,
tblOffice.DepartamentoID, tblOffice.[Country/Region], tblOffice.PhoneNumber,
tblOffice.FaxNumber, tblOffice.EmailAddress, tblQuotations.QuotationDate,
tblQuotations.QuotationValidity, tblQuotations.QuotationPayment,
tblQuotations.QuotationDelivery, tblQuotationDetails.QuotationDetailID,
tblQuotationDetails.ProductID, tblBrand.BrandName,
tblProducts.ProductDescription, tblPresentation.PresentationType,
tblProducts.ProductSize, tblProducts.ProductBrandCode,
tblProducts.ProductSapiensCode, tblQuotationDetails.Quantity,
tblQuotationDetails.QuotationProductVRUnit,
tblQuotationDetails.QuotationProductIVA,
([QuotationProductVRUnit]+[QuotationProductIVA]) AS [VRUnit+IVA],
tblQuotationDetails!Quantity*([QuotationProductVRUnit]+[QuotationProductIVA]
) AS TotalPrice, tblTitle.*, tblDepartmentos.*
FROM tblTitle INNER JOIN ((tblEmployee INNER JOIN ((tblDepartmentos INNER
JOIN (tblCustomer INNER JOIN tblQuotations ON tblCustomer.CustomerID =
tblQuotations.CustomerID) ON tblDepartmentos.DepartamentoID =
tblCustomer.DepartamentoID) INNER JOIN tblOffice ON
tblDepartmentos.DepartamentoID = tblOffice.DepartamentoID) ON
(tblOffice.OfficeID = tblEmployee.OfficeID) AND (tblEmployee.EmployeeID =
tblQuotations.EmployeeID)) INNER JOIN ((tblPresentation INNER JOIN (tblBrand
INNER JOIN tblProducts ON tblBrand.BrandID = tblProducts.BrandID) ON
tblPresentation.PresentationID = tblProducts.PresentationID) INNER JOIN
tblQuotationDetails ON tblProducts.ProductID =
tblQuotationDetails.ProductID) ON tblQuotations.QuotationID =
tblQuotationDetails.QuotationID) ON (tblTitle.TitleID = tblEmployee.TitleID)
AND (tblTitle.TitleID = tblCustomer.TitleID)
WHERE (((tblCustomer.CustomerID)=[Forms]![frmCustomer]![CustomerID]) AND
((tblQuotations.QuotationID)=[Forms]![frmCustomer]![sfrmQuotations].[Form]![
QuotationID]));

I hope reading all this doesnt make your eyes bugout,

Scott
Click to expand...
 
Steve,

Scrapping idea sounds like the easiest option. But can you explain to me
what is the best way to do it

I dont want the users to type in the Title. Why? Because they will do what
ever they want, maybe abbreviations etc
The less they have to do the better. Also there are 32 departments in
Colombia South America, so it would be good if
they could choose them some how.

If you could point me in the best direction Ill have a crack at it.

Cheers,
Scott V

Steve Schapel said:
Scott,

I don't know if this is the only problem, but if you go to the design view
of your query, you will need to add a separate instance of the tblTitle
and tblDepartmentos tables for each time you want to use it. The way it is
at the moment, it assumes the Title of the Employee will always be the
same as the Title of the Customer, and that the Department of the
Employee's Office will always be the same as the Department of the
Customer. Er, are you sure the Customer has a Department?

Of course, following by initial suggestion of totally scrapping the idea
of the redundant TitleID and DepartamentoID fileds would immediately
eliminate the problem anyway :-)

--
Steve Schapel, Microsoft Access MVP

Scott said:
Steve,

Thanks for the help, I really think its a fundamental problem in the way
i
have the tables connected now.

Here is the sql for the query, its a query to get all the information I
need
to do the report

SELECT tblCustomer.CustomerID, tblCustomer.CustomerType,
tblCustomer.CompanyName, tblCustomer.CompanyNIT, tblCustomer.TitleID,
tblCustomer.FirstName, tblCustomer.MiddleName, tblCustomer.LastName,
tblCustomer.Cedula, tblCustomer.BillingAddress, tblCustomer.City,
tblCustomer.DepartamentoID, tblCustomer.[Country/Region],
tblCustomer.PhoneNumber, tblCustomer.Extension, tblCustomer.FaxNumber,
tblCustomer.MobilePhone, tblCustomer.EmailAddress,
tblQuotations.QuotationID, tblQuotations.EmployeeID, tblEmployee.TitleID,
tblEmployee.FirstName, tblEmployee.MiddleName, tblEmployee.LastName,
tblEmployee.OfficeID, tblOffice.Address, tblOffice.City,
tblOffice.DepartamentoID, tblOffice.[Country/Region],
tblOffice.PhoneNumber,
tblOffice.FaxNumber, tblOffice.EmailAddress, tblQuotations.QuotationDate,
tblQuotations.QuotationValidity, tblQuotations.QuotationPayment,
tblQuotations.QuotationDelivery, tblQuotationDetails.QuotationDetailID,
tblQuotationDetails.ProductID, tblBrand.BrandName,
tblProducts.ProductDescription, tblPresentation.PresentationType,
tblProducts.ProductSize, tblProducts.ProductBrandCode,
tblProducts.ProductSapiensCode, tblQuotationDetails.Quantity,
tblQuotationDetails.QuotationProductVRUnit,
tblQuotationDetails.QuotationProductIVA,
([QuotationProductVRUnit]+[QuotationProductIVA]) AS [VRUnit+IVA],
tblQuotationDetails!Quantity*([QuotationProductVRUnit]+[QuotationProductIVA]
) AS TotalPrice, tblTitle.*, tblDepartmentos.*
FROM tblTitle INNER JOIN ((tblEmployee INNER JOIN ((tblDepartmentos INNER
JOIN (tblCustomer INNER JOIN tblQuotations ON tblCustomer.CustomerID =
tblQuotations.CustomerID) ON tblDepartmentos.DepartamentoID =
tblCustomer.DepartamentoID) INNER JOIN tblOffice ON
tblDepartmentos.DepartamentoID = tblOffice.DepartamentoID) ON
(tblOffice.OfficeID = tblEmployee.OfficeID) AND (tblEmployee.EmployeeID =
tblQuotations.EmployeeID)) INNER JOIN ((tblPresentation INNER JOIN
(tblBrand
INNER JOIN tblProducts ON tblBrand.BrandID = tblProducts.BrandID) ON
tblPresentation.PresentationID = tblProducts.PresentationID) INNER JOIN
tblQuotationDetails ON tblProducts.ProductID =
tblQuotationDetails.ProductID) ON tblQuotations.QuotationID =
tblQuotationDetails.QuotationID) ON (tblTitle.TitleID =
tblEmployee.TitleID)
AND (tblTitle.TitleID = tblCustomer.TitleID)
WHERE (((tblCustomer.CustomerID)=[Forms]![frmCustomer]![CustomerID]) AND
((tblQuotations.QuotationID)=[Forms]![frmCustomer]![sfrmQuotations].[Form]![
QuotationID]));

I hope reading all this doesnt make your eyes bugout,

Scott
Click to expand...
Click to expand...
 
Scott,

Sure. You can still have a Titles table and a Departments table. Just
have one field in these tables, i.e. a text field with the name of the
Title or the name of the Department. Then you can use these tables as
the row source of your comboboxes on the form for the data entry. The
major difference being that the fields in the main tables where this is
being entered will now be text data type, and hold the actual name of
the department etc, rather than any ID. Make sense? In this model, of
course, there is then no need for the tblTitle or tblDeparmentos to be
included in any queries, as there's nothing to look up... the correct
data's already there.
 
Back
Top