Access Query to Find First Time Contacts

[staciann]

Hi. I have an Access database and one of the tables holds records for all of the quotations that have been requested. Fields include: Company, FirstName, LastName, QuoteDescription, etc.

Is there any way that I can write a query to find records that would be requests from potential customers (people who have requested quotes) that have not previously contacted us (Combo of "FirstName", "LastName" & "Company" does not appear anywhere else in the table).

Is this possible? If so how?

Thank you in advance for your help,
Staci

 

[genomon]

SELECT name1, name2 FROM whatever
WHERE COUNT(name1) = 1 might work....

Did you hear about the Buddhist who refused Novocain during a root
canal? He wanted to transcend dental medication.

 

[staciann]

I think I figured it out - I did a find duplicates query and changed the >1 to <2. But now I have another problem:

I am comparing the data in that query to another table that has old requests in it (2003-2006) by creating a second query. It knocks another 20 or so records out of the query - which is perfect - exactly what I wanted it to do.

The problem is that it won't let me alter any data from the second query so that it reflects on the original table (2007 requests). I would like to check a box from that query that would represent the record being a first time contact. I brought this field in from the original table. I have the first table and queries all joined by a Request Number. I'm guessing it's because I've brought a second table into the second query. Is there any way around this? There is no way to link the two tables together (the type of data is similar, but all of the records are different - leaving no two things the same to be linked.)

 

[SkipVought]

Hi,

So you are considering that if there is..
[tt]
John Doe from Company A
Richard Roe from Company A
[/tt]
that you have two "first time" contacts?

"The problem is that..." and you go on to state a problem, that is NOT the real problem. The REAL PROBLEM is that you have chopped your data up, arbitrarily, by YEARS. IF this were data that 1) was bloating the database and adversly affectin performance and 2) was merely archive-type data (meaning that it will rarely if every be accessed), then that data ought to be transferred to another table. But data from the past few years is valuable historical data that ought to be readily accessible, without having to do grimaces and handstands, We have data going back more than 10 years in some cases.


Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[staciann]

Well, what happened is that we used to hand type in our requests into a table (old table). We updated our website this year so that the requests sent in through the website will automatically go into a table (new table). The way the web coding was set up, the new records (from this year)couldn't be put into the old table because different fields were added/changed.

Pretty much, the information collected this year is different than the information that was collected for the past 4 years - that is why there are 2 different tables.

The FirstName and LastName fields are the only ones that I need to cross-reference and that information has been collected in both tables.

 

[SkipVought]

"The FirstName and LastName fields are the only ones that I need to cross-reference and that information has been collected in both tables. "

Then that's your answer, isn't it?

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[staciann]

I don't know how to cross reference them - that's the problem. That's what I'm asking how to do.

Can I create a query that would pull out anything from Table 2 that already appears in Table 1 and then be able to check the "First Time Contact" box in that query for the records that are left?

 

[SkipVought]

Select Last, First, OldCommonField1, NewTextField, NewNumericField ....
From NewTable

UNION

Select Last, First, OldCommonField1, '', 0 ....
From OldTable

Field positions must line up. Easiest to put your common fields first, followed by the new fields; but notice that there must be a placeholder in the seconf query,

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}