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

 

[lespaul]

could you provide some sample data and expected results?

Which fields have you set up the relationships on (how are the tables linked?)? What are you trying to count?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[SA812]

I have a simple query i've linked container table and container sub table by the record number. Show all records from container table and only show matching records from Container Sub. I have a date range query [enter start date] and [enter ending date] from the date field in the container table. When i run the query it shows the container number with every sub container data. Below is an example of the query;

[date] [Container #] [Action] [Product type] [Total Time]
3/3/06 nyku1234 unload tank 45 3/3/06 nyku1234 putaway boiler 60 Now when i try to total everything up it keeps duplicating the container # for evey sub record action like; put away, kit, unload..

 

[lespaul]

Main table:
[tt]
[Record #] [Date] [Container #]
1 3/3/06 nyku1234
[/tt]
Link sub table:
[tt]
[Action] [Product Type] [Total Time]
unload tank 45
putaway boiler 60
[/tt]

I'm guessing that this is the way the original data looks in the table, but I still don't see how the tables are linked and what you are trying to count.

 

[SA812]

I want to create a report that totals the containers recieved between the date ranges, and also totals all the time by action for the date range. See below

Containers from 3/1/06 to 3/20/06

Container count= 15
Total Unload time 600 minutes
Total Kitting time 500 minutes
Total put away time 240 minutes

I'd also like to maybe group the unload, kitting, and Putaway time by product catagory;

Containers from 3/1/06 to 3/20/06

Container count= 15
Product [Tanks]
Total Unload time 200 minutes
Total Kitting time 250 minutes
Total put away time 120 minutes

Product [Boilers]
Total Unload time 200 minutes
Total Kitting time 250 minutes
Total put away time 120 minutes

Grand Totals
Unload Time 400 minutes
Kitting Time 500 minutes
Putaway Time 240 minutes

Hope this clarifies what i'm looking for.

SA

 

[lespaul]

So, I'm guessing that Container # is the key field that's in both tables?

This query will give you the sum of the time per action:
[tt]
SELECT SubTableName.[Container #], Action, SUM([Total Time]
FROM SubTableName
INNER JOIN MainTable on SubTableName.[Container #] = MainTable.[Container #]
WHERE [Date] BETWEEN #3/1/06# AND #3/20/06#
GROUP BY SubTableName.[Container #], Action
[/tt]
To get it by Product as well, just add that field to BOTH the SELECT clause and the GROUP BY clause:
[tt]
SELECT SubTableName.[Container #], Product, Action, SUM([Total Time]
FROM SubTableName
INNER JOIN MainTable on SubTableName.[Container #] = MainTable.[Container #]
WHERE [Date] BETWEEN #3/1/06# AND #3/20/06#
GROUP BY SubTableName.[Container #], Action, Product
[/tt]
To get the count of the containers recieved during that time:
[tt]
SELECT COUNT(*) FROM MainTable WHERE [Date] BETWEEN #3/1/06# AND #3/20/06#
[/tt]
you can play with the GROUPING in the report as well.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[SA812]

Actually the auto number is the key field in both tables. I couldn't get the container number to auto populate the sub table field.

 

[lespaul]

just switch the ID field with the Container # in the query above.

 

[SA812]

Hi guys thanks for your patiences with me. I've copied the SQL view of my query. My query still duplicates my Container number for every sub record found.
Here is my query;
SELECT Container.Date, Container.[Container #], [Container Sub].Action, [Container Sub].[Total Time]
FROM [Container] LEFT JOIN [Container Sub] ON Container.Record = [Container Sub].Record
WHERE (((Container.Date) Between [StartDate] And [EndDate]));

My relationship is to show every record from container and only matching records from sub container. My join is on record number.

 

[lespaul]

Now when i try to total everything up it keeps duplicating the container # for evey sub record action like; put away, kit, unload..

if you don't want to total by action, you HAVE to remove action from the select list.

if you want a total of time PER CONTAINER:

SELECT Container.Date, Container.[Container #],SUM([Container Sub].[Total Time])
FROM [Container] INNER JOIN [Container Sub] ON Container.Record = [Container Sub].Record
WHERE (((Container.Date) Between [StartDate] And [EndDate]))
GROUP BY Container.Date, Container.[Container #];

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[SA812]

So i guess i can't have both then can i? If i count containers i can't sum the actions per container. I'd like to know total time by action for each container w/ a container count.

 

[lespaul]

no, once you get this query working the way you want, in the REPORT you can manipulate the information to display what you need. If you need a count of each Container # then you can do a DCOUNT in a control on the report and it will count the containers.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[SA812]

Leslie
I believe Dcount will solve my problem. Now in order for it to work i have to write it as follows;
=DCount([Container #],"test2") I keep getting an error when i try to write like above. I don't think it knows i want "test2" to be a query. Should i write =DCount([Container #],[query]"test2")? Test2 is the simple query i wrote.

Thank You

 

[lespaul]

I'm not sure if you can use a query as the source of the DCOUNT function (maybe someone can jump in here and let us know??). If not, use the table and the same date parameters you're using in the query:

DCount("[Container #]", "TableName", "[Date] > #" & param_Value & "#")

I'd also suggest changing the field name from DATE to something more descriptive like Arrival Date. "Date" is a reserved/key word in most languages and can cause issues.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[SA812]

Ok i tried these but i recieve errors on both.

=DCount("[Container #]","Countainer","[Date] > #" & "[Between [StartDate] And [EndDate]" & "#")


=DCount("[Container #]","Countainer","[Date] > #" & "(((Container.Date) Between [StartDate] And [EndDate]))"& "#")

I read in the Access help files you can use either a table or a query in Dcount. I'm not sure how to write out that i'm using a query in the domain part of the expression.
DCount («expr», «domain», «criteria»)

Thanks
SA

Thanks
Steve A.

 

[lespaul]

where are you getting the values 'Start Date' and 'End Date'? is there a form that the user inputs those values? That's what needs to go here:

=DCount("[Container #]", "Container", "Container.[DATE] Between #" & FormName.FieldNameThatContainsStartingDate & "# AND #" & FormName.FieldNameThatContainsEndingDate & "#")

The syntax of the FormName.Control may be a little off, but that's the idea.

Leslie

 

[SA812]

The Start date & End date come from the Query Criteria under the date field in critera it says "Between [StartDate] And [EndDate]". I guess i could create a form to enter the dates into, but it seems to be turning a major over hall of the simple query.

 

[lespaul]

Do you get prompted to enter StartDate and EndDate?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[SA812]

Yes either when you run the query or the report it prompts you for the start date & the end date.

SA

 

[lespaul]

Ok, if you need to use the DCOUNT in the report, you are going to have to replace the Access parameter prompt with a small form. Your query will look like:

WHERE Container.[Date] Between FormName.ControlNameStartDate AND FormName.ControlNameEndDate

you'll be able to use those same control names for the DCOUNT and you'll only have to enter dates one time on the same form.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design