A couple of questions... Please help (1 Viewer)

Johnsonsimon

Registered User.
Local time
Today, 03:59
Joined
May 5, 2012
Messages
45
Hello all,

This is my first post here so please be gentle, although been browsing for a while I apologise for jumping in at the deep end, but I have been googling and searching around everywhere and can't find anything. It could be that I am searching for the wrong thing or asking in the wrong way but I really can't find anything.

The DB I am building is for my father's currently small chauffeur company in order for them to input the details of the jobs to have a record of future, and completed jobs and also to produce printed and PDF job sheets for each job.

This is how I currently have the DB setup;
Tables:
Client
Driver
Vehicle
Jobs

Each of these tables has its own form setup and all works great.

My issue is with a feature request that my dad's business partner has put forward.
The client table has fields; CLIENT ID, CLIENT NAME, CLIENT CONTACT NAME, CLIENT CONTACT NUMBER, ADDRESS (lines 1-3), POSTAL CODE.
What he has requested is to be able to enter more than one CLIENT CONTACT NAME & CLIENT CONTACT NUMBER for each client, as often with big companies there is more than one person who books jobs. They do not wish to simply have a seperately client for each client contact. Is this possible. Would it require a separate table or can it be done directly in the client table.

When booking a job they go to the job booking form which allows them to choose the client from a combo box, with the above issue fixed they would wish to choose the client from a combo box still and then choose the contact person for this client from another combo box which would then also show this persons contact number.


The second question is of a similar vain to the first, also in the jobs booking form there are text boxes LEAD PASSENGER NAME & LEAD PASSENGER CONTACT NUMBER, which are currently linked to txt fields in the jobs table. What they wish to be able to do is be able to choose the leade passenger from a combo box, but it needs to be client specific (filtered?), based on the client chosen from the CLIENT combo box. When they choose the lead passenger it should also display their contact telephone number.
E.g. They choose 'Company X' from a combo box, and then the Lead passenger combo box is populated with passenger names for that particular client.

I hope that makes some sort of sense. I can post a link to the DB if it would help at all.

Or I can answer any questions to try to help anyone understand my request more clearly.

I really appreciate the help in advance.

Simon
 

Guus2005

AWF VIP
Local time
Today, 04:59
Joined
Jun 26, 2007
Messages
2,645
First, how many years experience do you have working with Access or as a programmer and which Access Version do you use?

I am trying to understand the process.

The CLIENT is the company and the CLIENT_CONTACT_NAME is the person who calls and makes a reservation with your dads firm?
The caller makes a reservation for the LEAD_PASSENGER and tells the number of people and the destination of the trip?

If so, you need to make a small database change. You need to split CLIENT and CONTACT from the CLIENT table.
A CONTACT belongs to one CLIENT and the CLIENT has more than one CONTACT.

In the combobox CLIENT you can select the company and in the combobox CONTACT you can select the contacts belonging to that company.

The LEAD PASSENGER is like a CONTACT and it belongs to the CLIENT.
You could say that a PASSENGER is a special kind of CONTACT.
In your CONTACT table you can reflect the difference between a CONTACT and a PASSENGER by using a Yes/No field. If you call it Passenger and the value is true this record is a passenger. If not a regular contact.

HTH:D
 

Johnsonsimon

Registered User.
Local time
Today, 03:59
Joined
May 5, 2012
Messages
45
Guus2005, thanks for the swift response.

I am using Access 2010, I have used access a number of times before, but not really to do anything other than basic projects when I was doing my A levels. I can be pretty adept at picking things up if they are explained properly. I am not really fluent in any programming languages but given enough reading of them I can generally get what each line of code is trying to achieve etc.

The way you described above if how I was considering looking at it.

Yes the CLIENT is the company (or individual if no company is involved).
The CLIENT_CONTACT_NAME & CLIENT_CONTACT_NUMBER are the details of the people who call up to make the booking.
They call and make a reservations and give the details of the lead passenger, the LEAD_PASSENGER-NAME & LEAD_PASSENGER_CONTACT_NUMBER.

Each client effectively needs to have an unlimited number of contacts and unlimited number of lead passengers.

Would it be best to just add 2 new tables: CONTACT & PASSENGERS, or do as yoiu suggested with the Yes/No Box. I presume these would need to have primary keys defined (e.g. CONTACT ID, or PASSENGER ID?)

AS when booking the job on the Job Booking form, they will need to be able to select the client name from a combo box, and then the CONTACT and then the LEAD PASSENGER, but will also if possible need to be able to add names to these lists from the Job Booking Form..

Hope that makes sense.
 
Last edited:

Guus2005

AWF VIP
Local time
Today, 04:59
Joined
Jun 26, 2007
Messages
2,645
Yes, it is possible to add two tables and leaving out the yes/no field. But as long as the fields are the same for both tables you can combine the two and add the yes/no field.
I would go for the combined version. But that's your call. It doesn't interfere with the rest of your application.
In both cases a company(CLIENT) can have an unlimited number of contacts and passengers.

If you set the combobox property limit-to-list to False an event is triggered the moment you enter a value in the combobox that isn't in the list yet.
Using that event you can open a form which allows you to add the new passenger or contact.

HTH:D
 

Johnsonsimon

Registered User.
Local time
Today, 03:59
Joined
May 5, 2012
Messages
45
Legend...

Thankyou.

Primary Key such as CONTACT or PASSENGER ID number required for the new table(s)?

