473,320 Members | 1,817 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Combining Columns

Don
I'm thinking this is simple and I'm just not understanding how to do it but
I have to ask because I'm stumped.

I have a query (which is used to create a report) and three of the columns
in this query are Date, Charge by Hour, and Charge by Day. In my report all
three of these columns show. I'd like to have only two columns in my
report; Date, Charge (charge would consist of Charge by Hour and Charge by
Day). How can I make that happen? I've tried several things but can't seem
to get it done.
Thanks,
Don..........
Jan 14 '06 #1
6 5381
Don wrote:
I'm thinking this is simple and I'm just not understanding how to do
it but I have to ask because I'm stumped.

I have a query (which is used to create a report) and three of the
columns in this query are Date, Charge by Hour, and Charge by Day.
In my report all three of these columns show. I'd like to have only
two columns in my report; Date, Charge (charge would consist of
Charge by Hour and Charge by Day). How can I make that happen? I've
tried several things but can't seem to get it done.
Thanks,
Don..........


To combine (concatenate) two fields in a query you use...

Charge: [Charge by Hour] & [Charge by Day]

Of course that would run the two right together so I assume you want at least a
space between them. That would be...

Charge: [Charge by Hour] & " " & [Charge by Day]

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Jan 14 '06 #2
"Don" <vz******@verizon.net> wrote in
news:rq8yf.77$Iw3.0@trndny06:
I'm thinking this is simple and I'm just not understanding how
to do it but I have to ask because I'm stumped.

I have a query (which is used to create a report) and three of
the columns in this query are Date, Charge by Hour, and Charge
by Day. In my report all three of these columns show. I'd
like to have only two columns in my report; Date, Charge
(charge would consist of Charge by Hour and Charge by Day).
How can I make that happen? I've tried several things but
can't seem to get it done.
Thanks,
Don..........

Your question is unclear. Do you want charge by hour to appear
when some condition is met, and charge by day if not that
condition?

if that's the case you would use the iff() function
Make the texbox
=iif(something = true, [Charge by Hour], [Charge by Day])
--
Bob Quintal

PA is y I've altered my email address.
Jan 14 '06 #3
Don
Rick thanks that exactly what I was trying to do. Another question if you
don't mind.

I think you call this an "expression". This field, or expression, if I want
it to show as currency how does that happen. In the format box it doesn't
give me the option to set it up as currency. So what I've got now are hours
which may show as something like, 8.499996 and days which may show as 17.
I'd like them to show as $8.50 and $17.00 but I'm not sure how to make that
happen.

Thanks again for the first reply it does exactly what I've asked for.
Don...............
"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:Kt**************@newssvr29.news.prodigy.net.. .
Don wrote:
I'm thinking this is simple and I'm just not understanding how to do
it but I have to ask because I'm stumped.

I have a query (which is used to create a report) and three of the
columns in this query are Date, Charge by Hour, and Charge by Day.
In my report all three of these columns show. I'd like to have only
two columns in my report; Date, Charge (charge would consist of
Charge by Hour and Charge by Day). How can I make that happen? I've
tried several things but can't seem to get it done.
Thanks,
Don..........
To combine (concatenate) two fields in a query you use...

Charge: [Charge by Hour] & [Charge by Day]

Of course that would run the two right together so I assume you want at

least a space between them. That would be...

Charge: [Charge by Hour] & " " & [Charge by Day]

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Jan 14 '06 #4
Don wrote:
Rick thanks that exactly what I was trying to do. Another question
if you don't mind.

I think you call this an "expression". This field, or expression, if
I want it to show as currency how does that happen. In the format
box it doesn't give me the option to set it up as currency. So what
I've got now are hours which may show as something like, 8.499996 and
days which may show as 17. I'd like them to show as $8.50 and $17.00
but I'm not sure how to make that happen.


Charge: Format([Charge by Hour], "Currency") & " " & Format([Charge by Day],
"Currency")

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Jan 14 '06 #5
Don
No I don't think that's what I want. I'm sorry I haven't be clearer.
Here's the situation as best as I can explain it.

Actually in my first question I wasn't completely correct in my description
of what I have. Let me try again.

