Query selecting same record several times?

[spacepro]

I have a query based on two tables that are related.
One is a order table and the other is a product table, where the order table can have many records in the product table(One-to-Many).

When I run the query it will show 5 records with the same ID with the different products on each line.

Is there any criteria that I can put in the ID entry = (no duplicate autonumbers). Any help much appreciated

Or is there some filter that I can set on the report, the recordsource is the query.


Andy

 

[neileg]

Your report should be handling the duplicates.

Surely, you want group the product lines by order number, so use the sorting and grouping in your report to deal with this. If you eleiminate the dupes in the query, you'll only have one record for your report, and that sounds wrong to me!

 

[spacepro]

I don't won't to delete the records as each line is related to that ID.
The grouping/sorting does not work. Any more suggestions.

Example of query records.

ID Name Address OrderID ID Product Qty

1 Andy acacia av 1 1 beans 1
1 Andy acacia av 2 1 sugar 1
1 Andy acacia av 3 1 milk 2

When I run the report is show the record, but then also show several pages as well as to how many records there are, in this example it would show up 3 times.

Many Thanks
Andy

 

[neileg]

I don't won't to delete the records as each line is related to that ID.
I didn't suggest that, did I?

The grouping/sorting does not work. Any more suggestions.
Example of query records.
ID Name Address OrderID ID Product Qty
1 Andy acacia av 1 1 beans 1
1 Andy acacia av 2 1 sugar 1
1 Andy acacia av 3 1 milk 2

Well it works for me based on your sample data. (providing I ignore the repeated field called ID). I created a header based on the ID and added in the name and address. In the detail I have the OrderID, Product and Quantity

When I run the report is show the record, but then also show several pages as well as to how many records there are, in this example it would show up 3 times.
I don't think you understand how sorting and grouping work. The effect you describe would only happen if the detail lines ended up in the page header.

 

[spacepro]

Neil,
Sorry I misunderstood .
I understand about removing duplicates from the query but I cannot do this because of the product lines are on seperate lines on each entry when the query displays the results.

And also I have placed the info from the query into a custom built delivery note, so I can't do what you have suggested can I.

see this thread as to what I have trying to do.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=44611

Thanks Neil for your patience.
Andy

 

[neileg]

I understand about removing duplicates from the query but I cannot do this because of the product lines are on seperate lines on each entry when the query displays the results.
I didn't suggest you should do this! I said it sounded wrong!

And also I have placed the info from the query into a custom built delivery note, so I can't do what you have suggested can I.
I don't understand why not. Provided your delivery note has a clear horizontal division between the header stuff, name address, document numbers etc, and the product lines (as is almost universal) the approach I suggested is perfectly practical.

I did see your other posting. It drives us nuts when people do this because we end up answering the same or similar questions.

So let me state this clearly:
I understand what you want to do. I have made a suggestion that answers your problem. You don't seem to be ready to believe that it will work, but it does!

 

[spacepro]

Neil,
I don't normally post twice but on this occasion I was trying to get help to somethin specific and not the whole process I am trying to achieve.

I will try you suggestion, however I don't have much room on my d/note and I thought that adding a header would push the d/note into two pages, which I don't want.

I do apologise but I probably am a bit confused, I though it would be quiet easy to accomplish.

You will be probably say Yes it is. I will follow your post to the letter and I thank you for your comments and suggestions.

Andy

 

[neileg]

I don't normally post twice but on this occasion I was trying to get help to somethin specific and not the whole process I am trying to achieve.
Mm. You might be better posting the whole problem once!

I will try you suggestion, however I don't have much room on my d/note and I thought that adding a header would push the d/note into two pages, which I don't want.
Hang on. Don't you need to print things like the delivery name and address? Where else does this go except on the header?
My dnote created from your data looks like the attached

I do apologise but I probably am a bit confused, I though it would be quiet easy to accomplish.
Happens to us all!

I'll follow your post to the letter and I thank you for your comments and suggestions.
I could be wrong!

 

[directormac]

I will try you suggestion, however I don't have much room on my d/note and I thought that adding a header would push the d/note into two pages, which I don't want.

Andy: You could probably help Neil help you if you would post some example of the report you have and tell us how it differs from the report you want. A screenshot would do, a zipped version of the .mdb would be even more helpful.

--Sample Mac

 

[KevinM]

Neil,
I don't have much room on my d/note and I thought that adding a header would push the d/note into two pages, which I don't want.

So what if I wanted to order a hundred different items?

 

[spacepro]

Neil,
I have posted in Db in the chance you could point me in the right direction.

The query is tblHousehold query and the report is test.

Mm. You might be better posting the whole problem once!
You are probably right! I was going to delete one of the thread once completed. Just trying to take the learning curve round the bend.

I would be grateful if could could have a look at it for me.
many thanks
Andy

Neil- The Db won't attach please d/load from this link. Thanks
My DB
Enter Delivery Date as 25/03/03
This will show you what I mean

 

[spacepro]

KevinM

So what if I wanted to order a hundred different items?

Kevin this will never happen, all customers are households.


Thanks
Andy

 

[spacepro]

The report show the ID 39 several times, I only want it to show it once and then show ID 55.

Directomac: Thanks for the advice I was about to upload when you suggested it, but nice to know everyone helps one another.

Andy

 

[KevinM]

'Never', maybe not now but you should be as flexible as possibe with your db and allow for possible expansion in the future.

i.e. restricting deliveries to one page only is not a good idea (if at all possible, IF you had several order lines)

I've looked at your report and neil has answered your query in his first reply.

 

[spacepro]

Kevin,
I appreciate your comments. We are currently changing the business operation and bringing in systems to implement the changes to the operation. Thus saying Never.

However I do take on board what you have said I will bear this in mind when continuing with my project.

Many Thanks
Andy

 

[neileg]

As KevinM said, I was right!

The firewall at work won't let me post this back.

Basically, you need to add a new header based on the WorldfleetID. Move all of the stuff you have in the detail section except the subreport to this header. Delete the subreport and add the fields that were in the subreport to the detail section. Add the column headings for these fields to the bottom of the WorldfleetID header.

PS I need a new Bosch dishwasher. Could you add this to your next delivery run!

 

[spacepro]

Neil,
Thanks for having a look, and telling how to do it. Thank you.

BTW The dishwasher, we are up their every day so you never know. I'll surprise you.

Thanks Neil.
I will post back if I have any problems thank you.

Andy

 

[spacepro]

Thanks Neil , I have done it!

 

[neileg]

You've done it?

 

[spacepro]

I mean you've done it.
Thanks Neil