i'm trying to create a customer phone list from a query, that takes each customer once as well as their phone number from the order table that i created. how do i eliminate extra records from customers who order more than once? is there any critera expression i can add to my query under customer name? thanks to anyone who can solve my dilema!

Create a new table that includes all of the fields you will be showing in your query, then set the primary key on the customer ID or customerName field. (This will ensure no duplicate records). Then change your query to an AppendQuery and select the table you just created. Run the query. You should get a message telling you that a certain number of records could not be appended, would you like to continue. Say yes.
Open the table, and voila!, one record per customer!

>how do i eliminate extra records from customers who order more than once? <

Are you actuallysaying that the telephone numbers are actually stored in the orders table? (I hope not :D ).

You shouldn't. It indicates that your database isn't properly normalized.

Anyway, what's the relation between the Customer table and the Order table.
Is the phone number entered per order.
Could you post your query statement.

RV

charityg,

I just read your advise.
IMO, I wonder whether your suggestion isn't a bit overdone and will add to a solution.

As vivian implicates, the phone numbers are stored per order.
Assuming the same phone number is stored per row, you'll still need to join the Customer and order table....
Furthermore;

>Open the table...<

suggests you're suggestion to create extra tables, that's really a bad idea...
Or am I missing a point here?

RV

in response to RV: i've stored customer information in the main form where as the order information is a subform going back to a sort of "transaction" table. i hope this isn't the worst idea to do things. is there a better way? i think it organizes the info quite well.

I think what RV was trying to point out is that the customer's phone number should be stored with the rest of the customer information. Unless of course the phone number is different based on the order. You should have a CustomerTable and an Order or Transaction table. Your main form should be linked to the customer table, and the subform linked to the transaction. Your transaction table should have a field for a unique customerID. The main form and subform should be linked by the customerID. Then by design, in your customer table, you will only have one record per customer, and your transaction table will contain many records (orders) per customer. This is what is called a one-to-many relationship.

You can use my original solution if you just want a quick fix for printing a telephone directory, but I agree with RV that you should probably rethink your database design.

charityg,
sorry, i think that my question is a little misunderstood. i do have a customer info table, and all the customer info-such as the phone number- are stored in that table. that table is totally separate from the orders table-containing all the order information, including products and all that stuff. in my "order form" i have the main form linked to my customer table. from there i have a subform linked to my order's table. these are linked by an order #. therefore i can print just the one order with that specific order information. i linked them by order # because i wanted the order # to be an autonumber. because you can only have one auto number per table, i thought this would be the best idea.

Yes it is fine for you to have your order number an autonumber, but you should not be storing order numbers in your customer table. You should be storing your customer numbers in your Orders table. Can you output your table structures, and attach them to a post, so that I may better understand your design, or attach a copy of your db without any sensitive data?

I'd really like to help you understand that sound database design (tables and relationships) is essential to a good database because it is the foundation that supports all of the data.

By the way, if I'm understanding you correctly. If your customertable includes Customer Name, address, phone, etc. Then your query statement to elimate any duplicate entries would be Select DISTINCT YourFields FROM YourTable. You need to add the keyword DISTINCT after the select statement.

i wish i could send you my tables and such, but my e-mail/internet is on a separate, none-linked computer :( . i think i understand what you are saying, i shall link the customer to the order via customer, not the order to the customer via order #. thanks for all your help. sorry, for all the questions, i'm fairly new at this, i'm willing to learn. thanks again

Don't ever apologize for asking questions. Asking displays a willingness to learn, and learning is how you get better. A lot of people don't care what the correct way is as long as they can get it to work. I hope RV and I gave you a nudge in the right direction. If you have any other questions, don't hesitate to ask. This forum is full of knowledgable, understanding individuals who are willing to help!

Northwind.mdb is a good example of the table structure that you need.