2 roles for my customer id causes problems

[antonyx]

ok i have an issue.

my user will enter customer details.. eg name phone.. and the primary key is customer id.

there are also job details, pickup address, no. of passengers.. etc.. and the primary key is job id.

assigning a job to a customer is done is this way.

job id
customer id

my problem is this however. the person booking the job is also a customer and will also be assigned to the job using the same method.

when i create a query however, i cant display both the customer name and the booker name in the same query as both use the customer id... is this clear enough..

basically i need a method that allows me to assign customers from the same table to the same job but as 2 roles.

 

[SkipVought]

Hi,

What tables/keys do you have? You have not define this.

Skip,

_{[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE!}

 

[antonyx]

ok well..

i have a job table which is

job id (primary key)
job date
job time


then a customer table

customer id (primary key)
customer name
customer phone


then i tried doing something like this

job customer table
job id
customer id (with caption 'for customer')

also with a..

job booker table
job id
customer id (with caption 'booked by')

now this system worked.. it told the database which customer booked the job and which customer had the job. however, when i tried to make a query.. as you can imagine.. when i dragged customer name into the query.. if i included the job customer table and the job table, then it would show the customer, and if i included the job booker table and the job table it would display the booker.

but there is no way to display both the customer and booker name along with the job.

because on my job customer and job booker forms, i use a drop down list to assign the customer to the job (with the customer name in the drop down and it saves the record as the customer id)

with my above system aswell, if i want to see the booker and the customer in the same query then it only displays the id numbers.. which is no good to my user in the report.

 

[SkipVought]

Cust table, jobs table and JobRole table
[tt]
JobID CustID Role
A C2 Booker
A C1 Customer
[/tt]

SELECT J1.JobName
, C1.CustName As Booker
, C2.CustName As Customer

FROM `D:\My Documents\Stuff\Jobs`.`Customers$` C1
, `D:\My Documents\Stuff\Jobs`.`Customers$` C2
, `D:\My Documents\Stuff\Jobs`.`JobRole$` JR1
, `D:\My Documents\Stuff\Jobs`.`JobRole$` JR2
, `D:\My Documents\Stuff\Jobs`.`Jobs$` J1
, `D:\My Documents\Stuff\Jobs`.`Jobs$` J2


WHERE C1.CustID = JR1.CustID 
  AND JR1.JobID = J1.JobID 
  AND C2.CustID = JR2.CustID 
  AND J2.JobID = JR2.JobID
  and JR1.Role='Booker'
  and JR2.Role='Customer'

Skip,

_{[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE!}

 

[antonyx]

i think you have mistaken me for someone who would completely understand the steps that need to be taken to make your code work.

i think i realise this..

have a job role table.. with 3 fields, job id, cust id, and role

so the records will be like

1,1,booker
1,2,customer

OR if the same customer books the job for themselves.

1,1,booker
1,1,customer

if you could just explain to me in simple terms how i can edit a query to make sure that the names of both the customer and booker are displayed.

 

[MajP]

Make a query from your job customer table, linked to your customer table by customer ID. Now your query should include these fields. You can drop the customer ID for customer name.

qryJob_Roles
jobID
customer Name
Role

Now you can use the crosstab query wizard to build a cross tab query from the above select query. The wizard will ask for a row field, pick job ID. Then it will ask for column headers, select "Role". Then it will ask for value field pick "customer name" and choose "first". If you go to sql view it will look like this.

TRANSFORM First(qryJobRoles.strName) AS Name
SELECT qryJobRoles.jobID
FROM qryJobRoles
GROUP BY qryJobRoles.jobID
PIVOT qryJobRoles.role;

This will work as long as you only have a max of two records per job. This will not work if you can have many people book the same job or be a customer. This will not work:

1,1,booker
1,1,customer
1,7 booker
1,8 customer

Skips sql should work for the above case with more that 2 bookers or customers; however, that a tough one for me and I have written a lot of queries.

 

[Lilliabeth]

Won't this work:

tblCustomers
tblJobs
tblJobsCustomers

In the query, add tblCustomers twice.

Change the joins in the query:

Join one instance of tblCustomer to tblJobCustomers on CustomerID-CustomerID.

Join the other instance of tblCustomers to tblJobCustomers on CustomerID-BookerID.

 

[antonyx]

http://bb.1asphost.com/antonyx6/queryproblem.jpg

lillia, i think i needed a solution a bit simpler so i chose your one.. i have done what i think u suggested.. if you look tho i can still only display 1 name per job from the customer table, even if i link the tables in the query like you said..


im confused.

 

[MajP]

Lillabeth,
That will not work (or work well) because he has two or more job records. His table is not set up like you suggest. For that to work he would need a table like,

tblJobsCustomers
intJobID
intBrookerID
intCustomerID

but that is not what he has.

However, if Jobs have people with no other roles than Customers and Brookers, and a job can only have one custmer and one brooker than this is a better table structure. I think this is what Lilibeth was suggesting

Antonyx,
If the above table makes sense for your rules than use the above table which is more normalized, than Lillibeth solution will work easily and well.



He could build a query like you said that shows.
Job
person name
persons role

Which will show multiple records for each job. In some of the records you will see a name

 

[Lilliabeth]

im confused.

Me too.

I looked at the link you provided, and either I am missing something or you just need to include the name field from the customer_1 table.

How many customers can a job have?
How many bookers can a job have?

 

[antonyx]

i) a job will only ever have one booker

ii)a job may have one or more customers


