Problem with counting fields.

[SA812]

I have designed a simple database to track containers my company recieves. We unload the containers and then either kit the products or kit & put them away..
I have one table with a auto number (Record #) as the primary key.
My table looks as follows.
Main table;
[Record #] [Date] [Container #]
Link sub table;
[Action] [Product Type] [Total Time]
My sub table tracks the action like unload time or kitting time or put away time.
My problem is when i run a query it duplicates the container # for every sub record in the sub table.
I can hide the duplicate container #'s in the finshed report but when i try to total or count the # of containers it still counts the hidden duplicate container #'s. I've tried running 2 separate queries but havent had any luck.. I'm using office 2003 and i've set up the relationship as 1 to many. with the record field being linked.

Thanks
SA

 

[Hap007]

Dear SA812

Sounds like you have been given a lot of good advice here.

I might suggest a different appoach.

1) Create a query (call it qryStep1SumContainer)
This would be a summary query, grouped on Container Number
The data you need then could be summed, counted or whatever you require.

2) Now create a query for your report.
You would have two record sources.
a) Container File
b) then query we created in step 1 above (qryStep1SumContainer)

Join the two sources on Container number and that should give you one record, per container with all the info you need.

Hope this Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[SA812]

That would be great because i allready have two queries created but could get them both to work on the report. How do i assign two queries to a report record sources?

I think i'm still going to create a form with the date ranges because it seems a better approach than using the criteria in the query it self. Plus Leslie has been trully dedicated to this post

But i'd love to know how to fix my current problem quickly.

Thanks
SA

 

[lespaul]

How do i assign two queries to a report record sources?

you combine them into a single query like Hap suggested:

SELECT * FROM qry1 INNER JOIN qry2 on qry1.ContainerNumber = qry2.ContainerNumber

you will get a single row for each container and the ONE query can be the report source.

Thanks for the compliment!

Les

 

[SA812]

I tried that before but i was stuck having each query haveing a date parameter so i'd enter the start & end date twice. So i guess i'm better of just creating a form that runs the date ranges.

 

[lespaul]

yes, in order to only enter the dates one time and populate both queries at once, you will need a form.

leslie