Digital Paint Discussion Board

Digital Paint Community => Other Stuff => Topic started by: KnacK on February 06, 2009, 02:27:32 PM

Title: Database Query Help
Post by: KnacK on February 06, 2009, 02:27:32 PM

I have an access database that contains data from our fuel tanks at our 4 plants.

Table name = exporttrans
field names:
siteid = sites 1 to 4
vehicleid = id number for trucks
trantime = transaction date/time formmated to just mm/yyyy
quantity - quantity of fuel pumped at time of transaction

Heres the current query:

Code: [Select]

TRANSFORM Sum(ExportTrans.[QUANTITY]) AS SumOfQUANTITY
SELECT ExportTrans.[TRANTIME], ExportTrans.[VEHICLEID], Sum(ExportTrans.[QUANTITY]) AS [Total Of QUANTITY]
FROM ExportTrans
GROUP BY ExportTrans.[TRANTIME], ExportTrans.[VEHICLEID]
PIVOT ExportTrans.[SITEID];

THis provides a table that goes:

Date     Truck ID    Quantitytotal   site 1   site 2   site 3   site 4

CUrrently, the typical output looks like this:

11/2008   000045      45.7            45.7
11/2008   00076        55.2                                55.2

So you see where this is going. But, trucks fill up multiple times a day and I am looking at getting just monthly stats.

Essentially, I want one line entry per truck per month.

Any thoughts?


*on edit*
The date stamp is actually a date/time stamp that I have formatted out. So the actuall date data is time/date stored as mm/dd/yyyy hh:mm:ss.  That might be the cause of my multiple date entries.  So, I am assuming that in my query, I need to filter there for the mm/yyyy to get all transaction for one month.

Make sense?

Title: Re: Database Query Help
Post by: IronFist on February 06, 2009, 10:57:13 PM

I don't use Access/SQL Server much at all, but this should work: do your transformation of the timestamp into a 'MM/YYYY' string when selecting, so that GROUP BY will behave as you want (grouping records per month). I'll take a guess at what this would look like:

TRANSFORM Sum(ExportTrans.[QUANTITY]) AS SumOfQUANTITY
SELECT MONTH(ExportTrans.[TRANTIME]) & "/" & YEAR(ExportTrans.[TRANTIME]) AS [TRANMONTH], ExportTrans.[VEHICLEID], Sum(ExportTrans.[QUANTITY]) AS [Total Of QUANTITY]
FROM ExportTrans
GROUP BY [TRANMONTH], ExportTrans.[VEHICLEID]
PIVOT ExportTrans.[SITEID];

Title: Re: Database Query Help
Post by: KnacK on February 07, 2009, 09:56:49 AM

That looks exactly like what I need. I knew that  I would have to do it while selecting.  My db skills are a lot rusty.

I'll give this a shot later today.  Thanks for the tip IF.