vba???

[Lucy02]

Hi i'm not really sure what i'm meant to be doing. In a previous query post I was told that I would need to use a VBA code to update a temporary table as two tables that I wanted to link together are not in a one-to- many relationship. Can anyone give me any help as to how I do this? I am a newbie to access so I'm not really sure what it means! If I havent explained very well please let me know and I will fill the gaps in!

Thanks

 

[Mile-O]

Describe the tables, please.

Also, why don't you redesign them so that they are related properly? That would save a lot of fuss and future work.

 

[Lucy02]

Ok i have two tables one called telemarketing and one called appointments. Both tables have fields called Company Name Day Date and Month so each table will show the company which was called or had an appointment and the day, date and month which it took place. What I want to find out is which companies had both telemarketing and appointments and on what days. I have been told that a query won't show this but a report will if i set up a one-to-many relationship between the two tables. However, this is not the case they are a many-to-many relationship and therefore i have been told that i need to use a VBA code to update a temporary table to show all the calls and appointments of one company in one row but i do not have a clue how to do this??? Can you help at all??

many thanks

 

[Mile-O]

Based on that information, I would suggest you really only require one table and less fields than you have. Although a few other tables would be recommeneded.

i.e.

tblCompanies
CompanyID
CompanyName

tblContacts
ContactID
CompanyID
ContactTypeID
DateOfContact

tblContactTypes
ContactTypeID
ContactType

You would join the fields with the same name to its equal in the other tables to create the one-to-many joins.

The third table, tblContactTypes, has two fields - an autonumber for indexing and a contact type. In each row you can put the contact types such as meeting, telemarketing, complaint, etc. That way you are always prepared for future additions without having to redesign anything.

The company would go in its own table too with any other information you want to keep about it i.e. phone numbers, contact names, address, etc.

The middle table, tblContacts, is where you store your details.

Using a query you can now select the details you want from that one table alone and bind it to the report. No VBA needed.

 

[Lucy02]

ah you're a star i'm going to give it a go now thankyou so much!!!

 

[Mile-O]

Bear in mind that you don't need fields for Day and Month because you are storing the DateOfContact. With this date, you can extract the day and month: it saves space and prevents errors.

 

[Lucy02]

Hi sorry me again,

I have tried your suggestion but everytime I try to run the query or bind the query to a report it tells me that i have mismatched expression? Also there are alot of companies which means that i would need to remember all of the codes for each company. Is there a way of using a drop down box when entering the data so that I pick the company name and the company Id automatically enters itself in?

Sorry to be a pain but i really do appreciate this!

 

[Lucy02]

HA HA Please ignore my last post I have done it hurrah!!! I mis read your instructions. Thankyou soo much for all of your help!!!!!

 

[Mile-O]

So, the bold and italic fields should be Autonumbers and primary keys; and the ones in red (foreign keys) should be Number fields.