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

[Johnsonsimon]

Hi Guus,

Thanks for the works you've done and thanks for the compliment regarding the design (to be honest that is more my area). I will have a proper look at what you've added/changed etc.. I'm sure I will have questions...

but thanks for now, i really appreciate it.

Si

 

[Johnsonsimon]

Hi Guus,

I have had a look at the changes you made now and as I suspected I do have some questions... so sorry to be a pain. If you can't answer any or I am asking too much then please just say.

Questions...
Location Table
The way you have set it, would it allow an adhoc address (ones not in list) to be entered through the Jobs&Booking Form?

Client Table
The tblContact has a link to the Client via the ClientID, in the Client form will it be possible to have a view where you can enter 'CONTACTS' for the current client and have it show up like a data sheet view?
And in the Jobs&Booking Form when you choose CLIENT from the combo box, it will show another combo box with the CONTACTS for that client, which has the option to edit if necessary.

Also the same as above for Passenger on Jobs&Booking Form.

Contact Table
How do the Passenger and Passenger+Alternative fields works? in relation to the form.

Job Table
How does the Status field work? in relation to the form
I presume I could use the existing Completed and Cancelled Jobs Forms but linked to the now single Jobs table via a query??

Thank you so much for all your help

Simon

 

[Guus2005]

Wow, one answer raises so many other questions

I haven't looked at your forms yet.
You can enter values that are not in the combobox list like Contact or Address by programming the OnNotInList event. I believe the Northwind database has a few samples about working with comboboxes.

In the database i returned there are two queries. qryContact and qryPassenger.
They are both connected to the same tblContact but they show a different result.

The tblJob with its added Status field can do the same.
A query like this:

Select * from tblJob where Status = Completed

Will show completed jobs only. The syntax of this query will not pass the parser but i think you get the general idea.

Probably this weekend i will look at the rest of your questions and perhaps answer them.

HTH

 

[Johnsonsimon]

Cheers Guus,

Really appreciate the help

 

[cplmckenzie]

Question...

Have you resolved this issue yet.

Just downloaded your db, will set up testing environment.

Post if not resolved.

cplmckenzie

 

[Johnsonsimon]

Hi cplmckenzie,

Nope I still haven't resolved. Guus2005 is still looking into it, but I would still appreciate any assistance you can provide.

Cheers

Simon

 

[Guus2005]

I've seen that you use a lot of macro's.
There are only two macro's i use: AutoKeys and AutoExec.

The first one to redirect keystrokes and the second one to start a procedure to execute the program.
I hope you're willing to dive into the world of VBA coding.

HTH

 

[Johnsonsimon]

I would be happy to dive into the world of VBA coding, I have done a tiny bit in the past during my degree, had to build a very simple game using vba, but thats hidden somewhere in the depths of my memory.

If I can achieve the same or better functionality with VBA (which i suspect I can), then I have no issue with using it to replace the macros, but I wouldn't know where to start.

 

[Johnsonsimon]

cplmckenzie or guus2005, have either of you gotten anywhere at all,

no pressure intended by this message, I am just completely stuck and just want to keep the topic alive, hope you both don't mind.

thank you to all for the help, i really do appreciate it

 

[cplmckenzie]

Johnsonsimon,

Just finishing up some work I am helping on the Tech Support Guy forum.

Will be looking into your issue over the coming weekend.

BTW. Nice design of your database. It flows smoothly.

cplmckenzie

 

[Johnsonsimon]

thankyou so much,

thanks for the compliemtns on the design, as I said to guus2005, design is more my forte than programming, but I am always looking to further my skills, so here we are with me building a database for my father's company.

si

 

[cplmckenzie]

johnsonsimon,

Trying to get on the same page as you.

Look at the attached image, let me know if my thinking is following your needs.

cplmckenzie

 

[Johnsonsimon]

Hi cplmckenzie,

Finally got my head around the diagram you sent, was trying to view it on a phone initially and was hard to read so I got confused.

Yes that is basically what I need it to do. Although a separate form for CONTACTS is not necessarily needed as long as you could add CONTACTS for a particular client on the CLIENTS form.

Also on the Jobs & Booking form they need to be able to chose the CLIENT from a drop down and then when the client is chosen a list of drop down box of CONTACTS for that client.

Also I was discussing with guus2005 that they would like the option for lead passenger to be client specific also, which I presume would work along the same principle as the CONTACTS. This info would not need to be entered on the CLIENT screen so a separate form for the lead passenger which somehow links to a particular client would be useful.

Hope that makes sense.

Thanks again for the help.
Simon

 

[cplmckenzie]

johnsonsimon,
The second image I posted shows a more detailed description of what could possibly work.
Not a separate form for contacts, but a subform of Your current client form, in datasheet view which would populate a tbl_Contacts table with the data linked as image2.
Let me know your thoughts

cplmckenzie

 

[cplmckenzie]

johnsonsimen,

Also in image2 there is a design relationship for the Jobs and Bookings concern.

I could, however, use a more clear understanding of "a lead passenger" entity.

cplmckenzie

 

[Johnsonsimon]

I only see 1 image that you posted?

Ok I will try to explain the "lead passenger" entity a little better.
Basically when a job is booked a person from company X calls up. This is the CLIENT and CONTACT elements. During the booking they give the name of the lead passenger for the job. Each job has details of the lead passenger, that is the person who is to be contact when the driver turns up to pick them up. Often jobs have more than 1 passenger but this doesn't matter they just need to contact details of a lead passenger who is technically "responsible". Each CLIENT often makes bookings for the same passenger on a regular basis, i.e. the Managing Director going to the airport or something similar.

Hope that makes sense
Si

 

[cplmckenzie]

johnsonsimon,

image2 attached.

cplmckenzie

 

[Johnsonsimon]

maybe i should stick to design in the future, haha.

i think i get the relationship diagram, but not 100% with it, how would the new table (tb;_Contact_job) be populated?

Have you altered my database with the noted changes that you could post here? if not don't worry I am sure I will get there lol.

 

[Guus2005]

There are two ways of setting the relationships for your database.

You can store the clients id in the job table (like the one above) or you can store the contact id in the job table (below).

It depends whether or not the client is the main attribute in your table design (relationship 1). If it is the contact you can choose relationship 2.

I would choose the latter.

No need to create any extra tables.

Enjoy!

 

[cplmckenzie]

This modified version of your db is an attempt to add unlimited contacts
and contact info a specific client.
This is just an example in the simpilest way to accomplish the following,
per your posts.....
-------------------------------------------------------------------------
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.
-------------------------------------------------------------------------
A few changes have been made to the design layout of your
Clients Form ... with the addition of a subform to add contacts
Jobs.etcForm ... the contact area is now a combobox that is
populated when a new Client is selected.
tbl_Clients ... no longer has a contact field as that is now
linked to the ClientID field in the newly added
tbl_Add Contacts.
A few other mods have been made, minor error checking put in.
New VBA code and queries have been written and created.
Not understanding fully how your database runs.
These mods simply addresses your first concern.
As I did not look at the design layouts of the other poster, these mods
adhered to the to design changes from the 2 images I posted.
Test it by creating a directory C:\test3 and place the db there.
This can be changed if it works the way you spoke of, also some cleanup
still needed but not wanting to put more time in it if it will now work
for you.
Post if the changes are usable, will cleanup and address the lead
passenger if you want.
Will also document all modifications made from your original design.
cplmckenzie