Programming start on problem 1

[Kurt6905]

Hi. Could anyone help me if it's not too involved? I need a VBA program that does the following:

From:

County Person Product1 Amount1 Product2 Amount2
Mercer Tom Nails 200 Screws 300

To:

County Person Product Amount
Mercer Tom Nails 200
Mercer Tom Screws 300


If anyone could get me started, that would be great. I figure copy field commands but don't know the VBA format (newbie). Thanks.

 

[SkipVought]

Youi can simply do this with a UNION query

Select County,Person,Product1,Amount1
From YourTable
UNION
Select County,Person,Product2,Amount2
From YourTable

Skip,
[sub]
[red][/red]
[/sub]

 

[Golom]

Select Country, Person, Product, Amount

From

(

Select Country, Person, Product1 As [Product], Amount1 As [Amount]
From myTable

UNION ALL

Select Country, Person, Product2, Amount2
From myTable

)

 

[JerryKlmns]

And the following query

SELECT qdfGOLOM.*
INTO myBetterTable
FROM qdfGOLOM

should get you a normalized table to store your data.

Where qdfGOLOM is

Select Country, Person, Product, Amount
From
(
Select Country, Person, Product1 As [Product], Amount1 As [Amount]
From myTable
WHERE Amount1 > 0

UNION ALL

Select Country, Person, Product2, Amount2
From myTable
WHERE Amount2 > 0
)
the query from Golom (a bit modified to select only records that should be there) -- no offense Skip but he had the column names ready!

 

[Golom]

JerryKlmns

Good call Jerry. Just to beat this horse to death

Select Country, Person, Product, Amount
From
(
Select Country, Person, Product1 As [Product], Amount1 As [Amount]
From myTable
WHERE Amount1 [red]IS NOT NULL[/red]

UNION ALL

Select Country, Person, Product2, Amount2
From myTable
WHERE Amount2 [red]IS NOT NULL[/red]
)

Negative Amounts may be legit values.