OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

query creates duplicate records? [SOLVED]

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
svender
General User
General User


Joined: 27 Jan 2010
Posts: 34

PostPosted: Thu Feb 04, 2010 11:56 pm    Post subject: query creates duplicate records? [SOLVED] Reply with quote

I have made a form with which I can add records to a table.

I've created a query for that table.

The table only contains one record.

For some reason the query duplicates that record 8 times so I have 8 identical records in the query.

I've also created a report for that query and there are 8 duplicate records showing on the report as well.

Why is it doing this?

Thanks for any help!


Last edited by svender on Wed Feb 10, 2010 1:40 am; edited 2 times in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Feb 05, 2010 9:08 am    Post subject: Reply with quote

svender:

You said:

svender wrote:
For some reason the query duplicates that record 8 times so I have 8 identical records in the query.

Why is it doing this?

The way you have asked the question, and, with the information ( or rather lack of information ) you provided, it is impossible for me to answer.

If you would like me to help, please tell me / us:
  1. How did you create the query? That is, from the Query window:

    • Create Query in Design View...
    • Use Wizard to Create View...
    • Create Query in SQL View...

  2. Can you please show ( include ) the SQL that it / you created

    If you do not know how to display the SQL, let me / us know

I suspect, but, withOUT seeing the answers to the above, you have multiple tables, and, you probably have NOT related the tables. Need to see the SQL to confirm what is going on.

I hope this helps, please be sure to let me / us know.

Sliderule
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Feb 05, 2010 9:41 am    Post subject: Reply with quote

Code:
SELECT "T1".*
FROM "T1", "T2"

Selects all fields and all records from table T1 multiplied by all records from T2 (http://en.wikipedia.org/wiki/Cartesian_product).
If T1 has one record and T2 has 8 records, the resulting row set includes 8 records, all from T1 since you selected only the fields of T1.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
svender
General User
General User


Joined: 27 Jan 2010
Posts: 34

PostPosted: Fri Feb 05, 2010 6:15 pm    Post subject: Reply with quote

I apologize for the lack of information.

Villeroy clarified the problem. I did have more than 1 table in SQL. I removed the tables that were not associated with the query and now it does not display multiple records.

I did create the query in design view and added the tables manually. What I failed to do was to remove the unnecessary tables.

This is the SQL after removing the unnecessary tables.

Code:

SELECT DISTINCT "Invoice No", "Invoice Date", "PO No", "Sold To Address", "Sold To City", "Sold To State", "Sold To Zip Code", "Sold To Telephone", "Sold To Email", "Ship To Address", "Ship To City", "Ship To State", "Ship To Zip Code", "Bill To Address", "Bill To City", "Bill To State", "Bill To Zip Code", "Salesperson", "Ship VIA", "Ship Date", "Terms", "FOB", "ID" FROM "tblInvoice" AS "tblInvoice" ORDER BY "Invoice No" ASC


Thanks again for the help...
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group