Tried to do this in Access and it didn't work out. Maybe Excel can?

[kmprice710]

I have five tables: car sales, motorcycle sales, jet ski sales, trailer sales, and boat sales.
Each table has different columns but they have some column elements: customer ID #, company name, customer ID #, customer name, year.
Each row has the entire year's purchases by a customer but not every customer has a purchase row on each table.

I'd like to have 1 table that has 1 row per customer per year with all of their purchases, regardless of car, boat, jet ski, trailer and motorcycle.

How can I make that table? If the same customers were on each sheet and each of them had 1 row in each sheet, it would be a simple sort. But it won't work with this.

Any ideas?

 

[alansidman]

How about some sample data that we can manipulate. Use XL2BB or post to third party location

 

[kmprice710]

five tables:

Filebin | 9hi71vlbnhdif2vm

Convenient file sharing. Think of it as Pastebin for files. Registration is not required. Large files are supported.

filebin.net

 

[sandy666]

something like this with all years and customers?
(this is a part of the whole table only)

customer	Attribute	2018	2017	2016	2015	2014	2013
A.J.	fpuga		0.592252187
A.J.	fpugm		0.17405573
A.J.	knetprofit		0.5280672	0.873052632	0.265130594
A.J.	krtdte		0.691329766	0.996169416	0.614779124
A.J.	pcmp	0.566055312	0.416011319				0.162406332
A.J.	pnetprofit	0.790287613	0.957158334				0.408600093
A.J.	pntwe	0.819283788	0.683903087				0.755086352
A.J.	posipu		0.365696814
A.J.	posk		0.369115911	0.398187725	0.177100008
A.J.	posp	0.813214702	0.173177955				0.961460759
A.J.	pqty	0.877587086	0.272831902				0.509159208
A.J.	prsatyds		0.888136026	0.149765888	0.451321763
A.J.	prtdopps		0.718084481	0.665324097	0.027963905
A.J.	prwe		0.544875512	0.316206544	0.625206445
A.J.	ptdte	0.600030283	0.601157389				0.036009244
A.J.	ptpuyds		0.816088369
A.J.	pupata		0.200137826
A.J.	pupatm		0.45716236
A.J.	pupt		0.75041804
A.J.	qtyk		0.396460728	0.253092031	0.023308999
A.J.	qtyr	0.874754247	1.149902718	0.471988212	0.411740795		0.748653542
A.J.	recsatyds	0.107802051	1.327778207	0.007155377	0.042970209		0.012260349
A.J.	rectdopps	0.710272457	1.839496199	0.133055517	0.325038593		0.211008432
A.J.	recwe	0.694708449	1.084982669	0.53590482	0.859076377		0.577116673
A.J.	rnetprofit	0.056934562	1.680870359	0.902929324	0.940390978		0.616986793
A.J.	rpos	0.736783933	0.770335602	0.090740268	0.342277679		0.383575028
A.J.	rseg	0.602911028	1.421060655	0.277832408	0.400126997		0.656817055
A.J.	rtdte	0.122953758	0.595150526	0.968741839	0.759984035		0.490250829
A.J.	segk		0.247770751	0.741278198	0.784728287
A.J.	segp	0.347241832	0.861661674				0.748566193
A.J.	segpu		0.079107872
Aaron	qtyr					0.785358682	0.450409595
Aaron	recsatyds					0.18138537	0.77242115
Aaron	rectdopps					0.067563974	0.686528424
Aaron	recwe					0.953513875	0.156734509
Aaron	rnetprofit					0.393526135	0.120645165
Aaron	rpos					0.111256184	0.093293536
Aaron	rseg					0.689518473	0.304676596
Aaron	rtdte					0.687708901	0.804575113
Abraham	Posd	0.37680352
Abraham	dint	0.815831344
Abraham	dnetprofit	0.852250692
Abraham	ffwe	0.685579935
Abraham	frecwe	0.963939056
Abraham	itdwe	0.014894218
Abraham	pddwe	0.465075359
Abraham	segd	0.988283973
Abraham	skdwe	0.373301173
Abraham	tadwe	0.469808099
Abraham	tfldwe	0.85139392
Abraham	tsdwe	0.93162591
Abraham	ttdwe	0.60369716
Adam	fpuga			0.88834034	0.474690465	0.35061118	0.010475921
Adam	fpugm			0.788848844	0.988193761	0.008507457	0.220956174
Adam	posipu			0.373190207	0.845817935	0.378303667	0.776285648
Adam	ptpuyds			0.37455427	0.981190131	0.778092127	0.038152795
Adam	pupata			0.522618966	0.545767188	0.048856521	0.946334251
Adam	pupatm			0.642746545	0.579786846	0.346998036	0.162199508
Adam	pupt			0.952863119	0.693285337	0.436567883	0.718412763
Adam	qtyr		0.356456327
Adam	recsatyds		0.285751933
Adam	rectdopps		0.67328859
Adam	recwe		0.710637512
Adam	rnetprofit		0.326730297
Adam	rpos		0.865474389
Adam	rseg		0.902865201
Adam	rtdte		0.317692643
Adam	segpu			0.741240428	0.009896819	0.158249526	0.911438803
Adrienne	Posd	0.673214953
Adrienne	dint	0.337802272

 

