Help make table with various options

[tmcrouse]

My data: (actually I have a lot more than this but this is an example)

mpin	claim	dos	descript	item	qty	unit	total	invoice	contract
3	123	11/15/2011	60MM ACE SHELL, HEMI, HOLED	1706-0-0060	1	$3,450.00	$3,450.00	$39,985.00	Implant Pass-through: PPR Tied to Invoice
3	123	11/15/2011	20MM, 6.5 CANCELLOUS BONE SCREW	0013-1-6520	1	$250.00	$250.00		Implant Pass-through: PPR Tied to Invoice
3	123	11/15/2011	LOCKING NUT, TI, 30MM	3449-03000-0	1	$350.00	$350.00		Implant Pass-through: PPR Tied to Invoice
3	123	11/15/2011	26 X 30, PROX BODY, HA/PLASMA	3461-5260-A	1	$4,950.00	$4,950.00		Implant Pass-through: PPR Tied to Invoice
3	123	11/15/2011	10CC DBM PUTTY	DBM510	2	$2,950.00	$5,900.00		Implant Pass-through: PPR Tied to Invoice
3	123	11/15/2011	.5ML BIODFACTOR HUMAN AMNION ALLOGRAFT	BD50	1	$4,000.00	$4,000.00		Implant Pass-through: PPR Tied to Invoice
3	123	11/15/2011	30CC CRUSHED CANCELLOUS	CC30	1	$1,200.00	$1,200.00		Implant Pass-through: PPR Tied to Invoice
3	123	11/15/2011	4X6CM HUMAN AMNION PATCH	HA460	1	$5,000.00	$5,000.00		Implant Pass-through: PPR Tied to Invoice
3	123	11/15/2011	15MM, 6.5 CANCELLOUS BONE SCRW	0013-1-6515	1	$250.00	$250.00		Implant Pass-through: PPR Tied to Invoice
3	123	11/15/2011	36X60 XLINK ACE INSERT 10 HOOD	1008-0-3660	1	$3,150.00	$3,150.00		Implant Pass-through: PPR Tied to Invoice
3	123	11/15/2011	NECK, STANDARD 36MM X 34MM	3441-13634-A	1	$4,795.00	$4,795.00		Implant Pass-through: PPR Tied to Invoice
3	123	11/15/2011	STEM, 18X160 FLUTED, STRAIGHT	3422-11816-0	1	$3,795.00	$3,795.00		Implant Pass-through: PPR Tied to Invoice
3	123	11/15/2011	NEU, 36MM BIOLOX DELTA FEM HD	0005-0-3602	1	$2,895.00	$2,895.00		Implant Pass-through: PPR Tied to Invoice
4	899	11/20/2011	VeriFLEX (MR) US 4.5 x 16mm		1	$560.00	$560.00	$1,120.00	Implant Pass-through: PPR Tied to Invoice
4	899	11/20/2011	VeriFLEX (MR) US 3.5 x 12mm		1	$560.00	$560.00		Implant Pass-through: PPR Tied to Invoice
4	980	11/29/2011					$0.00	$0.00	Implant Pass-through: PPR Tied to Invoice
4	400	11/29/2011	FG,Promus Element Plus, MR,US 3.5x28	14714363	1	$1,485.00	$1,485.00	$1,485.00	Implant Pass-through: PPR Tied to Invoice

I am trying to do a make table to take the invalid data and put it in a table of its own. But it has to be the entire claim. Not just 1 part of the claim that is wrong. A valid claim is one that has the mpin, claim, dos, descript, qty, unit, total and contract in all columns and the invoice amount is in one itself. So, if I try running a query and say give me the nulls and put them in the table, it does not work because it selects only those lines. For instance, from the above it should create a table like this: This is because claim 899 does not have the item information. Then claim 980 is missing all information.

mpin	claim	dos	descript	item	qty	unit	total	invoice	contract
4	899	11/20/2011	VeriFLEX (MR) US 4.5 x 16mm		1	$560.00	$560.00	$1,120.00	Implant Pass-through: PPR Tied to Invoice
4	899	11/20/2011	VeriFLEX (MR) US 3.5 x 12mm		1	$560.00	$560.00		Implant Pass-through: PPR Tied to Invoice
4	980	11/29/2011					$0.00	$0.00	Implant Pass-through: PPR Tied to Invoice

[June7]

Why do you need to move records to another table as opposed to just filtering?


You want any records where any of those listed fields is Null?

[tmcrouse]

Why do you need to move records to another table as opposed to just filtering?


You want any records where any of those listed fields is Null?

It is not me that wants it that way, it is management. They want valid as in complete claims in one database and the incomplete in another in order for the claims people to research and complete the incomplete claims. They do not want any filters, they want it in a separate table so there is no chance of valid table getting altered or deleted. I also cannot limit what the users are doing because I am not the DB admin. I am only an analyst and have no power to limit usage. I am just trying to provide what management wants. I have zero say or power to change their mind. I have 1500 claims with about 5000 rows and to manually go through each one and move to a separate table is not only not productive but there is a high chance of error. I automated the intake process already due to errors. The team was opening up a single submitted worksheet with 1 claim and cutting and pasting into the database. I was able to set up macros to automate and get it all into a database, but I cannot figure out the right code to complete the final task. I am sure it is a loop and requires if statements but I just cannot get it quite right.

[June7]

Please answer the second question.