Splitting Query results into 2 columns 1

[zevw]

I would like to get my query result records split into 2 columns

This is the Query

SELECT tblPRItemEmployeeTaxHist.ItemID, tblPRItemEmployeeTaxHist.TmpName, tblPRItemEmployeeTaxHist.TaxAmount
FROM tblPRItemEmployeeTaxHist;

I would like to have the first 5 records in the 2nd and 3rd columns and the other records in columns 4 and 5 in the first column I want to stay with item id.

Is there a way how this can be done?

 

[lespaul]

Maybe it's a terminology issue, but I don't understand what you are asking for.

A record is a set of fields. In this case your table has the fields ItemID, TmpName and TaxAmount (maybe others but those are the only ones listed in your query). Some sample records could be:
[tt]

ItemID TmpName TaxAmount (Field names)
1 Something 5.00 (record1)
2 Nothing 6.00 (record2)
3 Extras 10.00 (record3)
4 Basics 12.50 (record4)
5 SuperExtras 15.00 (record5)
6 DooDahs 17.50 (record6)
7 Widgets 2.00 (record7)
[/tt]
each "row" above is a record.

Now, using the terminology as shown above, when you say:

first 5 records in the 2nd and 3rd columns and the other records in columns 4 and 5 in the first column I want to stay with item id.

How would you want to put records 1 - 5 in the 2nd and 3rd columns?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[zevw]

Leslie!

Thanks so much for responding

Your graph is really neat there is only one difference Item Id (it is something like an employee id) is always the same number.

ItemID TmpName TaxAmount (Field names)
304 Something 5.00 (record1)
304 Nothing 6.00 (record2)
304 Extras 10.00 (record3)
304 Basics 12.50 (record4)
304 SuperExtras 15.00 (record5)
304 DooDahs 17.50 (record6)
304 Widgets 2.00 (record7)

I want record 6 and 7 to on the same row as record 1 and to in columns 5 through 8. Is there a way how to do that?

 

[lespaul]

Can you take the example data and "show" me what you want? For instance, if you said:

I want to combine all the TmpNames into a list and it totals the taxamount but only when the TaxAmount is less than 10 so that my results look like:
[tt]
ItemID TmpName TaxAmount
304 Something, Nothing, Widgets 13.00 (sum of these three items)
[/tt]

 

[zevw]

Leslie

Let me write exactly what I want.

This a payroll sheet

Each employee has taxes and deductions that are deducted from their gross pay. Sometimes a employee has 5 deductions and sometimes eight deductions. The taxes and deductions are in a seperate table. Now I want my report to be legible and have a certain format / structure. I want that my first 5 taxes/deductions should be in the first column and if there are more i.e. 8 I don't want them to be listed in the same column and now we will have 8 rows of deductions. I want to carry over the next 3 deductions to be parallel to the first 3 deductions. Therefore there is always 5 rows for each employee and the other records are carried over.

Please write back if I did not clarify myself, and once again I appreciate your time!

 

[lespaul]

have a certain format / structure.

can you "show" me the certain format/ structure like I "showed" you a result set above?

ThisIsColumn1 ThisIsColumn2
What do you want here? What do you want here?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[lespaul]

It occurred to me, that you want to do this type of formatting in the REPORT not the query.

Leslie

 

[zevw]

Sorry for asking this stupid question. How do you make the spacing between each column so exact. Tab gets me out of the message form.

 

[zevw]

I just saw your second post, its in a subreport based on a table/query.

 

[lespaul]

click the Process TGML link at the bottom of the submission form for instructions on getting the quotes, code and "column spacing" (that's the [tt] tag).

You'll probably be better off asking in the Access Reports Forum703

leslie

 

[zevw]

This is close to the way I want it

[tt]ItemID  TmpName    TaxAmount     (Field)    ItemID  TmpName    TaxAmount     (Field) 
304     Something      5.00      (record1)  304     DooDahs        17.50     (record6)
304     Nothing        6.00      (record2)  304     Widgets        2.00      (record7)
304     Extras         10.00     (record3)
304     Basics         12.50     (record4)
304     SuperExtras    15.00     (record5)[/tt]

 

[zevw]

Leslie!

You are a great man and I appreciate all your time.

While trying to figure out a solution. I thought that I will create a query with a counter for each record and base each subreport on the counter number if the counter is < 6 put it in the first column, from 6 - 10 put in second column. I just did not know how to run a query and create a new counter based on each Id. I posted thread701-1297365 and I think that is the way will go.

 

[lespaul]

again, you are not going to be able to get the "layout" you want in the query. The query is going to return:
[tt]
ItemID TmpName TaxAmount (Field)
304 Something 5.00 (record1)
304 Nothing 6.00 (record2)
304 Extras 10.00 (record3)
304 Basics 12.50 (record4)
304 SuperExtras 15.00 (record5)
304 DooDahs 17.50 (record6)
304 Widgets 2.00 (record7)
[/tt]
you need to configure the sub-report to split a single person's information into two columns on the REPORT.

(and I'm a great WOman!!! No offense taken - happens all the time!)

Leslie

 

[PHV]

And she don't play guitar, despite her handle ;-)

 

[lespaul]

PH, you spoiled the surprise!!!

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[zevw]

Basically what I am going to do is create three subreports each based on the query that creates the counter (and is inserted into a temp table) so PH gave me the code to create a counter. In the first subreport it gives me the records from 1-5 based on the counter. In the second subreport it gives me the records from 6-10 based on the counter. etc.

I works fine!

Sorry! for the "man" clause and you are a great woman for giving me so much of your time.

 

[lespaul]

glad you got it worked out!

leslie