Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Query Question

Discussion in 'Information Technology' started by metaltecks, Jul 28, 2006.

Tags:
  1. metaltecks

    metaltecks
    Expand Collapse
    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?
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    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


    "metaltecks" <metaltecks@discussions.microsoft.com> wrote in message
    news:3CD2069A-9907-4521-99E6-34C3E1A14E65@microsoft.com...
    >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?
     
  4. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    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

    "metaltecks" <metaltecks@discussions.microsoft.com> wrote in message
    news:3CD2069A-9907-4521-99E6-34C3E1A14E65@microsoft.com...
    >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?
     
  5. metaltecks

    metaltecks
    Expand Collapse
    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?

    "Graham Mandeno" wrote:

    > 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
    >
    > "metaltecks" <metaltecks@discussions.microsoft.com> wrote in message
    > news:3CD2069A-9907-4521-99E6-34C3E1A14E65@microsoft.com...
    > >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?

    >
    >
    >
     
  6. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    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



    "metaltecks" <metaltecks@discussions.microsoft.com> wrote in message
    news:21269448-E55A-4AF5-8252-8ACFDA201579@microsoft.com...
    > 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?
    >
    > "Graham Mandeno" wrote:
    >
    >> 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
    >>
    >> "metaltecks" <metaltecks@discussions.microsoft.com> wrote in message
    >> news:3CD2069A-9907-4521-99E6-34C3E1A14E65@microsoft.com...
    >> >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?

    >>
    >>
    >>
     
  7. metaltecks

    metaltecks
    Expand Collapse
    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.

    "Graham Mandeno" wrote:

    > 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
    >
    >
    >
    > "metaltecks" <metaltecks@discussions.microsoft.com> wrote in message
    > news:21269448-E55A-4AF5-8252-8ACFDA201579@microsoft.com...
    > > 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?
    > >
    > > "Graham Mandeno" wrote:
    > >
    > >> 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
    > >>
    > >> "metaltecks" <metaltecks@discussions.microsoft.com> wrote in message
    > >> news:3CD2069A-9907-4521-99E6-34C3E1A14E65@microsoft.com...
    > >> >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?
    > >>
    > >>
    > >>

    >
    >
    >
     
  8. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    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

    "metaltecks" <metaltecks@discussions.microsoft.com> wrote in message
    news:BFE77F7E-0A81-4D4F-AFAE-5BDE198F56C2@microsoft.com...
    > 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.
    >
    > "Graham Mandeno" wrote:
    >
    >> 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
    >>
    >>
    >>
    >> "metaltecks" <metaltecks@discussions.microsoft.com> wrote in message
    >> news:21269448-E55A-4AF5-8252-8ACFDA201579@microsoft.com...
    >> > 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?
    >> >
    >> > "Graham Mandeno" wrote:
    >> >
    >> >> 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
    >> >>
    >> >> "metaltecks" <metaltecks@discussions.microsoft.com> wrote in message
    >> >> news:3CD2069A-9907-4521-99E6-34C3E1A14E65@microsoft.com...
    >> >> >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?
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     

Share This Page