Pivot an Access table? (or in Excel) (1 Viewer)

bobunknown

Registered User.
Local time
Today, 07:23
Joined
May 25, 2018
Messages
77
Hi all,

I have a table with numerous records in a access table in the following format:


Debtor_AC_NoPIM_NoTrade_NamePremessis_TypeBin IDBin_Size_IDEmptying CostBin CostCollectionNoIndivCostRental TradeRental S2
00177847PIM:01154The BankTrade - Bins
979​
T 140
£2.80​
£27.50​
52​
£145.60​
£0£0
00001407PIM:00232YMCASchedule 2 - Bins
602​
S2 360
£4.50​
£55.00​
52​
£234.00​
£0£0
00001407PIM:00232YMCASchedule 2 - Bins
1005​
S2 360
£4.50​
£55.00​
52​
£234.00​
£0£0
00001724PIM:00218Short Break ServiceSchedule 2 - Bins
370​
S2 240
£3.20​
£40.00​
52​
£166.40​
£0£0
00006839PIM:00193Coronation GardensTrade - Bins
263​
T 360
£7.10​
£55.00​
26​
£184.60​
£0£0
00007171PIM:00525Shajan Indian RestaurantTrade - Bins
613​
T 240
£4.80​
£40.00​
52​
£249.60​
£0£0
00007171PIM:00525Shajan Indian RestaurantTrade - Bins
614​
T 240
£4.80​
£40.00​
52​
£249.60​
£0£0
00007171PIM:00525Shajan Indian RestaurantTrade - Bins
615​
T 240
£4.80​
£40.00​
52​
£249.60​
£0£0
00007171PIM:00525Shajan Indian RestaurantTrade - Bins
616​
T 240
£4.80​
£40.00​
52​
£249.60​
£0£0
00007171PIM:00525Shajan Indian RestaurantTrade - Bins
617​
T 240
£4.80​
£40.00​
52​
£249.60​
£0£0
00007171PIM:00525Shajan Indian RestaurantTrade - Bins
969​
TR 1100
£19.00​
£1.60​
52​
£988.00​
£83.20£0

The table records information relating to the collection of bins (exciting stuff). What I need is to find a way to place all the information from Bin_Size_ID right, on one row. So that it would look like so:

Debtor_AC_NoPIM_NoTrade_NamePremessis_TypeBin IDBin_Size_IDEmptying CostBin CostCollectionNoIndivCostRental TradeRental S2Bin_Size_ID1Emptying Cost1Bin Cost1CollectionNo1IndivCost1Rental Trade1Rental S21
1407​
PIM:00232Salvation ArmySchedule 2 - Bins
602​
S2 360
£4.50​
£55.00​
52​
£234.00​
£0​
£0​
S2 360
£4.50​
£55.00​
52​
£234.00​
£0​
£0​


Dose anyone know a good method? I have looked in to pivot tables in Access and Excel but had little success. The end goal is to have the information mail merged in to a series of letters, one for each business, hence why they need to all be on one row (unless im wrong about that)

Any help anyone could provide will be much appreciated.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:23
Joined
May 7, 2009
Messages
19,169
use Total Query.
 

bobunknown

Registered User.
Local time
Today, 07:23
Joined
May 25, 2018
Messages
77
Can you please expand, I have just had a look at total queries and I cant see how they would be of use? I need the raw data stacked end to end if that makes sense.
 

isladogs

MVP / VIP
Local time
Today, 14:23
Joined
Jan 14, 2017
Messages
18,186
You could try using crosstab queries for this.
Unfortunately its too complex to do in one query so you would need to do more than one for different fields and join them together

Alternatively you could try a pivot table if you have A2007/2010 or use Excel for the purpose.
Its years since I've done this so can't advise on specifics.

But if the idea is to get all related data into one record for mail merge, I would check whether this is absolutely necessary
 
Last edited:

bobunknown

Registered User.
Local time
Today, 07:23
Joined
May 25, 2018
Messages
77
Thanks for the suggestions. ill explore crosstab queries.

I'm also looking in to using something like the Vlookup function in excel.

(y)
 

plog

Banishment Pending
Local time
Today, 09:23
Joined
May 11, 2011
Messages
11,611
You should use an Access Report for your letters and feed your Bin/Rental data into rows on those letters. This would not require the manipulation of the data. The letter would look like this:

Dear [Trade_Name];

Static paragraph 1 here. Blah blah blah

Static paragraph 2 here. Blah Blah blah. And here is the bin information:

Record1 data here
Record2 data here
Record3 data here
....

Closing paragraph here. Blah blah blah.

I would read up on how to make reports in Access.
 

Users who are viewing this thread

Top Bottom