gthe two contact Ids would need to be linked in some way though so the integrity stays ok e
PK is the Primary key field . This is a unique identifier for that record. Most frequently they are an autonumber type field.
FK is the Foreign Key field. This is where you store the PK of the related record from the parent table.
So in your example above the CustomerID is Unique in the customer table, but is saved in the call logging table as a foreign key value to identify the customer by simply storing that unique ID every time the customer calls.
Ditto the contact ID.
Like I demonstrated you can duplicate the tables in the relationship window to show where multiple relationships exist.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
cheers Minty- so being an absolute newbie with abasic understanding - how does the relationship get establised if i dont make them a relationship.
in my new layout ive picked up the key fields.
I removed the black line do i now set up a reatisohip by duplicaing the table- apologies if this is tiresome
Simply drag the field from one table to the other. Then repeat for the duplicated table to the other related table. Right click the join line to set the type.
Relationships are important, but don't get carried away with enforcing referential integrity at this point.
That can follow once you have the data correctly stored.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
Before you get carried away with forms, make sure your data storage can do everything you want it to, and that the reports you want can be pulled from that data.
Get a pen and paper out, or whiteboard, draw your design out and "move" some trial data around in it.
Make absolutely sure everything you need can be achieved via pen and paper first.
If it can't then your design isn't complete.
If the above isn't working all the sexy forms in the world won't help.
Forms and UI are actually the final piece of the puzzle.
Redesigning them after you add another couple of vital fields you forgot, or worse change the whole structure, is a real pain, and creates lots of extra work.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
Very good advice-i was getting used to the design functions as well - just knocking some rough edges off me.
so the tough bit as far as i will be concerned will be getting a result of
1) creating a function whereby i get a phone call list- an email follow up list by date - or daily
2) creating a to do list of actions to follow up - and/or need to be completed
ive taken your advice on board and will sit back and try to resolve the data requirements before i plough further in!!
any other tips advice etc will be greatly received - eg sorting by dates havent looked at that yet
If your data is stored correctly, then the items you have listed above will be trivial to resolve.
One thing in your design, try and avoid storing calculated results or data.
So for instance if you have a follow-up date for a customer that is based around the initial or latest contact for that case, don't store it, calculate it.
That way you don't need to try and capture every point that your last "thing" was updated to try and update the stored value.
It's a common mistake that you see frequently with new users.
As an idea - you could store a default follow up interval in the customer record, and use that in your queries to generate the lists you mention.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
O I get it so follow up date - last contact date plus 5 days for example
Yes that's the sort of thing.
Not storing redundant information and not duplicating information, are both important in good database design.
It's commonly known as Normalisation - have a good read if you aren't familiar.
In fact have a good look here http://www.rogersaccesslibrary.com/forum/forum46.html
and follow the tutorial examples. It will take a few hours in total, but they are really good.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
Hi - its been a while but i have a fucntional database that ive started to populate.
there area couple of refinements I now find I could use.
the relationship table above isnt quite right- what I need is to add a table that shows indivual call logs to the same customercontact ID?
I assume I add a table like the below relationship ( id remove the comments field from the contact log and use the new table to record comments?
if so i would need a add new comment button
I think I may need a redesign to achieve what i want
Yes, a few tweaks.
First, you have a 1-to-1 relationship between tables Comments and CallLogtblNEW. There is no reason for this. It limits the call log to one comment.
One CallLogtblNEW (record) can have many comments and one Comment is for one CallLogtblNEW. This is a 1-to-many relationship.
Next, in table TblCustomerContactsNew, I would have have a contact first name and a contact last name.
I would also have a table for "Roles".
Consider something like this
I do not know what some of the fields are used for (especially in tblCallLogs), but the diagram above might help you to move forward.
Hi Master!
yes I rebuilt it and deleted some records that were just pointless goign forward-looks neater now and the reprots ive looked at seem to do what i want.
one point - ive added the role( ive called product category as the guys i deal with manage different areas eg toys gifts torches etc . how do I set the customer contact so I can update more than 1 product cat for the one customer-ive set it as yes no duplicates - but cant seem to desing the for to enable me to do this - do i just keep processing the add record button.
so when i search through my contacts where the #name it would be great to see a list of these areas ( or do i design a new screen for this to send it out and use the current form as data entry )