advice on Table/form setup 1

[SimonCleaver]

I have a form for my customer details.
I need to record when a brochure was sent to a customer. I have more than 20 different brochures and a customer may receive one, all, some or none of these.
I also need to be able to run a report/query for each brochure manufacturer to tell them which customers have been sent their specific brochure.

So I need to store on a customer record:
1) which brochure(s) they have been sent
2) the date this was sent
3) to be able to send a regular report to the manufacturer a list of customers (but not repeating customers details on subsequents lists) presumably by running a query.

Up to now I have put 20 date fields on my customer table, each named by the brochure title. So when I send a customer a brochure i put the date in this field.
I have then 20 individual queries each asking for a list of customers by date (criteria being: the day after the last date the query was run TO todays date).
This works ok - but each time I want to run the queries I have to manually change the criteria dates in all the queries.
I was thinking about putting another YES/NO field next to the dates boxes to specify that the registration had been made and then the query could be: select all records that have ANY date but do not have a YES in the yes /no field. But then I need to manually change all these records to update the yes/no field to a yes. If I use an updatequery, I have to make 20 different update queries.
Confused? so am I!!!! Any advice is very gratefully received.

 

[KenReay]

Hi

You need some extra tables:

A table of brochure names

tblBrochures
strBrochureName PK
strSupplier (maybe?)

a Table of Brochures sent

tblBrochuresSent
CustomerId )
strBrochureName ) PK
datDateSent )

On your Customer form, add a sub form which is based on the tblBrochureSent, and will allow you to record a list of 'n' brochures sent to a given customer

Amend you report to work from a query based on tblBrochuresSent and Customers (joined on CustomerId)



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[SimonCleaver]

Thanks Ken,

I thought I might need more tables.
This may be a daft question, but when I create a new table for brochures sent - how do I create/specify the link to the CustomerID and the brochurename?

I tried to use the Relationship but, but seem to go wrong somewhere.

Thanks for the help

 

[KenReay]

Hi

There would not be a single relationship

There would be a one to many from CustomerId of the Customer table to customerId of the BrochuresSent table, similarly there would be a one to many relationship from the Brochures table (Brochurename) to the BrochuresSent table (Brochurename)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[SimonCleaver]

Thanks again Ken,

When I try to make these relationships, the CustomerID to Customer Id will set as a One-Many, but the BrochureName to BrochureName will only link as a one-one relationship, whichever of the 3 options i choose, it still says one - one.
By the way - do you know if you can you make attachments in this forum?

 

[KenReay]

Hi

Check the indexes on your tables, I would suspect you have a unique index on BrochuresSent on Brochure name

No you cannot send attachemnts as far as I know, I assume you were going to ask to send your db?

I can give you my EMail address kenneth.reayREMOVETHIS@talk21.com, but be warned I am on duty tonight taking my wife out for a meal, so sitting at the computer will be frowned upon!

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[SimonCleaver]

Thanks again Ken,

I wouldn't dream of interrupting, ha ha - Im not that brave!!!! I think my wife is hoping for similar !!!!!

I have created a brand new database using your ideas (I will then use the concept on my database if I can get it to work how I want - my live database is quite big and has loads more tables, etc that aren't related to this problem).

I hope you don't mind by I have emailed the new simple one to you so you can have a look. I had to use my hotmail account because if I use outlook and you need to email back you may need to change the extension as Outlook won't let me received attachments with a .mdb extension (presumably Bill Gates thinks it could be a cyber terrorist !!!!!!)

 

[KenReay]

Hi Simon

Having looked at your sample, you had the CustomerId in the customer table as an autonumber, and the CustomerId in the Brochures sent table as a string (it should be a Numeric long) so you will have dificulties decalaring a relationship between such disimilar column types.







Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[SimonCleaver]

Hi Ken,

Thanks very much for the help.
Just one more thing, if I'm not being to cheeky.
If I wanted to have the brochures selected via a lookuop box (combo or list) - would I need to make another table to store the brichure names or can this be included in the existing one?

 

[KenReay]

Hi

Difficult to answer this one without asking more questions!

Is you database multi user?

Is your database split into a frontend/backend

Answers to these may impact waht you actually do.

I would normally use a split FE/BE, so I would have a local table of selected brochures, and use the Brochure table as the list of available Brochures. You can use queries to make it so that the two tables are joined in queries, to present a list of selected brochures, and a list of unselected brochures. Sorry my skills of explanation are not up to putting it in words, if you are willing to wait until I have time I can send you an example ?


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[SimonCleaver]

Thanks Ken,

Sorry to keep using your time like this !!!

The database is used by 2 users on a network.
It is not split in any way.

If you like I can send you a copy of the real database I have created - so you can see all my novice like creations/actions, ha, ha.

Thanks very much for you help