I'm setting up a database that will keep track of charges. There is a date
field (which would be Date of Service). Then there is another field which
would be used if the service was for less than a complete day (Charge by
Hour). And then the last field would be all charges that were complete days
(Charge by Day).

I have an expression that multiplies 1.888888 times the number of hours,
let's say 4 hours (this calculates to 8.49996).
I have another expression that multiplies 17 times the number of days (in
every case this would be 1, so it always calculates to 17).

So when my report opens up it shows the number of "Hours" in once column and
the number of "Days" in a second column.

I was mistaken by saying I wanted Charge by Hour and Charge by Day in the
same column, what I really wanted was the expression "Hours" and the
expression "Days" in the same column.

I took Bill's example and just substituted Charge by Hour for Hours and
Charge by Day for Days. It works exactly as I asked. The only problem is
the "Hours, 8.4996" and the "Days 17" don't show as currency with two
decimals.

That's about as detailed as I can get. I've probably confused the issue
even more, huh? I thank you for taking the time to try and understand my
problem/question. I wish I understood all of this much better so I could do
it myself or at least explain it better. Unfortunately I'm, as well as
everyone else, is stuck with what I am.
Thanks again,
Don..............

"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
"Don" <vz******@verizon.net> wrote in
news:rq8yf.77$Iw3.0@trndny06:
I'm thinking this is simple and I'm just not understanding how
to do it but I have to ask because I'm stumped.

I have a query (which is used to create a report) and three of
the columns in this query are Date, Charge by Hour, and Charge
by Day. In my report all three of these columns show. I'd
like to have only two columns in my report; Date, Charge
(charge would consist of Charge by Hour and Charge by Day).
How can I make that happen? I've tried several things but
can't seem to get it done.
Thanks,
Don..........

Your question is unclear. Do you want charge by hour to appear
when some condition is met, and charge by day if not that
condition?

if that's the case you would use the iff() function
Make the texbox
=iif(something = true, [Charge by Hour], [Charge by Day])
--
Bob Quintal

PA is y I've altered my email address.

Jan 14 '06 #6
Don wrote:
No I don't think that's what I want. I'm sorry I haven't be clearer.
Here's the situation as best as I can explain it.


You can still use the Format() function within a larger expression to apply
formatting to that portion of the output.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Jan 14 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Jagdip Singh Ajimal | last post by:
I have a datagrid that I create from a query on a database. In this datagrid are two columns, appTimeHour and appTimeMin for the hour and minute of the appointments. I would like to create a new...
1
by: Mark Carter | last post by:
I would like a wxListCtrl with 3 columns in it. The number of rows in it will vary during run-time. In the first column of each row should be a wxCheckBox, which the user can check or uncheck. ...
5
by: JackT | last post by:
Hi, I have the following SQL SELECT Table1.Col1, Table3.Col1 AS Expr1, COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc, FROM Table3 INNER JOIN Table2 ON...
8
by: Ilan | last post by:
Hi all I need to add data from two Excel sheets (both on the same workbook) to an existing table in my SQL DB. The problem is that each sheet holds different fields for the same record, though...
5
by: M.Stanley | last post by:
Hi, I'm attempting to create a query that will combine 2 columns of numbers into one. The followng comes from 1 table with 4 fields (A,B,C,D) A B RESULT 700 000 700000 700 001 ...
11
by: Bonzol | last post by:
Hey there, just starting out using data tables, can anyone tell me how to combine 2 tables? Ive tried just coyping the info from 1 table to another, which both have exactly the same table layout...
0
by: Hexman | last post by:
Hello All, I'm stumped with this one. I have a Database table ("PROD") and I have set up 2 different opens for it. As you can see I alias two of the columns in the first open. I run...
2
by: J055 | last post by:
Hi I need to search a number of DataTables within a DataSet (with some relationships) and then display the filtered results in a GridView. The Columns that need to be displayed come from 2 of...
3
by: Ken Fine | last post by:
This is a question that someone familiar with ASP.NET and ADO.NET DataSets and DataTables should be able to answer fairly easily. The basic question is how I can efficiently match data from one...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.