Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old December 13th, 2006, 02:26 PM
Authorized User
 
Join Date: Aug 2006
Location: Hoffman Estates, IL, USA.
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default Unique Records

Hello,
  I should know this, but am drawing a blank right now. I am working on recreating a database we have because it is bad. Part of what I am doing is taking one monsterous table and splitting it. However, I need to pull information out.

  The deal is this. We have numerous entries for the same item in the table and I need to create a query which will show each unique item once instead of however many times it is in the database. The field I want to key off of is the ISBN field. Can someone please help this brain-dead goof right now?

Thanks in advance,
Arholly

  #2 (permalink)  
Old December 13th, 2006, 02:46 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

What is the current table structure, and what do you want it to look like when you are finished?



mmcdonal
  #3 (permalink)  
Old December 13th, 2006, 02:53 PM
Authorized User
 
Join Date: Aug 2006
Location: Hoffman Estates, IL, USA.
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,
  The current ugly mess has the following fields:
Code:
Holding_ID
Master_ID
School
Program
Course_Code
Book_Title
Edition
ISBN
E-Book_ISBN
Author
Publisher
First Session Used
Scheduled_Review_Date
Copyright_Date
Software
Trial_Software_Included
Trial_Software
Internal_Notes
Who_Changed
External_Notes
Date_Changed
Reason_Changed
Changed
New
Inactive
Inactive_Reason
What I need to do is break it into a few different tables. Right now, I'm trying to get just the book information from the table. My plan is to create four separate tables (I think it is what will work best). One table will house books, another software, a third will let me know what books or software are for each course, and the final table will let me know why a book or software is inactive for a given course.

Does this help?

  #4 (permalink)  
Old December 13th, 2006, 03:01 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

That seems pretty easy, in spite of all the things wrong with that.

Do a Make Table query that pulls all of the columns you need from ISBN on down. Make sure Unique values are set to yes. You may still have duplicates since there is bound to be two records with just one character out of place, which makes the records unique.

After you create the new table, make the ISBN the PK (indexed Yes, No Duplicates). This will probably throw errors until you get the dupes out. Also, make sure the ISBN's are not duplicated, some with dashes, others without, as sometimes happens.

Once this new table is clean, you can do two things, or a combination:

Change the ISBN column to a look up, and have it look into the new table for its PK. You may want to display the ISBN, and the Title.

If you throw some errors, you canmake the look up by hand, and allow items not in the list until you can clean them up. You will have to scroll through and make the cnages by hand.

Alternatively, you can do a query JUST on the ISBN, and set the values to Unique = Yes, and this will show you a list of all the ISBNs, and you can shape them up before you do the make table query.

Anyway, after you do that, just delete the extra columns from the first table since they will be captured in the new table. Also, change allow not in list to no.

I am sure you have more questions. No prob. See, designers shouldn't have to do this hand work.


mmcdonal
  #5 (permalink)  
Old December 13th, 2006, 03:04 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

As an after thought, and I have done this (sadly): create a new column in the old table called ISBN1 for now, and after yyou create the ISBN table, open the old table, and do a look up on each new ISBN to make the closest match to the old one.

How many records do you have? The smartest thing is to clean the ISBN data before you do anything.


mmcdonal
  #6 (permalink)  
Old December 13th, 2006, 03:12 PM
Authorized User
 
Join Date: Aug 2006
Location: Hoffman Estates, IL, USA.
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have 748 records I need to work on. Yes, I'm going to have to work on this to get it cleaned up. It's why I'm starting it now and plan to do the major work over the weekend.

  #7 (permalink)  
Old December 13th, 2006, 03:28 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Oh, well, that is not a lot of records.
Do a make table query, and create a unique list of ISBNs plus their related data.
Then clean that first.
Then see if the look up will work.
If not, create a new column and match them up by hand.
Then delete the old ISBN column, and any other columns that moved.
ALWAYS BACK UP YOUR DATA FIRST!!!
GLWT

mmcdonal
  #8 (permalink)  
Old December 13th, 2006, 03:29 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

BTW: The table splitter never works right and can cause more problems than it resolves.
Of course, you could always try that, too.


mmcdonal
  #9 (permalink)  
Old December 14th, 2006, 05:18 AM
Authorized User
 
Join Date: Dec 2006
Location: , , .
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I do not know much about access but I think I could give you the sql command that can help you (replace database with your database's name)

SELECT DISTINCT DATABASE.ISBN
FROM DATABASE;

this should give you all the unique isbn numbers

I hope I answered your question
  #10 (permalink)  
Old December 14th, 2006, 08:22 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

That would be:

SELECT DISTINCT TableName.FieldName
FROM TableName;

For most Access developers at this level, the easiest way to do this is in the query designer, select table, select column, then select Properties, and set Unique Values to Yes. These steps yield the SQL string above.




mmcdonal


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Variables to Count Unique Records Dallas Miller BOOK: Professional SQL Server 2005 Reporting Services ISBN: 0-7645-8497-9 0 October 18th, 2007 08:03 AM
Inserting unique records from a table to another elygp SQL Server 2000 0 September 10th, 2007 01:58 AM
SQL script to create a unique ID for records chubnut SQL Server 2000 1 October 16th, 2006 07:25 AM
Unique records for reports Brendan Bartley Access 2 November 28th, 2005 06:35 AM
Generating 5 unique random records from a database ps124 ASP.NET 1.0 and 1.1 Basics 2 March 8th, 2004 05:59 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.