[sandy666]

or like this
(this is a part of the whole table (359 records) )

customer	2018	2017	2016	2015	2014	2013
A.J.	3.907120485	17.68833651	7.781424296	7.051144788		3.696668701
Andre	21.77616784	10.1784236
Andrew	9.249491972	10.29554928	4.257770998	8.175941547	4.591976707	8.220918327
Anthony	5.801129574	17.61092465	7.423364413	9.280287911	2.966607465	14.1429328
Ardarius			7.690210688	8.695260419	2.932084826
Aub		4.480019601		7.64471029
B.J.			7.44023995
Benton		3.780314979	4.056448049	8.721642291
Bill						3.798178195
Bre'					11.62720822	5.732247033
Brett			2.431563204	4.362519089
Brian	26.329759	8.756812386			3.610224679	9.656455979
Bruce			10.16944513
Bryan			8.909366668	7.768745837	4.876967764
Bryce			5.863038346	5.975612414
Bud						7.794288472
Calvin		5.060888888	7.390531419	2.903785079	5.678560341
Chase	4.662565195
Chris	6.911565596	6.726585103		7.168551609	2.522489518	4.510216105
Christion					9.999563614	7.505871481
Craig	6.187419549	4.51522091
Cyrus				7.265846188	9.493519728	8.34067888
Dakota			8.565760035	3.843690225
Damien	3.07101922	10.61198659	3.861738384	8.089786164
Darius		6.75650714			10.09351982	7.342327682
Darrin						6.474751112
Deandrew					7.508082372	7.624115965
Dee						13.59873204
Deltron	7.189426852	9.031859454

 

[sandy666]

sorry, I made mistake in previous posts so ignore posts #4 and #5
here is proper (I hope) result

