Query Question

[Guest]

I have a database and a spreadsheet.
The spreadsheet contains a customer list. I need to make a report with other
data.
This data comes from the database.

I have imported the spreadsheet into the database as a table.

I link the first and last name to eachother and run the new query.
I get various duplicates. The only other field I can think of linking is
the purchase date. The only problem is that the purchase date is written as,
4/18/2006 in the spreadsheet, and the database has 3 different tables for the
date, ex. date1=month, date2=day, date3=year.

Other than manually looking up each account, is there a way I can make my
search easier?

 

[Jeff Boyce]

If your query is returning multiple rows when you match by name, I'll guess
that you either have "duplicate" names, or the table contains more than one
row per person.

Do you have any "more unique" identifier? After all, how many "John Smith"
entries might your data have? <g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Graham Mandeno]

Why are you storing the date as three separate fields? There is a perfectly
good data type (Date/Time) for storing dates in a single field.

Also, are the duplicate names actually different people with the same name
or are they multiple purchases made by the same person? Do you have any way
to tell?

You should have two separate tables for customers and purchases. Each
customer should have only a single record in the customers table with a
unique primary key. Purchases made by that customer are identified by
having that primary key value (a foreign key) in a field in the purchases
table.

It seems you have some basic design issues here. Perhaps it would help if
you were to post some more details about you design and what the application
is required to achieve.

 

[Guest]

Unfortunately, the separate fields are needed for the order entry system. I
can not change that at all. The only way I could possibly do it is separate
the date field in excel.

No, the duplicates can be people with the same name or different instances
of that person. Does that help?

Why are you storing the date as three separate fields? There is a perfectly
good data type (Date/Time) for storing dates in a single field.

Also, are the duplicate names actually different people with the same name
or are they multiple purchases made by the same person? Do you have any way
to tell?

You should have two separate tables for customers and purchases. Each
customer should have only a single record in the customers table with a
unique primary key. Purchases made by that customer are identified by
having that primary key value (a foreign key) in a field in the purchases
table.

It seems you have some basic design issues here. Perhaps it would help if
you were to post some more details about you design and what the application
is required to achieve.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a database and a spreadsheet.
The spreadsheet contains a customer list. I need to make a report with
other
data.
This data comes from the database.

I have imported the spreadsheet into the database as a table.

I link the first and last name to eachother and run the new query.
I get various duplicates. The only other field I can think of linking is
the purchase date. The only problem is that the purchase date is written
as,
4/18/2006 in the spreadsheet, and the database has 3 different tables for
the
date, ex. date1=month, date2=day, date3=year.

Other than manually looking up each account, is there a way I can make my
search easier?

 

[Graham Mandeno]

You can use the DateSerial function to create a date from three numbers:

DateSerial( yr, mth, day )

or in your case:

DateSerial( [date3], [date1], [date2] )

Unless you have unique customer IDs, I can't see how you can distinguish
between a purchase by one John Smith and another John Smith, and even if you
have dates, that doesn't help if they both make a purchase on the same day.
You will still need to deal with duplicates.

Actually, rereading your original post, I'm not entirely sure what the
problem is. What is it you are searching for?
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Unfortunately, the separate fields are needed for the order entry system.
I
can not change that at all. The only way I could possibly do it is
separate
the date field in excel.

No, the duplicates can be people with the same name or different instances
of that person. Does that help?

Why are you storing the date as three separate fields? There is a
perfectly
good data type (Date/Time) for storing dates in a single field.

Also, are the duplicate names actually different people with the same
name
or are they multiple purchases made by the same person? Do you have any
way
to tell?

You should have two separate tables for customers and purchases. Each
customer should have only a single record in the customers table with a
unique primary key. Purchases made by that customer are identified by
having that primary key value (a foreign key) in a field in the purchases
table.

It seems you have some basic design issues here. Perhaps it would help if
you were to post some more details about you design and what the
application
is required to achieve.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a database and a spreadsheet.
The spreadsheet contains a customer list. I need to make a report with
other
data.
This data comes from the database.

I have imported the spreadsheet into the database as a table.

I link the first and last name to eachother and run the new query.
I get various duplicates. The only other field I can think of linking
is
the purchase date. The only problem is that the purchase date is
written
as,
4/18/2006 in the spreadsheet, and the database has 3 different tables
for
the
date, ex. date1=month, date2=day, date3=year.

Other than manually looking up each account, is there a way I can make
my
search easier?

 

[Guest]

