duplicates in query result

S

sueviolet

Registered User.
Local time
Today, 18:14
Joined
May 31, 2002
Messages
127
I will try my best to describe my table relationship:


tbl_FW_Main
ID (autonumber)
PubID (Primary KEY)
Title
Department
Date_Published
Publisher
Report_Location
Authors


Tbl_Waterbody
ID (autonumber)
PubID (foreign key)
WaterbodyName
Watershedcode
AliasName
etc


Tbl_Species
ID (autonumber)
PUBID (foreign key)
Speciescode


tbl_FW_Main - this table is the 'one' side of relationship. This table is linked to the other tables by "PUBID". There are no duplicate pubid in this table. For example, one pubID will link to many records in either tbl_species or tbl_waterbody

tbl_waterbody- this table is on the "many" side of the relationship
tbl_species - this table is on the "many" side of the relationship

For example,

PubID "LM506.3" in tbl_FW_Main should link to 2 waterbodies (GOLD CREEK, ALOUETTE LAKE) in tbl_waterbody and 5 species types in tbl_species.


Access query result for "LM506.3":

PubID WaterBodyName SpeciesCode Authors
LM506.3 ALOUETTE LAKE RB ROB KNIGHT
LM506.3 ALOUETTE LAKE "DV" ROB KNIGHT
LM506.3 ALOUETTE LAKE "KO" ROB KNIGHT
LM506.3 ALOUETTE LAKE "NSC" ROB KNIGHT
LM506.3 ALOUETTE LAKE "CSU" ROB KNIGHT
LM506.3 ALOUETTE LAKE "STICKLEBACK AND SCULPINS" ROB KNIGHT
LM506.3 ALOUETTE LAKE "RSC" ROB KNIGHT
LM506.3 ALOUETTE LAKE "PCC" ROB KNIGHT
LM506.3 ALOUETTE LAKE "LT" ROB KNIGHT
LM506.3 ALOUETTE LAKE Cutthroat ROB KNIGHT
LM506.3 GOLD CREEK RB ROB KNIGHT
LM506.3 GOLD CREEK "DV" ROB KNIGHT
LM506.3 GOLD CREEK "KO" ROB KNIGHT
LM506.3 GOLD CREEK "NSC" ROB KNIGHT
LM506.3 GOLD CREEK "CSU" ROB KNIGHT
LM506.3 GOLD CREEK "STICKLEBACK AND SCULPINS" ROB KNIGHT
LM506.3 GOLD CREEK "RSC" ROB KNIGHT
LM506.3 GOLD CREEK "PCC" ROB KNIGHT
LM506.3 GOLD CREEK "LT" ROB KNIGHT
LM506.3 GOLD CREEK Cutthroat ROB KNIGHT

As you can see there are 2 of each species type. (not good)

I would like not to have duplicate values in the query output

Is there something wrong with my table relationships or is it the query that is the problem?

Thanks

Sue
 
I think you need a separate table in which waterbody and species get joined. I'll call this tblOccurance in the attached gif. An occurance is reported by a publisher. This report can have one ore more authors.
 

Attachments

  • species.gif
    species.gif
    15.7 KB · Views: 153