both the bookers and the customers will be stored in the customer table (as they are both customers in a sense)

i did what you said and placed the other name in the query and that did display the correct results

new query design is here..
http://bb.1asphost.com/antonyx6/queryproblem.jpg

so would this be a good way to do things then for rules "i" and "ii" above

 

[Lilliabeth]

If a job can have only one booker, why not put the bookerid in the job table?

Need a table for jobcustomers so you can enter multiple customers for a job.

So, you could have 3 tables:
job
customer
jobcustomer


Still add the customer table to the query twice. One joined to bookerid in the job table. The other joined to customerid in the jobcustomer tables.

 

[antonyx]

ok thanks for that.

 

[MajP]

Lillabeth and Antonyx,

That approach still does not make sense. If that is the case, you can have many customers and one booker the table should be:

tblJob_Role
intJobID
intPersonID
strRole

Booker and Customer are merely people roles. Do not make a booker key. That is non-normalized.

The query then links intPersonID to the poorly named Customer table (which is reall the Person table)

Now you have just a typical many to many relationship

The query then shows all Jobs, and all people with their role for that job.

 

[antonyx]

yes that is a better way Maj, but there are other problems

you see i dont assign my customer to the job, i assign them to the pickup or destination, because one job pickup may have many customers. and a destination may also need to drop off 2 or more customers.

this is why, even tho having a booker id in the job table is not normalised, i mite have to do that anyway, because the job will simply have one booker per job.. and the customers will belong to the pickups and destinations which are linked to the job..

obviously you wont fully understand because you cannot see my full table relationships.. realistically tho how problematic will it be having the booker in the job table?

 

[MajP]

No actually there is no problem, now that I think I see what you are doing. Lillabeth was correct, it is the naming convention that confused me. Actually if may be the more logical how she said to do it. It really depends on buisness rules.

If there is only ever one booker to a job, then it is logical to put that booker key in the job table. It is even more logical if a job requires a booker.

If jobs have one or more customers, but no additional roles to worry about then a Job_Customer table is correct.

joinTblJob_Customer
jobID
Person ID

The thing that threw me was that we were calling the table that had people names in it a customers table, but using it to refer to Bookers and Customers. It really is a Person Table.
So as Lillabeth said put a booker ID in the Job table that points to a person in the Persons Table a one to many relationship. And then have a many to many relationship with the joinTblJob_Customer. Just do me a favor and change the name from tblCustomers to tblPersons.

 

[antonyx]

ok no worries

 

[Lilliabeth]

Yes, his naming convention (or lack of it) was very confusing. But I felt like if I didn't adhere to it I would just confuse things even more.

 

[antonyx]

ok..

remember my situation, one job will have one booker but may have many customers..and the bookers and customers both derive from the person table.

with a query layou like so i am able to see the bookers and the customers for each job and it works fine..

http://bb.1asphost.com/antonyx6/prob1.jpg

however.. even tho i realise parameters are not the best way to go about things.. i want my user to be able to enter a phone number from my main form and view the details for that customer..

my problem is however that to see whether a person has been a customer.. you need a query like so..
http://bb.1asphost.com/antonyx6/prob2.jpg

and to see whether a person has been a booker... you need a query like so..
http://bb.1asphost.com/antonyx6/prob3.jpg

so i tried doin something like this..
http://bb.1asphost.com/antonyx6/prob4.jpg

but this gave me blank results.. basically i want to reshape this query so it allows me to search the job details.. in the form of a phone number parameter.. and through that one phone number, tell me whether a person has been a booker or a customer...

i hope that is clear.

 

[PHV]

You have to use an OR logic, not an AND.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886