The new table would you just add the fields; CONTACT_ID, CONTACT_NAME & CONTACT_PHONE_NUMBER, or do I need to add a field to link them to the client, i.e. CLIENT_ID number (to link to the client ID in the CLIENT table, or am I over complicating.
 

Guus2005

AWF VIP
Local time
Today, 04:59
Joined
Jun 26, 2007
Messages
2,645
Two scenarios:

One table scenario:
Tablename:CONTACT
Fields:
ID, autonumber
Name, text
Telephone, number or text
Passenger, Yes/No

Two table scenario:
Tablename:CONTACT
Fields:
ID, autonumber
Name, text
Telephone, number or text

Tablename:pASSENGER
Fields:
ID, autonumber
Name, text
Telephone, number or text

Two scenarios for the Passenger Combobox recordsource:
One Table Scenario:
Select ID, Name from CONTACT where Passenger = True;

Two table scenario:
Select ID, Name from PASSENGER;

One table scenario saves space when you have a lot of Contacts/Passengers.

HTH:D
 

Johnsonsimon

Registered User.
Local time
Today, 03:59
Joined
May 5, 2012
Messages
45
thats great thank you

the only issue i have is how to make these client specific,

so on the CLIENT form you could see a list of all the CONTACT persons and their respective telephone numbers, and a separate list of LEAD PASSENGERS and their respective telephone numbers.

and then on the JOB Booking FORM when you select the CLIENT from the drop down, e.g.. CLIENT X, and it will show a combo box with all the associated CONTACTS and another combo box with all the associated LEAD PASSENGERS, also with the ability to add to this list, or manually overtype if it is a non-reguar client.

Cheers again
 
Last edited:

Guus2005

AWF VIP
Local time
Today, 04:59
Joined
Jun 26, 2007
Messages
2,645
Ok, A picture says more than a thousand words, now it's time to view your relationship diagram.

Can you post/put a link to a sample of your database? FE/BE?

I haven't seen a problem that couldn't be solved in Access and your problem is not difficult. I just need to see what we're talking about.

HTH:D
 

Johnsonsimon

Registered User.
Local time
Today, 03:59
Joined
May 5, 2012
Messages
45
Hi Guus,

I cannot post links as I have not made enough posts on the forum yet. Will try to send it in a PM.

I have not added the new table(s) as discussed as I wasn't sure how the links would need to work.

I hope you can assist.

Cheers
 

Johnsonsimon

Registered User.
Local time
Today, 03:59
Joined
May 5, 2012
Messages
45
ok it appears you can't PM on this forum either,

if you are able to message me or whatever, I will reply or give me your email and I will post you the link to my dropbox.

Cheers

Simon
 

spikepl

Eledittingent Beliped
Local time
Today, 04:59
Joined
Nov 3, 2010
Messages
6,144
Post your stuff here (not links) - few ppl want to click on external links and also the stuff is lost for posterity. READ the first post (sticky) at the top of this forum!
 

Johnsonsimon

Registered User.
Local time
Today, 03:59
Joined
May 5, 2012
Messages
45
ok db attached now, finally figured that one out.

hope someone can help.

cheers again
 

Attachments

  • SJE Job Database v1.1 new structure.accdb.zip
    531.5 KB · Views: 122

bob fitz

AWF VIP
Local time
Today, 03:59
Joined
May 23, 2011
Messages
4,713
If you can attach a copy of your db in mdb format, you may get more response. Some of us are still using A2003 and are unable to open accdb files.
 

Johnsonsimon

Registered User.
Local time
Today, 03:59
Joined
May 5, 2012
Messages
45
I just tried to save it as a 2003 version, but it says that it can't as there are features I have used that are unsupported in access 2003.

so hopefully someone with 2010 or 2007 can help, or if someone can point out how to override this error with saving as 2003 i will happily post a 2003 version of it.

cheers again
 

Guus2005

AWF VIP
Local time
Today, 04:59
Joined
Jun 26, 2007
Messages
2,645
you can create a 2003 Access database (also in 2007 or 2010), open it and import all items from the 2007/2010 version database. This way you'll get a bigger audience.

Later today i'll have a look at your database and see how you problem can be fixed easily.

Posting your database in a 2003 format, you'll might get a faster response from someone else.

HTH:D
 

Johnsonsimon

Registered User.
Local time
Today, 03:59
Joined
May 5, 2012
Messages
45
Any luck with that Guus?

I had another look at trying to save as a 2003 database vsinut really couldn't figure that one out.

Cheers
 

Johnsonsimon

Registered User.
Local time
Today, 03:59
Joined
May 5, 2012
Messages
45
does anybody have any ideas with this one?
as regards the original problem... also if someone can talk me through saving the DB as an access 2003 version so more people can have a look at it I would really appreciate it.

Thank you so much for all your help so far.

Cheers Si
 

Guus2005

AWF VIP
Local time
Today, 04:59
Joined
Jun 26, 2007
Messages
2,645
Hi Johnsonsimon,

I've looked at your relationship diagram and see that it isn't properly normalised.

You've split the tbl_Job into three different tables where you should use only one.
Just add a Status field which contains whether a job was cancelled, active, completed or even Deleted.

I'd call "Frequent Locations", just "tblLocation"

I've added a tblContact, a query qryContact which shows all contacts and qryPassenger which shows all Passengers. These queries can be used in your comboboxes.
I've added ClientID to connect Contact and Passenger to the Client.

Let me know what you think. Nice menu!

HTH:D
 

Attachments

  • SJE Job Database v1.1 new structure.zip
    524.5 KB · Views: 81

Users who are viewing this thread

Top Bottom