customer	2013	2014	2015	2016	2017	2018
A.J.			2.969539125	4.393076531	4.383840375
Andre					3.784093081	8.708609273
Andrew	3.955659342		4.460179033			3.720617846
Anthony	5.477162068		5.473754738
Ardarius			4.730771671	3.485638222
Aub			4.085479487
B.J.				3.430484325
Benton			4.551309614
Bill	3.798178195
Bre'		4.774233996
Brett				2.431563204
Brian	5.019885426				4.889348729	4.734792169
Bruce				4.761774175
Bryan		4.876967764	3.783265742	4.309966557
Bryce				5.863038346
Bud	4.490765017
Calvin				3.728680819
Chase						4.662565195
Chris			2.841610435
Christion	4.123950414	5.185098743
Craig					4.51522091
Cyrus		3.961034765	3.425310611
Dakota				4.922988147
Damien			3.90844924		4.204108602
Darius	4.801859607
Darrin	2.617935673
Deandrew	3.289293595	4.217275502
Dee	4.63748934
Deltron					4.167968719	3.578327602
Demetrius					5.047286979
Denzel					3.135299083
Derek						3.989988733
Derrick				4.853619812
Devin	3.425178059	4.011605816
Devon		5.795599514
Donte	4.294694976
Drew	4.570504535	3.559559165
Dylan			4.608635125
Eddie				4.246338156
Fransohn	4.928011432
Garrett	4.094817838	2.094052836	4.035868182		7.326396746	7.375972754
Gehrig				2.817147435
Gerrit						3.936989842
Greg	4.11713579
Hakeem			4.057324338
Hale			4.145772638	3.611885167
Harrison	4.664953721
Hayden					4.026848684
Henry					3.72152292
Imani	4.134599915	3.680952919	5.307589194
Irv						3.560876382
Jacob		3.345235012
Jalen		3.872752975
Jamarcus	3.943737904	3.360937391
Jamari	3.780711299
Jaylen						4.752902322
Jeremiah						4.144851258
Jimmy	4.900976732
Jon		4.700436216
Jonathan					3.496960994
Joseph		2.779995076				5.168815168
Josh		3.651717597
Joshua				3.688482762	3.150436352	3.630045302
Kailon					2.214882734	3.504364534
Keith			3.558795626
Kendall						3.758765721
Kennard	3.453702718
Kenny	4.453016044
Kenyan	2.213384676		2.924004393
Kobie					3.291700364
Kwadarrius				4.9884599
L.T.	4.443770451	4.271165991
LaVonne					3.921876047
Lamarcus	4.817310496	5.472683588
Landon		4.325070084
Larry				3.455654336
Malachi	5.319136855
Malik					4.141738503
Manny			3.138289024		5.16222858
Matt			4.035478414
Maudrecus		3.032565216
Maurice		2.038269004
Maverick						2.888210928
Michael			4.973062797
Mikee				3.597378146	4.530106379
Miller						3.797229043
Minkah			5.12116407		2.75224715
Mykel	4.374190035	4.313309512
Nate		3.824088383				4.806774073
Noland					4.120153069
O.J.		3.139730377
Paco		4.261627851
Richard			4.58687766
Riley	3.440687973
Rodarius		3.710387081
Rona				2.705717505	3.338110406	3.25685981
Ronnie			5.27016135
Sam	3.484365926
Santonio	4.856669258
Shawn					3.311475006	3.646491606
Shayne		2.376549801
Starling						3.901377705
Timothy			3.54192703	3.576193057	4.074315876
Trevon				3.812661243	4.888921563
Tristan	5.680589018
Ty			3.859302433
Tyler	3.470333328		4.085051884
Tyrell			4.493381453	3.486600038
Tyren		4.461127624
Van			5.045434778	3.975983416	4.354351637	4.299331885
Weston		3.408753077
Xavian				4.411784729	3.902007281
Xzavier	2.676117253	3.623650562
Zach		4.437162149		4.163459825

 

[Fluff]

Also posted as an Access problem Can't run the query I need, anybody help?

 

[kmprice710]

something like this with all years and customers?
(this is a part of the whole table only)

