Converting an excel spreadsheet to access. Stuck on duplicate removal and sums.

[Sirenrof]

Hello all, I am trying to convert an excel spreadsheet to a database because there is too much data for excel to handle. I have managed to recreate the cartesian join in access; however, I cannot figure out how to remove duplicate entries from my query. I also cannot figure out how to add up all of my player costs.



I need to remove duplicate data that looks like this:

QB1, RB1, RB2, WR1, WR2, WR3, TE1, K1, DEF1
QB1, RB1, RB2, WR3, WR2, WR1, TE1, K1, DEF1
QB1, RB1, RB2, WR1, WR3, WR2, TE1, K1, DEF1
QB1, RB2, RB1, WR1, WR2, WR3, TE1, K1, DEF1
QB1, RB2, RB1, WR3, WR2, WR1, TE1, K1, DEF1
QB1, RB2, RB1, WR1, WR3, WR2, TE1, K1, DEF1

These are all of the same. In excel it seems easy to remove these kind of entries. I can't figure out how to do this in Access even after searching forums and the web.

I've attached a zip file with both the spreadsheet and database. Any help would be appreciated, thank you!

[Sirenrof]

I think this may take care of my duplicate issue:

SELECT [_hQB].hQB, [_hRB1].hRB1, [_hRB2].hRB2, [_hWR1].hWR1, [_hWR2].hWR2, [_hWR3].hWR3, [_hKick].hKick, [_hTE].hTE, [_hDEF].hDEF
FROM _hDEF, _hKick, _hQB, _hRB1, _hRB2, _hTE, _hWR1, _hWR2, _hWR3
WHERE ((([_hRB1].hRB1)<>[hRB2] And ([_hRB1].hRB1)<[hRB2]) AND (([_hWR1].hWR1)<>[hWR2] And ([_hWR1].hWR1)<>[hWR3] And ([_hWR1].hWR1)<>[hWR2]) AND (([_hWR1].hWR1)<[hWR2] And ([_hWR2].hWR2)<[hWR3]));

Can anyone confirm this? I sorted and it looks like it solves it? If it does, then I really just need help with the summing portion. Thanks!

[CJ_London]

a much simpler way is

SELECT DISTINCT * FROM _hDEF, _hKick, _hQB, _hRB1, _hRB2, _hTE, _hWR1, _hWR2, _hWR3

[Sirenrof]

thank you. does this eliminate any of the other portion of my query? i am specifically worried about getting multiple mirrored combinatons.

[Sirenrof]

a much simpler way is

SELECT DISTINCT * FROM _hDEF, _hKick, _hQB, _hRB1, _hRB2, _hTE, _hWR1, _hWR2, _hWR3

This does not actually work because I cannot have something like this:

QB1A

RB1H

RB2G

WR1H

WR1H

WR1H

TE1C

DEF1A

K1C

WR1H cannot fill 3 spots. WR1 can only be in one spot.