Although Waterbody and Species each have a relationship with FW_Main, they do NOT have a relationship with each other. Just because they have a foreign key that can be used to make this join, doesn't mean you should do it. The essential outcome of a join between tables that are not related is a cartesian product. The total number of records in the recordset of a cartesian product will be tblA * tblB * tblC. Since I'm sure you have join lines (so you don't have a complete cartesian product, only a partial one), tblA will be excluded from the calculation and you'll end up with tblB * tblC.

Bottom line - this query makes no sense. If you want to make a form or report that shows all the data, use a main form/report for FW_Main, and two separate subforms/reports. One for each of the other two tables. These subforms/reports should be linked by the master/child links to the main form/report.

Other observations.
1. You should not use just "ID" for your autonumber. It should be qualified by tableName_ID such as FW_Main_ID, Waterbody_ID, and Species_ID.
2. If you are going to use autonumbers, use them as the primary key and define a uniqe index to enforce your business rules. So FW_Main_ID would be the primary key.
3. The primary key must be used as the foreign key so PubID would need to change to FW_Main_ID.

The point of usnig autonumbers as primary keys is to provide unique, unchanging identifiers for each tables. There is no point in having an autonumber unless you are going to use it as the pk.
 
Thanks

Thankyou to you both for your reply.

I would like to change my database structure to the design illustrated in "species.gif"

Not sure how to transfer my data in its existing structure to the structure in "species.gif" - any not lose the links between the data. In my existing structure, the only thing linking the three tables is PUBID.


What steps should I take?

Thanks,

Sue
 
What steps should I take?
Click to expand...
First of all I would store a backup of the existing data.

You could then link your existing tables into a new database and design some select queries for the basic data tables, change them to maketable queries and execute them.

In Table design mode erase the data, add a primary key as an autonumber field. Do the necessary arrangements of additional indexes and adjust validation rules for the fields.

Then back in the QBE editor change your maketable queries into *append queries, set up the fields and execute them. You shouldn't get any error messages.

After the basic data is set up, create the appropriate maketable queries for the outstanding tables and proceed with the previously described procedure. Maybe you have to do some grouping in one or the other query to eliminate duplicate data.

* changed update to append
 
Last edited:
Norbert,


Sorry, still I little confused

Could you be more specific (ie: what tables, fields)

Your help is greatly appreciated - you are a lifesaver

Sue
 
Norbert,


Sorry, still I little confused

Could you be more specific (ie: what tables, fields)

Your help is greatly appreciated - you are a lifesaver

Sue
 
Well, Sue, I will try to explain it in more simple words.

My suggestion is that you will end up in a new database without touching your old data - if you do a right click inside the database window you will find in the custom menu an item for linking tables.

In a new database use this feature and link each table of your existing database into the new one. Our queries will be set up by using these linked tables.

If you study the table logic, you will see, that it is impossible i.e. to setup the table tblOccurance and fill it with data before the tables tblWaterbody and tblSpecies are setup properly. The tblOccurence and the table tblPublisher must exist before filling the table tblReport and so on.

Let's start with the table tblWaterbody. Open the QBE editor and select the table tblWaterbody from the requester. Then click OK. Put all the required fields to the selection. Keep your old primary key out if it represents just an arbitrary value. Also do not select the old autonumber field. All other fields should be in the selection.

By selecting the context menu inside the query you could change the type of the query. Choose maketable query. After that You have to enter a name for your new table. Clicking on the red exclamation mark in the tool bar will execute the query and fill the table with data.

As Pat remarked earlier in his posting your tables should have an autonumber as the primary key. With a make table you could not accomplish this. To do it you should add the field WATERBODYID manually into the table in design mode. I also strongly recommend setting up some indexes for keeping your data consistent.

You could leave out the last step which I described before. Deleting the table's content and doing the data transfer with an append query is more suitable if you import data more than once into the same table.

Now repeat this step for tblSpecies.

For the tblOccurance you have to create a query in which you select data from five tables (your three and the two newly created). Join the two newly created tables via WATERBODYNAME and SPECIESCODE. Then put just the two newly created primary key in the selection. At least one of the primary key fields needs the function CLng(WATERBODYID) because of a lack in the maketable feature of Access. Change the type of the query to a maketable query and do further work (indexing, adding an autonumber as primary key) in table design mode.

This is only one possible solution for your problem. The table design could also done manually. The data gets then transfered by using append queries.
 
I understand up until:

"For the tblOccurance you have to create a query in which you select data from five tables (your three and the two newly created). Join the two newly created tables via WATERBODYNAME and SPECIESCODE. Then put just the two newly created primary key in the selection. At least one of the primary key fields needs the function CLng(WATERBODYID) because of a lack in the maketable feature of Access. Change the type of the query to a maketable query and do further work (indexing, adding an autonumber as primary key) in table design mode"

Not sure where to go from here, this step confuses me


?
 
Have a look at the relationships and you'll see how to go further. You have to fill the table from the bottom to the top. That means if there are Joins on the many-side the related tables of the 1-side have to been set up before.

Maybe you can attach a file with a small set of your tables.
 
I have attached my original 3 tables (Access 97) for you to take a look at. The 3 tables are linked by pubID

Thank you so much for your help on this

*******
I did what you said to do with the tbl_species and tbl_waterbody,
which has given me:



tbl_species_new:

SPECIESID (autonumber)
SpeciesCode

tbl_waterbody_new
WATERBODYID (autonumber)
WaterBodyName
WatershedCode


And I still have the existing 3 tables.


How do I bring SpeciesID and WaterbodyID into a query to make a table that will become tblOccurance?

In my attempt to produce tblOccurance, I tried to following your suggestions:

1. Join original 3 tables in query (linked my pubid)
2. Joined the original species table to the new species table (linked by 'speciescode')
3. Joined the original waterbody table to the new waterbody table (linked by 'waterbody')
4.Add the newly created primary keys (species id and waterbodyid)

5. Took a veryy long time for this query to run - I had to shut Access down.
 
I have attached my original 3 tables (Access 97) for you to take a look at. The 3 tables are linked by pubID.

Thank you so much for your help on this

*******
I did what you said to do with the tbl_species and tbl_waterbody,
which has given me:



tbl_species_new:

SPECIESID (autonumber)
SpeciesCode

tbl_waterbody_new
WATERBODYID (autonumber)
WaterBodyName
WatershedCode


And I still have the existing 3 tables.


How do I bring SpeciesID and WaterbodyID into a query to make a table that will become tblOccurance?

In my attempt to produce tblOccurance, I tried to following your suggestions:

1. Join original 3 tables in query (linked my pubid)
2. Joined the original species table to the new species table (linked by 'speciescode')
3. Joined the original waterbody table to the new waterbody table (linked by 'waterbody')
4.Add the newly created primary keys (species id and waterbodyid)

5. Took a veryy long time for this query to run - I had to shut Access down.
 
I left out the tables tblReportAuthor and the tblAuthor because the commata sometimes separates suffixes and sometimes authors.

In further work you could add a character like "|" in the place, where the author names should split. In a general module you'll find a function for doing the splitting.
You should end up in a query with the REPORTID and AUTHOR1, AUTHOR2, etc.

With this Query you can setup union queries. In the first union query you collect all the authors and transfer them to the tblAuthor, in the second union query you join the newly created tblAuthor in. Then select the REPORTID plus all the AUTHORIDs. This content should be moved to the tblReportAuthor.
 

Attachments

Thank you Nouba

Wow, I can't thank you enough!

I am going through it all to make sure I understand the relationships etc.


I had to add my original "PUBID" back to tblReport - this value was needed, as it is the value that links reports in this database to actual physical reports in our office.


If I can ask you another question:


I only included a small portion of my records (only could post a small file size). I still have alot of records still in my original 3 tables that need to be added to the original database design.

What would be the easiest way for me to do this?


Thanks so much for the time you have spent on this. You made my day!


Sue
 
Because I didn't check in the append queries against duplicate data I would copy just the new tables without any data plus the update queries into a new database. Link the existing tables into this new database and execute the queries in the right order (I explained this before). After you're all set, remove the links to your old tables.

If you have to repeat this step more than once, you could write some custom functions in which you embed the append queries.

Did you get your authors all separated?
 
Hi Nouba,

I am figuring out how to append my data to the tables you created.

Problem:

When I run 'qappReport' - it says it is missing 'tblLocation'. This missing table was not included in the database you sent me.

Any ideas?

Thanks

Sue
 
Thanks again Nouba

In regards to your last post:


"I would copy just the new tables without any data plus the update queries into a new database"

*****I don't see any update queries in the database you sent.
Just 'make table' queries, 'append' queries, and 2 sets of 'select' queries.

"Did you get your authors all separated?"

***Not yet, I am still trying things out to see how they work
 
Thanks again Nouba

In regards to your last post:


"I would copy just the new tables without any data plus the update queries into a new database"

*****I don't see any update queries in the database you sent.
Just 'make table' queries, 'append' queries, and 2 sets of 'select' queries.

"Did you get your authors all separated?"

***Not yet, I am still trying things out to see how they work
 
Well, I decided to rename tblLocation to tblReportLocation and forgot to change that in the append query. The text of the query should look like
Code: 
INSERT INTO tblReport (
  REPORTTITLE
, DEPARTMENTID
, DATEPUBLISHED
, PUBLISHERID
, LOCATIONID )
SELECT
  R.Title
, CLng(Nz([DEPARTMENTID]))
, CDate([Date_Published]) AS DATEPUBLISHED
, CLng(Nz([PUBLISHERID]))
, CLng(Nz([LOCATIONID]))
FROM (
  tblPublisher AS P
    RIGHT JOIN (
      qselReport AS R LEFT JOIN
        tblDepartment AS D ON
          R.Department = D.DEPARTMENT) ON
      P.PUBLISHER = R.Publisher) LEFT JOIN
    tblReportLocation AS L ON
      R.Report_Location = L.LOCATION;

Sorry, I meant append queries instead of update queries - a Freudian slip.

Hope that helps.
 
You must log in or register to reply here.

Similar threads

S
Union Query Help
Replies
12
Views
1,215
dcx693
dcx693

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom