insert date in duplicate fields

[sam*]

Hiya All

i have just registered today but i cant find what im looking for.

i have a database that sends out reports to our customers when they get made an offer, then what i do is add current date to indicate i have sent the report.
but when i get the same customer again i dont want to send a report again.

what i want to do is if i get another same customer how can i pick that up and insert the date that the letter was sent out.

 

[sam*]

is there no one that can help????

 

[Bat17]

Hmmm, The details of your setup is a bit skimpy which makes it a bit awkward to give much of an anwser.
Assuming that you have a table with the customer name and a date field that holds the date the report was sent and that that field will be empty if no report has been sent. You could use a DlookUp() function to pull the data from that field so that you can see it. how you trigger the DlookUp rather depends on your setup

peter

 

[sam*]

not quite sure what you mean could you give me more explanation please

ill write the problem out again maybe it will clear things up but i really need some help on this

if i have a main table with customer no and postdate
if customer no has been sent a letter a date is added.
if the same customer no gets added again in main table i need to get the date the letter was sent and insert that date in same customer.

thanks

 

[Bat17]

Are you adding the data through a Form?
How is the 'customer' defined? does each customer have a unique number that can be used as a reference? First Name + Last Name? but then how do you handle two customers with the same name?

Peter

 

[sam*]

I add the date with an udate query
I give the customer an auto id number

 

[Bat17]

Adding the date should be easy enough, it is deciding if it is a new customer or an old one that we need to sort out.

Do you all ready have a sytem to make sure the same customer does not get put in twice, getting 2 ID's

Peter

 

[sam*]

Here is a sample database
I have to have duplicate customers and i usually have thousands of records so i cant just manual add the date if already added

 

[Bat17]

OK
If you are not entering the data with forms we cant check it at the point of data entery
I have added a couple of queries which should update the table for you with the date previously used.
Make sure that you test it out on dummy data first though

HTH

Peter

 

[sam*]

Thanks that worked fine in the test data
But when im using it in the real database, its not working
it selects the right fields but doesnt insert the date
any ideas why??

 

[sam*]

ive figured why its doing that
its becos it selects the the first field data in post date and just puts that date in all the others, so if the first field is blank then its a blank insert
which is not giving me the right dates
any ideas how i can get around this!!!

 

[Bat17]

The answer really is down to database design. this should really be in its own table so that each customer is only in there once, or possible a table with a report field and Customer customer field as a joint key. that way when you bring out a new catalog you can add the dates for that one seperately.

If you are not to fussed what dates you have you could tweak the query I posted to add todays date quite easily by just changing the Update To field to Date()

I will have a look to see if I can come up with a proper solution though.

Peter

 

[Bat17]

try changing the Update To expression to
DMax("[post date]","Main","[customer number]=" & [qryWithDates]![customer number])

Peter

 

[sam*]

ahhh thanks a lot thats working all fine