Showing full names (1 Viewer)

[jamiecalvert]

If any one could help me with this problem I would appreciate it.

An overall salesperson is assigned to a customer but there may be individual products sold to that customer by different salespersons. One customer can buy many products.

Tables are:
CUSTOMER
CustomerID
SalespesonID

SALESPERSON
SalespersonID

PRODUCT
ProductID
CustomerID
SalespersonID

There are of course other fields. Names are shown in two fields Firstname & LastName.

I have a number of queries I am building to base reports on. Most of these use the Product table. The problem is that when producing reports I do not have the Clients or Salesperson name shown, only their ID number. I know why this is -whilst the look-up may return a name, the ID behing is a number - the ID number. I additionally wanted the names shown. I then added to my queries an expression which combined the Salespersons first & last name. OK to this point. Any report produced the Salespersons full name as well as their ID. When I added another expression combining the Customers first & last names, either all on the records dissappeared once the query had run, or I could not run it because their were ambigious joins. I have tried the help options but cannot seen to write any expressions which work, either in the query or reports.

I am a new user & apologise if I am missing something basic. I would appreciate any help.

 

[Rich]

Sometimes it's easier to add the customers first and last name etc. individually to the query grid and then combine them on a textbox in the report/form

 

[Pat Hartman]

It would be easier to determine your problem if you would post the SQL.

 

[jamiecalvert]

Thanks Pat & Rich for getting back.

I have managed to solve my problem. I created a Product Query which combined the salespersons name. This is used as the basis for a number of other queries. When queries require the Customers name I add the Customer ID from the Customers table when using the wizard to create the new query. In the query I can then write an expression which combines the customers first & last names. This is then used as the basis for any reports.

For some reason, I cannot add the Client field and then write the expression to combine customers first & last names in my original Product Query & received the errror messages described earlier.

 

[Pat Hartman]

It is clearly a syntax error. However if you don't post the sQL, no one will be able to find it.

 

[jamiecalvert]

Pat,

The SQL statement was:

SELECT [Product Details].PolDetailsID, [Product Details].CustomerID, [Product Details].SalespersonID, [Forename] & " " & [Surname] AS FULLSALEPERSONNAME, [Product Details].PolStatus, [Product Details].ProductType, [Product Details].PolWritCommission, [Product Details].PolNotes
FROM Salespersons INNER JOIN [Product Details] ON Salespersons.SalespersonID = [Product Details].SalespersonID
WHERE ((([Product Details].ProductType)="ISALS" Or ([Product Details].ProductType)="ISAR" Or ([Product Details].ProductType)="PEP" Or ([Product Details].ProductType)="WPBI" Or ([Product Details].ProductType)="WPBG" Or ([Product Details].ProductType)="DB" Or ([Product Details].ProductType)="UT"));

If I then added to the query the Customer table, and added and expression FULLCUSTOMERNAME: [CForename] & " " & [CSurname] (these are the field names), I get the error message saying there are ambigious joins, and I get asked to create a separate query that first performs the first join, and then incorporate it in this query.

I am new to Access, so presume I have been looking at wrong. Would really appreciate advice on where I may have gone wrong.

 

[Pat Hartman]

I don't think that the customer name fields are in the salespersons table or the product details table. They are probably in the customers table. The SQL you have posted does not include the customers table so this is NOT the SQL that doesn't work. I would guess that the customer table needs to be joined to the product details table. Since you are getting an error regarding a join that I CANNOT SEE because you didn't post the SQL, I will assume that to be the problem.

 

[RV]

(Of topic)

A smaal remark on your "full salespersons name" query, use the IN operator for your where clause:

WHERE ((([Product Details].ProductType)="ISALS" Or ([Product Details].ProductType)="ISAR" Or ([Product Details].ProductType)="PEP" Or ([Product Details].ProductType)="WPBI" Or ([Product Details].ProductType)="WPBG" Or ([Product Details].ProductType)="DB" Or ([Product Details].ProductType)="UT"));

becomes

WHERE [Product Details].ProductType
IN ("ISALS","ISAR", "PEP", "WPBI, "WPBG","DB","UT");

RV

 

[jamiecalvert]

Pat,

I apologise if I have not made things clear this end.

The customer fields are not in my salesperson table and are in the Customers table. The SQL I posted was the one I was working with. I knew it did not contact the Customer name fields and therefore tried to add this table into my query. It was when I added the Customer table, and then the Customer name fields, Access saild it could not run the query due to ambigious joins and I get asked to create a separate query that first performs the first join, and then incorporate it in this query. I have no other SQL to post because I could not change the one I posted to incorporate the customer fields. I do of course have the new one which works (my relpy 7/11). Reading your reply I suspect I needed a join between the customer and products table.