merge fields?

[rodeo]

Howdy, a newbie here, and new to access, but pretty strong computer skills..

need you're help please....

trying to "merge" fields - i think merge is the right name for it...

here's an example of the data...

100 ___
200 200
300 ___
___ 400
500 500
600 ___
___ 700

where the ___ = empty

I need it to look like this, a single field..

100
200
300
400
500
600
700

the values in the two fields are always the same if the exist in both fields..

thanks in advance!!!

Happy Holidays!

 

[Sum Guy]

In your query create a new field giving it a descriptive name, then do the code to merge the cells.
Example:
Descriptivename[field1]&" "&[field2])

For some reason a purple smilie comes up, it should be a colon.

 

[pbaldy]

It looks like you want something like:

NewField: IIf(Nz(Field1, "") = "", Field2, Field1)

 

[rodeo]

Howdy Again!

here's the sql that has built my DB...

SELECT [08].station, [09].station, [08].precip, [09].precip
FROM 08 RIGHT JOIN 09 ON [08].station=[09].station;
UNION ALL
SELECT [08].station, [09].station, [08].precip, [09].precip
FROM 08 LEFT JOIN 09 ON [08].station=[09].station;
UNION ALL SELECT [08].station, [09].station, [08].precip, [09].precip
FROM 08 INNER JOIN 09 ON [08].station=[09].station;


I need to merge [08].station and the [09].station fields...

the two suggestions above looked very promising, but I can't get them to work. I inserted them directly below the sql statement I posted above, and tried to change it to reflect my field names, but I got syntax errors...

would one of you try to put together the statement so I can learn what I was doign wrong? Thanks a whole bunch for the help!

 

[neileg]

If the fields are numeric, then you don't use '&' you use '+' and with the Nz() included becomes

Descriptivename: (Nz([field1])+Nz([field2]))

 

[tehNellie]

Unless I'm reading this wrong could you not use a union query to get all the combinations

SELECT column1 as Commonname 
FROM Table
UNION ALL
SELECT Column2 as Commonname
FROM table

Then query the union to get single values:

SELECT DISTINCT CommonName
FROM UnionQuery
WHERE commonName IS NOT NULL (or <> "" if you zls instead of NULL)
ORDER by CommonName

It's clumsy I admit, but should in the first instance give you the unique values from the two columns into a single column result

 

[Ron_dK]

I need to merge [08].station and the [09].station fields...

Actually the solution from pbaldy does the trick :

You'll need to revise your query ( based on the table station) to read something like :

NewField: IIf(Nz([08], "") = "", [08], [09])


Hth

 

[rodeo]

You guys rock!!! i got it! And, i learned a bunch as I made my mistakes on the path to success. thanks!

I'm now looking for a sum of two fields, I'll poke around, but if someone could post that, I'd appreciate it!

thanks!!!

 

[neileg]

You guys rock!!! i got it! And, i learned a bunch as I made my mistakes on the path to success. thanks!

I'm now looking for a sum of two fields, I'll poke around, but if someone could post that, I'd appreciate it!

thanks!!!

As I said
Descriptivename: (Nz([field1])+Nz([field2]))

 

[Ron_dK]

You could do something like :
Expr1: Sum(Nz([08])+(Nz([09]))) , but I would not sum in a query.
I would make an textbox on my form and put something like this in the control source :

DSum("[08]+[09]", "tablestation", "[08] = not null)


Hth

 

[rodeo]

Seriously, YOU all are Great! Thanks!!!

can you explain what the NZ is in the expression:
Expr2: (Nz([field3])+Nz([field4]))

thanks for helping a newbie out, I'm making big steps on learning this thanks to ya'll!

 

[Ron_dK]

Maybe this might help :

http://msdn2.microsoft.com/en-us/library/aa172237.aspx

 

[Rabbie]

Seriously, YOU all are Great! Thanks!!!

can you explain what the NZ is in the expression:
Expr2: (Nz([field3])+Nz([field4]))

thanks for helping a newbie out, I'm making big steps on learning this thanks to ya'll!

NZ(Variable, Value) is a function that substitutes the value if the Variable is Null. if you don't specify Value then it uses 0. See access Help for more info

 

[Sum Guy]

pbaldy and the others were correct.
I overlooked the fact it was a number field.