customer	Attribute	2018	2017	2016	2015	2014	2013
A.J.	fpuga		0.592252187
A.J.	fpugm		0.17405573
A.J.	knetprofit		0.5280672	0.873052632	0.265130594
A.J.	krtdte		0.691329766	0.996169416	0.614779124
A.J.	pcmp	0.566055312	0.416011319				0.162406332
A.J.	pnetprofit	0.790287613	0.957158334				0.408600093
A.J.	pntwe	0.819283788	0.683903087				0.755086352
A.J.	posipu		0.365696814
A.J.	posk		0.369115911	0.398187725	0.177100008
A.J.	posp	0.813214702	0.173177955				0.961460759
A.J.	pqty	0.877587086	0.272831902				0.509159208
A.J.	prsatyds		0.888136026	0.149765888	0.451321763
A.J.	prtdopps		0.718084481	0.665324097	0.027963905
A.J.	prwe		0.544875512	0.316206544	0.625206445
A.J.	ptdte	0.600030283	0.601157389				0.036009244
A.J.	ptpuyds		0.816088369
A.J.	pupata		0.200137826
A.J.	pupatm		0.45716236
A.J.	pupt		0.75041804
A.J.	qtyk		0.396460728	0.253092031	0.023308999
A.J.	qtyr	0.874754247	1.149902718	0.471988212	0.411740795		0.748653542
A.J.	recsatyds	0.107802051	1.327778207	0.007155377	0.042970209		0.012260349
A.J.	rectdopps	0.710272457	1.839496199	0.133055517	0.325038593		0.211008432
A.J.	recwe	0.694708449	1.084982669	0.53590482	0.859076377		0.577116673
A.J.	rnetprofit	0.056934562	1.680870359	0.902929324	0.940390978		0.616986793
A.J.	rpos	0.736783933	0.770335602	0.090740268	0.342277679		0.383575028
A.J.	rseg	0.602911028	1.421060655	0.277832408	0.400126997		0.656817055
A.J.	rtdte	0.122953758	0.595150526	0.968741839	0.759984035		0.490250829
A.J.	segk		0.247770751	0.741278198	0.784728287
A.J.	segp	0.347241832	0.861661674				0.748566193
A.J.	segpu		0.079107872
Aaron	qtyr					0.785358682	0.450409595
Aaron	recsatyds					0.18138537	0.77242115
Aaron	rectdopps					0.067563974	0.686528424
Aaron	recwe					0.953513875	0.156734509
Aaron	rnetprofit					0.393526135	0.120645165
Aaron	rpos					0.111256184	0.093293536
Aaron	rseg					0.689518473	0.304676596
Aaron	rtdte					0.687708901	0.804575113
Abraham	Posd	0.37680352
Abraham	dint	0.815831344
Abraham	dnetprofit	0.852250692
Abraham	ffwe	0.685579935
Abraham	frecwe	0.963939056
Abraham	itdwe	0.014894218
Abraham	pddwe	0.465075359
Abraham	segd	0.988283973
Abraham	skdwe	0.373301173
Abraham	tadwe	0.469808099
Abraham	tfldwe	0.85139392
Abraham	tsdwe	0.93162591
Abraham	ttdwe	0.60369716
Adam	fpuga			0.88834034	0.474690465	0.35061118	0.010475921
Adam	fpugm			0.788848844	0.988193761	0.008507457	0.220956174
Adam	posipu			0.373190207	0.845817935	0.378303667	0.776285648
Adam	ptpuyds			0.37455427	0.981190131	0.778092127	0.038152795
Adam	pupata			0.522618966	0.545767188	0.048856521	0.946334251
Adam	pupatm			0.642746545	0.579786846	0.346998036	0.162199508
Adam	pupt			0.952863119	0.693285337	0.436567883	0.718412763
Adam	qtyr		0.356456327
Adam	recsatyds		0.285751933
Adam	rectdopps		0.67328859
Adam	recwe		0.710637512
Adam	rnetprofit		0.326730297
Adam	rpos		0.865474389
Adam	rseg		0.902865201
Adam	rtdte		0.317692643
Adam	segpu			0.741240428	0.009896819	0.158249526	0.911438803
Adrienne	Posd	0.673214953
Adrienne	dint	0.337802272

Here is an example of the output that I am looking for. I did 2013 Lacoste manually.

Filebin | huluixz42y6184z4

Convenient file sharing. Think of it as Pastebin for files. Registration is not required. Large files are supported.

filebin.net

 

[kmprice710]

I have five tables: car sales, motorcycle sales, jet ski sales, trailer sales, and boat sales.
Each table has different columns but they have some column elements: customer ID #, company name, customer ID #, customer name, year.
Each row has the entire year's purchases by a customer but not every customer has a purchase row on each table.

I'd like to have 1 table that has 1 row per customer per year with all of their purchases, regardless of car, boat, jet ski, trailer and motorcycle.

How can I make that table? If the same customers were on each sheet and each of them had 1 row in each sheet, it would be a simple sort. But it won't work with this.

Any ideas?

Having done 2013 Lacoste manually, the ID column was just an Access automatically generated value and should be ignored for every table.

These columns exist in every table:

companyid

incpost

year1

company

customerid

customer

So they would not need to be duplicated in the output.

 

[sandy666]

check if this is what you want

s666-PQ-kmprice710.xlsx

Microsoft Excel Workbook

1drv.ms

btw. update your profile ( Account details ) about Excel version and OS, don't forget to scroll down and save