Data Presentation 3

[EnergyTed]

Hi

I have a csv file with thousands of rows which holds monthly invoice data. Unfortunately, the data is not held how I would expect it and I am finding it difficult to manipulate, please see sample data -

ID,Field1,Field2
1,Type:,THREE
2,Tariff Year:,2008
3,Invoice Period:,11: 01/02/2009 - 28/02/2009
4,Scheduled Run Date:,27/10/2009
6,Name:,A Business
8,Total Charge=,2439238192.81
1623,Name:,B Business
1625,Total Charge=,3246850.64
1673,Name:,C Business
1675,Total Charge=,5030898.19
1724,Type:,THREE
1725,Tariff Year:,2008
1726,Invoice Period:,12: 01/03/2009 - 31/03/2009
1727,Scheduled Run Date:,25/11/2009
1729,Name:,A Business
1731,Total Charge=,2690577384.89
3335,Name:,B Business
3337,Total Charge=,3622148.26
3387,Name:,C Business
3389,Total Charge=,6643882.41

There are two monthly invoices here - row 1 to 1675 being the first and row 1724 to 3389 being the second.

I get the feeling I will need to duplicate the type, tariff year, invoice period and schedule run date per row of data, but I am unsure how to piece this together.

Any help would be greatly appreciated.

Kind Regards

Ted

 

[dhookom]

Are there always three businesses for each invoice?

Duane
Hook'D on Access
MS Access MVP

 

[EnergyTed]

Hi

No, the number of businesses will vary.

 

[dhookom]

The first task I would do is to create a query with a calculated field displaying the Max(ID) where the ID is <= the current row ID. You can then create a crosstab query to create a display that has the Max(ID) as the Row Heading, Field1 as the Column Heading, and Min of Field2 as the value.

Duane
Hook'D on Access
MS Access MVP

 

[EnergyTed]

Hi Duane

I think i can create the crosstab query, but how do i create the Max(ID) where the ID is <= the current row ID?

Kind Regards

Ted

 

[dhookom]

I copied your records into a table [TT_EnergyTed] and created a query [TT_qgrpTypeID]

SELECT Max(TT_EnergyTed_1.ID) AS MaxOfID, TT_EnergyTed.ID, TT_EnergyTed.Field1, TT_EnergyTed.Field2
FROM TT_EnergyTed, TT_EnergyTed AS TT_EnergyTed_1
WHERE (((TT_EnergyTed_1.ID)<=[TT_EnergyTed].[ID]))
GROUP BY TT_EnergyTed.ID, TT_EnergyTed.Field1, TT_EnergyTed.Field2, TT_EnergyTed_1.Field1
HAVING (((TT_EnergyTed_1.Field1)="Type:"));

Then a crosstab like:

TRANSFORM Min(TT_qgrpTypeID.Field2) AS MinOfField2
SELECT TT_qgrpTypeID.MaxOfID
FROM TT_qgrpTypeID
GROUP BY TT_qgrpTypeID.MaxOfID
PIVOT TT_qgrpTypeID.Field1;

[pre]
MaxOfID Invoice Period: Name: Scheduled Run Date: Tariff Year: Total Charge= Type:
======= =========================== ========== =================== ============ ============= ======
1 11: 01/02/2009 - 28/02/2009 A 27/10/2009 2008 2439238192.81 THREE
1724 12: 01/03/2009 - 31/03/2009 A Business 25/11/2009 2008 2690577384.89 THREE
[/pre]

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I would think you would need two queries to start with. One to help normalize information about an invoice and one to help normalize the information from purchases. As stated for the invoice ID you could use the min ID within that invoice where field name = "Type". Then for purchases you would need the ID where the field name is "Name:" to be the purchase ID and then you also need the invoice ID.

Now from here you could use xtab queries or code to build the invoice table and the purchase table related to the invoice table.

SELECT 
 (Select Max(ID) from tblInvoice as A where A.fieldName = 'Type:' AND tblInvoice.[ID] >= A.ID) AS InvoiceID, 
 tblInvoice.FieldName, 
 tblInvoice.FieldValue
FROM 
 tblInvoice
WHERE tblInvoice.FieldName <> "Name:" And tblInvoice.FieldName <> "Total Charge="

Data

InvoiceID      FieldName	       FieldValue
1              Type:                   THREE
1              Tariff Year:            2008
1              Invoice Period:         11: 01/02/2009 - 28/02/2009
1              Scheduled Run Date:     27/10/2009
1724           Type:                   THREE
1724           Tariff Year:            2008
1724           Invoice Period:         12: 01/03/2009 - 31/03/2009
1724           Scheduled Run Date:     25/11/2009

Now one for the purchases

SELECT 
 (Select Max(ID) from tblInvoice as A where A.fieldName = 'Name:' AND tblInvoice.[ID] >= A.ID) AS ChargeID, 
 tblInvoice.FieldName, 
 tblInvoice.FieldValue, 
 (Select Max(ID) from tblInvoice as A where A.fieldName = 'Type:' AND tblInvoice.[ID] >= A.ID) AS InvoiceID_FK
