Need a little help...

[GregG2006]

My general database knowledge is very limited, but I'm currently working on a project that I will need to finish relatively soon.

I am currently working with my friend's customer database. He desires to send out anniversary cards to his customers. Currently I have two tables (queries). One table consists of all his customers for the past year (2005) and another table consists of all his customers for the two years prior to last (2003 & 2004).
All the fields are identical in both tables.

What I need to do is create a final list consisting of those customers who my friend has done business with in 2005 and a list of those customers he did business with in 2003 & 2004.

The problem is that every client should receive only one card. One for those he's done business with in the past year and one for those who he has done business with the two years before that. I don't know what operation to perform (or query?) on both tables.

Each table has date, name, address, and so on. What I need to eliminate is the duplication of a 2005 past year customer in the 2003-2004 table.

Can anyone assist me?

Greg

 

[carash77ash]

Why don't you create on table that has all the information and have a field on that table that has the date you desire.

Then you can create a query that filters the record by date.

Ash

 

[RV]

One table consists of all his customers for the past year (2005) and another table consists of all his customers for the two years prior to last (2003 & 2004)

Not really the best approach
Combine your tables into one, skip the year column as it's totally irrelevant.

RV

 

[carash77ash]

how would you suggest RV to sort the customers if you don't have another field that lets you know what group they are in.

Ash

 

[RV]

how would you suggest RV to sort the customers if you don't have another field that lets you know what group they are in.

Ash

Ash,

you're missing the point.
Year is not really a customer's property.
What's got sorting to do with that, nothing I'd say

RV

 

[carash77ash]

RV

Year is just a name for a column, you could call it group or you could call pink fluffy bunny, I applied the name year as in year he did buisness with customer. That way you could run a query eg show me all customers did buisness with in year 200?

What I need to do is create a final list consisting of those customers who my friend has done business with in 2005 and a list of those customers he did business with in 2003 & 2004.

Otherwise with no grouping all you would have is a customer field with no way to sort data.

Ash

 

[AngelsGuardian]

Greg.. you could also merge your 2 table and add a field "yes/no" ..a checkbox if you prefere and set no to all "non active" customers...

 

[GregG2006]

Thanks for your help...

What I need to know how to do is how to merge these two tables (all with the same fields) so that I have one table consisting of both the customers who placed orders in 2005 and the customers who placed orders in 2003&2004.

Since I have a date field (which is actually when the order was received) for each record, when I merge these two tables I will have customers listed more than once probably. So there may be a customer who placed an order in 2003 or 2004 as well as 2005. If that is the case, I will want to retain the customer record that contains the 2005 date and eliminate the customer's record for 2003 or 2004.

First, I need to know how to merge my two tables in Access.

Second, I need to know how remove all 2003&2004 entries for a customer IF that customer has a 2005 entry.

Then I can perform a query to weed out the 2003&2004 group and the 2005 group. The 2003&2004 group will be mailed one announcement and the 2005 will be mailed another. I need to do all of the above so that those people who did business with my friend's company in 2005 AND 2003/2004 DON'T receive BOTH announcements because of duplication. There are simply too many records to go over manually.

Thanks,
Greg

 

[RV]

RV

Year is just a name for a column, you could call it group or you could call pink fluffy bunny, I applied the name year as in year he did buisness with customer. That way you could run a query eg show me all customers did buisness with in year 200?

Otherwise with no grouping all you would have is a customer field with no way to sort data.

Ash

Ash,

no offence but I think you don't know what you're talking 'bout
A customer is either active or not, that's what you should register , storing years doesn't service any purposes.

RV

 

[carash77ash]

RV

You shouldn't drink if it makes you nasty.

Combine your tables into one, skip the year column as it's totally irrelevant

was your original post that suggested just to combine the two tables. Now your suggestion is:

A customer is either active or not, that's what you should register

Which was my original point that you needed a field to distinguish between the customer data.

Date was just a suggestion. AngelsGuardian then offered another way to group which i believe is a better way to sort.

Why not in your orginal post did you not suggest this because perhaps it may be you that

no offence but I think you don't know what you're talking 'bout

Ash

 

[AngelsGuardian]

Do you have LOTS of customers in those tables ...or is it under 100 ?

 

[GregG2006]

The 2005 table (query) has 745 records. The 2003&2004 table has 1135.

Thanks,
Greg