summation of deliveries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We log all deliveries into Access. If I have 6 deliveries to Atlanta Georgia, and I want a query to calculate the total number of times that a delivery matched "Atlanta, Georgia" what command would I use? It sort of works now - it will show 6 deliveries, but for some reason, even when the price field matches, it will skip a line and have something like April: 4 deliveries and April: 6 deliveries. I understand breaking it up if the price doesn't match, but I don't get this. Any help would be appreciated

Currently, I have the DeliveryID (primary key) for the Field, from tblDeliveries, Total: Count, Crosstab: Value.

Thanks!
 
I figured out part of this...it adds an additional line anytime the DATE changes. Is there any way to prevent this? I have Jan - Dec displayed across the top, so I don't want it to change unless it's a different month, not just a different day (3/1/04 vs. 3/2/04). Again, any ideas would be appreciated

Thanks!
 
It would help if you posted your SQL.

A guess - try applying a format to your date field: Format(DateField,"yyyyMM")
if you are trying to group by the year and month.
 
Here is the SQL that I have.

Field: Tr: Delivery ID
Table: tblDeliveries
Total: Count
Crosstab: Value

There is no specific formatting or anything at all listed in the properties for these fields. I hope this helps!
 
OK, can you copy and post the SQL of your query?

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
 
OK - here's everything from the query...hope this helps! Thanks

TRANSFORM Count(tblDeliveries.[Delivery ID]) AS T
SELECT tblDestinations.State, tblDestinations.City, tblDestinations.[Freight Goal] AS Goal, tblDeliveries.[Carrier ID], tblDeliveries.[Delivery Cost] AS Cos
FROM tblDestinations INNER JOIN (tblCarriers INNER JOIN tblDeliveries ON tblCarriers.[Carrier ID] = tblDeliveries.[Carrier ID]) ON tblDestinations.[Location ID] = tblDeliveries.[Location ID
WHERE (((tblDeliveries.[Delivery Date]) Between [Start Date] And [Ending Date])
GROUP BY tblDeliveries.[Delivery Date], tblDestinations.State, tblDestinations.City, tblDestinations.[Freight Goal], tblDeliveries.[Carrier ID], tblDeliveries.[Delivery Cost
ORDER BY tblDestinations.State, tblDestinations.Cit
PIVOT Format([Delivery Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
 
Try changing the group by statement to one of the following

GROUP BY Format(tblDeliveries.[Delivery Date],"YYYYMM"), ...
or
GROUP BY Format(tblDeliveries.[Delivery Date],"MMM"), ...

Currently you are getting a separate group for each Delivery Date plus the other
fields you are grouping on.

TRANSFORM Count(tblDeliveries.[Delivery ID]) AS Tr
SELECT tblDestinations.State,
tblDestinations.City,
tblDestinations.[Freight Goal] AS Goal,
tblDeliveries.[Carrier ID],
tblDeliveries.[Delivery Cost] AS Cost
FROM tblDestinations INNER JOIN
(tblCarriers
INNER JOIN tblDeliveries ON tblCarriers.[Carrier ID] = tblDeliveries.[Carrier ID])
ON tblDestinations.[Location ID] = tblDeliveries.[Location ID]
WHERE (((tblDeliveries.[Delivery Date]) Between [Start Date] And [Ending Date]))
GROUP BY Format(tblDeliveries.[Delivery Date],"YYYYMM"),
tblDestinations.State, tblDestinations.City,
tblDestinations.[Freight Goal],
tblDeliveries.[Carrier ID],
tblDeliveries.[Delivery Cost]
ORDER BY tblDestinations.State,
tblDestinations.City
PIVOT Format([Delivery Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
 
Back
Top