FROM 
 tblInvoice
WHERE 
 tblInvoice.FieldName ="Name:" Or tblInvoice.FieldName = "Total Charge="

data

ChargeID           FieldName                 FieldValue          InvoiceID_FK
6                  Name:                     A Business          1
6                  Total Charge=             2439238192.81       1
1623               Name:                     B Business          1
1623               Total Charge=             3246850.64          1
1673               Name:                     C Business          1
1673               Total Charge=             5030898.19          1
1729               Name:                     A Business          1724
1729               Total Charge=             2690577384.89       1724
3335               Name:                     B Business          1724
3335               Total Charge=             3622148.26          1724
3387               Name:                     C Business          1724
3387               Total Charge=             6643882.4           1724

 

[EnergyTed]

Hi Duane

I am very close but I am missing the rest of the businesses with same MaxOfID that equals 1. In the sample data, I am trying to show businesses A, B and C for MaxOfId, where your crosstab only shows 1 row with MaxOfId that equals 1. I hope I am explaining myself clearly.


Kind Regards


Ted

 

[dhookom]

EnergyTed,
Please notice how both MajP and I have taken a lot of time and TGML to display our work and results. Please do the same and show us how you want your data to display. It is much less confusing if you make the effort.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?

SELECT I.InvoiceID,Max(T) AS Type,Max(Y) AS TariffYear,Max(P) AS InvoicePeriod,Max(R) AS ScheduledRunDate,Max(N) AS Name,Max(C) AS TotalCharge
FROM (
SELECT Max(M.ID) AS InvoiceID
,IIf(D.Field1='Type:',D.Field2,'') AS T
,IIf(D.Field1='Tariff Year:',D.Field2,'') AS Y
,IIf(D.Field1='Invoice Period:',D.Field2,'') AS P
,IIf(D.Field1='Scheduled Run Date:',D.Field2,'') AS R
FROM TT_EnergyTed D INNER JOIN TT_EnergyTed M ON D.ID>=M.ID
WHERE M.Field1='Type:' AND D.Field1 NOT In('Name:','Total Charge=')
GROUP BY D.ID,D.Field1,D.Field2
) I INNER JOIN (
SELECT Max(M.ID) AS InvoiceID
,(SELECT Max(ID) FROM TT_EnergyTed WHERE Field1='Name:' AND ID<=D.ID) AS ChargeID
,IIf(D.Field1='Name:',D.Field2,'') AS N
,IIf(D.Field1='Total Charge=',D.Field2,'') AS C
FROM TT_EnergyTed D INNER JOIN TT_EnergyTed M ON D.ID>=M.ID
WHERE M.Field1='Type:' AND D.Field1 In('Name:','Total Charge=')
GROUP BY D.ID,D.Field1,D.Field2
) C ON I.InvoiceID=C.InvoiceID
GROUP BY I.InvoiceID,C.ChargeID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[EnergyTed]

Hi Duane

The result of your crosstab is ideally what I am trying to achieve only with the additional rows for each business -

MaxOfID Invoice Period: Name: Scheduled Run Date: Tariff Year: Total Charge= Type:
======= =========================== ========== =================== ============ ============= ======
1 11: 01/02/2009 - 28/02/2009 A Business 27/10/2009 2008 2439238192.81 THREE
1 11: 01/02/2009 - 28/02/2009 B Business 27/10/2009 2008 3246850.64 THREE
1 11: 01/02/2009 - 28/02/2009 C Business 27/10/2009 2008 5030898.19 THREE
etc

You have gave me loads of help and advise which is always greatly appreciated, I will figure this out hopefully.


Ted

 

[PHV]

Did you try my suggestion ?

 

[EnergyTed]

Hi PHV

Yes, this is the results -

InvoiceID Type TariffYear InvoicePeriod ScheduledRunDate Name TotalCharge
1 RUN_THREE 2008 11: 01/02/2009 - 28/02/2009 27/10/2009 5030898.19
1724 RUN_THREE 2008 12: 01/03/2009 - 31/03/2009 25/11/2009 6643882.41

Much the same as dhookom crosstab except the Name field is blank?

Ted

 

[PHV]

With your posted sample, the result for me is:

InvoiceID	Type	TariffYear	InvoicePeriod	ScheduledRunDate	Name	TotalCharge
1	THREE	2008	11: 01/02/2009 - 28/02/2009	27/10/2009	A Business	2439238192.81
1	THREE	2008	11: 01/02/2009 - 28/02/2009	27/10/2009	B Business	3246850.64
1	THREE	2008	11: 01/02/2009 - 28/02/2009	27/10/2009	C Business	5030898.19
1724	THREE	2008	12: 01/03/2009 - 31/03/2009	25/11/2009	A Business	2690577384.89
1724	THREE	2008	12: 01/03/2009 - 31/03/2009	25/11/2009	B Business	3622148.26
1724	THREE	2008	12: 01/03/2009 - 31/03/2009	25/11/2009	C Business	6643882.41

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[EnergyTed]

Just a mistype, the results are now as expected.

Thanks for everyones help.

Ted