Query problem

[lamkee]

Hi everyone,
I would like to know can I calculate the data like below by using query?

Database:


FIELD 1 FIELD 2 FIELD 3 FIELD 4 FIELD 5
BOX1 111 333 222 12000
BOX1 111 333 222 15000


If filed 1 to 4's data is same,then show the data like below,

BOX1 111 333 222 12000 15000

[jo15765]

Are you wanting a query that would calculate the value in your fields?

[lamkee]

Yes,I am looking a way to calculate the data like that!
Anyone can help?

[jo15765]

This will total the data input into each box individually if that is what you are wanting. Build your query, and when in the design view of the query, click the Sigma button to add the word "Total." In the field "Total" add the word "Sum" and run the query and it will total all of the data that has been input into the fields you are wanting to sum.

[lamkee]

Thank you for your reply!
Each line's record like below,
FIELD 1 FIELD 2 FIELD 3 FIELD 4 FIELD 5
BOX1 111 333 222 12000
BOX1 111 333 222 15000

I want to merge the data I first 4 filed 's data is the same,but not sum up.

Move 15000 and 12000 to new field / same field
Just like below! One record show 2 record's data
BOX1 111 333 222 12000 15000

[jo15765]

so you are wanting to merge the data from box1 to only be one entry and remove the duplicate entries also?

[lamkee]

Yup! Any idea?

[jo15765]

are field1 field2 etc. all bound to the same table? also, are you needing to do this fo multiple "boxes" or just box1

[lamkee]

One table ,and mutiples boxes !
some box just has one record

[jo15765]

If I am understanding correctly, this approach should conquer it! Read this article and let me know if this will work.

http://articles.techrepublic.com.com...1-1043732.html

[lamkee]

This article for find out the duplicate records!
But what I want is not only find out duplicate data also need merge the record together(not sum up)

[jo15765]

Right, I thought that may be a quick fix to finding duplicate entries that your database may contain. I may have jumped the gun a little bit, and you not be needing that information.

Here is a query that will combine all fields into one, when building the query go to the SQL View and input this code:

SELECT [Field_1] & "" & [Field_2] & "" & [Field_3] & "" & [Field_4] AS Combined
FROM Table1;

Where the "Table1" would be the name of the table that holds this data. Also, if you are needing a hyphen between each set of numbers you can add one between the "" between each ampersand.

Also, the word "Combined" where it shows AS Combined, I am telling the query to rename the output of Field_1, Field_2, Field_3, Field_4 as Combined. Whatever you change that word to, will be what your output will be named on the query.

[jo15765]

Did this work the way you were needing it to?

[lamkee]

if using your code,the result will be like below,that is not i am looking for.
Combined
1234
1234

field1 field2 field3 field4 field5
1 2 3 4 100
1 2 3 4 50

What I expected the output is,
1 2 3 4 100,50
one record only in this case