I'm trying to generate a report based on the information from the spreadsheet
and database. Unfortunately, when I import the spreadsheet and then query
the information. I get various duplicates. The only common fields are the
first and last name. The date is an extra field I'm trying to work on,
because two people could go have purchased on the same day, but it may not be
necessarily true.

You can use the DateSerial function to create a date from three numbers:

DateSerial( yr, mth, day )

or in your case:

DateSerial( [date3], [date1], [date2] )

Unless you have unique customer IDs, I can't see how you can distinguish
between a purchase by one John Smith and another John Smith, and even if you
have dates, that doesn't help if they both make a purchase on the same day.
You will still need to deal with duplicates.

Actually, rereading your original post, I'm not entirely sure what the
problem is. What is it you are searching for?
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Unfortunately, the separate fields are needed for the order entry system.
I
can not change that at all. The only way I could possibly do it is
separate
the date field in excel.

No, the duplicates can be people with the same name or different instances
of that person. Does that help?

Why are you storing the date as three separate fields? There is a
perfectly
good data type (Date/Time) for storing dates in a single field.

Also, are the duplicate names actually different people with the same
name
or are they multiple purchases made by the same person? Do you have any
way
to tell?

You should have two separate tables for customers and purchases. Each
customer should have only a single record in the customers table with a
unique primary key. Purchases made by that customer are identified by
having that primary key value (a foreign key) in a field in the purchases
table.

It seems you have some basic design issues here. Perhaps it would help if
you were to post some more details about you design and what the
application
is required to achieve.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a database and a spreadsheet.
The spreadsheet contains a customer list. I need to make a report with
other
data.
This data comes from the database.

I have imported the spreadsheet into the database as a table.

I link the first and last name to eachother and run the new query.
I get various duplicates. The only other field I can think of linking
is
the purchase date. The only problem is that the purchase date is
written
as,
4/18/2006 in the spreadsheet, and the database has 3 different tables
for
the
date, ex. date1=month, date2=day, date3=year.

Other than manually looking up each account, is there a way I can make
my
search easier?

 

[Graham Mandeno]

I'm not following you. Are you saying that the duplicates are not in the
data, but you are seeing them in the query?

Or are you saying the duplicates ARE in the data and you don't want to see
them in the query?

If it's the latter then you can use the predicate DISTINCT in your query:

Select DISTINCT <field list> from ...

If it's the former, then there is something wrong with the design of your
query. Please post the SQL of the query so that we can have a shot at
finding the problem.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm trying to generate a report based on the information from the
spreadsheet
and database. Unfortunately, when I import the spreadsheet and then query
the information. I get various duplicates. The only common fields are the
first and last name. The date is an extra field I'm trying to work on,
because two people could go have purchased on the same day, but it may not
be
necessarily true.

You can use the DateSerial function to create a date from three numbers:

DateSerial( yr, mth, day )

or in your case:

DateSerial( [date3], [date1], [date2] )

Unless you have unique customer IDs, I can't see how you can distinguish
between a purchase by one John Smith and another John Smith, and even if
you
have dates, that doesn't help if they both make a purchase on the same
day.
You will still need to deal with duplicates.

Actually, rereading your original post, I'm not entirely sure what the
problem is. What is it you are searching for?
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Unfortunately, the separate fields are needed for the order entry
system.
I
can not change that at all. The only way I could possibly do it is
separate
the date field in excel.

No, the duplicates can be people with the same name or different
instances
of that person. Does that help?

:

Why are you storing the date as three separate fields? There is a
perfectly
good data type (Date/Time) for storing dates in a single field.

Also, are the duplicate names actually different people with the same
name
or are they multiple purchases made by the same person? Do you have
any
way
to tell?

You should have two separate tables for customers and purchases. Each
customer should have only a single record in the customers table with
a
unique primary key. Purchases made by that customer are identified by
having that primary key value (a foreign key) in a field in the
purchases
table.

It seems you have some basic design issues here. Perhaps it would help
if
you were to post some more details about you design and what the
application
is required to achieve.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a database and a spreadsheet.
The spreadsheet contains a customer list. I need to make a report
with
other
data.
This data comes from the database.

I have imported the spreadsheet into the database as a table.

I link the first and last name to eachother and run the new query.
I get various duplicates. The only other field I can think of
linking
is
the purchase date. The only problem is that the purchase date is
written
as,
4/18/2006 in the spreadsheet, and the database has 3 different
tables
for
the
date, ex. date1=month, date2=day, date3=year.

Other than manually looking up each account, is there a way I can
make
my
search easier?