Query set-up not using cross-tab

Hi,

I keep running into this same problem when I’m building my query and I’ve 
tried several different approaches.  It could be that it is beyond my 
knowledge but I don’t know what direction to take to find the solution.  What 
is the "cleanest" way to build the following query.

I have a table (I simplified the example):
Empl	Type	THours
Joe	Billable	60	
Joe	Indirect	100
Mary	Billable	160

And I want my query to look like this:
Empl	Billable	Indirect	THours	Billable%	Indirect%	T%
Joe	62	100	162	39%	63%	101%
Mary	160	0	160	100%	0%	100%

I tried a cross-tab query but was unable to perform the calculations because 
my Type wasn’t a field and then I read about inserting IIF statements as 
columns and tried this as well but then I couldn’t group the data by employee 
(and other problems).  It’s very possible that I’m writing my IFF statement 
wrong but I’m not even sure if I should be taking this approach.

Note:  I want my %Columns to be Billable hours / total possible hours in the 
month…in this case 160.  When I do my IIF statement for this calculation, it 
thinks it is a parameter. 

How should I handle building this query?  You’re help will be greatly 
appreciated.

Thanks,
Kay

0
Utf
3/8/2010 3:53:01 PM
access.queries 6343 articles. 1 followers. Follow

9 Replies
738 Views

Similar Articles

[PageSpeed] 50

I think your data is inconsistent. Also why would you want to calculate the 
T% since isn't this always 100%?

Try SQL like the following which hard-codes the two Type values:
SELECT tblKay.Empl, 
 Sum(Abs([Type]="Billable")*[THours]) AS Billable, 
 Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, 
 Sum(tblKay.THours) AS THrs, 
 Sum(Abs([Type]="Billable")*[THours])/Sum([THours]) AS BillablePct, 
 Sum(Abs([Type]="Indirect")*[THours])/Sum([THours]) AS IndirectPct
FROM tblKay
GROUP BY tblKay.Empl;

-- 
Duane Hookom
Microsoft Access MVP


"AccessKay" wrote:

> Hi,
> 
> I keep running into this same problem when I’m building my query and I’ve 
> tried several different approaches.  It could be that it is beyond my 
> knowledge but I don’t know what direction to take to find the solution.  What 
> is the "cleanest" way to build the following query.
> 
> I have a table (I simplified the example):
> Empl	Type	THours
> Joe	Billable	60	
> Joe	Indirect	100
> Mary	Billable	160
> 
> And I want my query to look like this:
> Empl	Billable	Indirect	THours	Billable%	Indirect%	T%
> Joe	62	100	162	39%	63%	101%
> Mary	160	0	160	100%	0%	100%
> 
> I tried a cross-tab query but was unable to perform the calculations because 
> my Type wasn’t a field and then I read about inserting IIF statements as 
> columns and tried this as well but then I couldn’t group the data by employee 
> (and other problems).  It’s very possible that I’m writing my IFF statement 
> wrong but I’m not even sure if I should be taking this approach.
> 
> Note:  I want my %Columns to be Billable hours / total possible hours in the 
> month…in this case 160.  When I do my IIF statement for this calculation, it 
> thinks it is a parameter. 
> 
> How should I handle building this query?  You’re help will be greatly 
> appreciated.
> 
> Thanks,
> Kay
> 
0
Utf
3/8/2010 4:17:01 PM
Duane,

I put in this SQL and it worked perfectly…and yes, it’s beyond my knowledge 
but thank you…I will use this as a template for similar situations.  But I am 
trying to learn and you mentioned that my data might be inconsistent.   I’m 
pulling this data from Excel and I need to do so monthly.  I was hoping not 
to have to transpose the data and divide it up into several tables.  I gave 
two Types in the example, but I actually have six.   In your opinion, should 
I make separate tables for Type or will I be safe to use the Excel file and 
the code you provide me with?  Any suggestions?  

And to answer your question about it being 100%...no, it used to be that way 
but now we are including OT and the percent can exceed 100%.

Thanks,
Kay


"Duane Hookom" wrote:

> I think your data is inconsistent. Also why would you want to calculate the 
> T% since isn't this always 100%?
> 
> Try SQL like the following which hard-codes the two Type values:
> SELECT tblKay.Empl, 
>  Sum(Abs([Type]="Billable")*[THours]) AS Billable, 
>  Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, 
>  Sum(tblKay.THours) AS THrs, 
>  Sum(Abs([Type]="Billable")*[THours])/Sum([THours]) AS BillablePct, 
>  Sum(Abs([Type]="Indirect")*[THours])/Sum([THours]) AS IndirectPct
> FROM tblKay
> GROUP BY tblKay.Empl;
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "AccessKay" wrote:
> 
> > Hi,
> > 
> > I keep running into this same problem when I’m building my query and I’ve 
> > tried several different approaches.  It could be that it is beyond my 
> > knowledge but I don’t know what direction to take to find the solution.  What 
> > is the "cleanest" way to build the following query.
> > 
> > I have a table (I simplified the example):
> > Empl	Type	THours
> > Joe	Billable	60	
> > Joe	Indirect	100
> > Mary	Billable	160
> > 
> > And I want my query to look like this:
> > Empl	Billable	Indirect	THours	Billable%	Indirect%	T%
> > Joe	62	100	162	39%	63%	101%
> > Mary	160	0	160	100%	0%	100%
> > 
> > I tried a cross-tab query but was unable to perform the calculations because 
> > my Type wasn’t a field and then I read about inserting IIF statements as 
> > columns and tried this as well but then I couldn’t group the data by employee 
> > (and other problems).  It’s very possible that I’m writing my IFF statement 
> > wrong but I’m not even sure if I should be taking this approach.
> > 
> > Note:  I want my %Columns to be Billable hours / total possible hours in the 
> > month…in this case 160.  When I do my IIF statement for this calculation, it 
> > thinks it is a parameter. 
> > 
> > How should I handle building this query?  You’re help will be greatly 
> > appreciated.
> > 
> > Thanks,
> > Kay
> > 
0
Utf
3/8/2010 5:19:01 PM
I mentioned inconsistent because you had Joe's Billable of both 60 and 62. 
That isn't consistent. I don't know why you need to do all the calculations 
of percent in the query. Typically this would be performed in a report or 
form. 

I would use a crosstab with the type as the Column Heading. You may need to 
enter all types into the Column Headings property.

I would never create separate tables to store similar data.
-- 
Duane Hookom
Microsoft Access MVP


"AccessKay" wrote:

> Duane,
> 
> I put in this SQL and it worked perfectly…and yes, it’s beyond my knowledge 
> but thank you…I will use this as a template for similar situations.  But I am 
> trying to learn and you mentioned that my data might be inconsistent.   I’m 
> pulling this data from Excel and I need to do so monthly.  I was hoping not 
> to have to transpose the data and divide it up into several tables.  I gave 
> two Types in the example, but I actually have six.   In your opinion, should 
> I make separate tables for Type or will I be safe to use the Excel file and 
> the code you provide me with?  Any suggestions?  
> 
> And to answer your question about it being 100%...no, it used to be that way 
> but now we are including OT and the percent can exceed 100%.
> 
> Thanks,
> Kay
> 
> 
> "Duane Hookom" wrote:
> 
> > I think your data is inconsistent. Also why would you want to calculate the 
> > T% since isn't this always 100%?
> > 
> > Try SQL like the following which hard-codes the two Type values:
> > SELECT tblKay.Empl, 
> >  Sum(Abs([Type]="Billable")*[THours]) AS Billable, 
> >  Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, 
> >  Sum(tblKay.THours) AS THrs, 
> >  Sum(Abs([Type]="Billable")*[THours])/Sum([THours]) AS BillablePct, 
> >  Sum(Abs([Type]="Indirect")*[THours])/Sum([THours]) AS IndirectPct
> > FROM tblKay
> > GROUP BY tblKay.Empl;
> > 
> > -- 
> > Duane Hookom
> > Microsoft Access MVP
> > 
> > 
> > "AccessKay" wrote:
> > 
> > > Hi,
> > > 
> > > I keep running into this same problem when I’m building my query and I’ve 
> > > tried several different approaches.  It could be that it is beyond my 
> > > knowledge but I don’t know what direction to take to find the solution.  What 
> > > is the "cleanest" way to build the following query.
> > > 
> > > I have a table (I simplified the example):
> > > Empl	Type	THours
> > > Joe	Billable	60	
> > > Joe	Indirect	100
> > > Mary	Billable	160
> > > 
> > > And I want my query to look like this:
> > > Empl	Billable	Indirect	THours	Billable%	Indirect%	T%
> > > Joe	62	100	162	39%	63%	101%
> > > Mary	160	0	160	100%	0%	100%
> > > 
> > > I tried a cross-tab query but was unable to perform the calculations because 
> > > my Type wasn’t a field and then I read about inserting IIF statements as 
> > > columns and tried this as well but then I couldn’t group the data by employee 
> > > (and other problems).  It’s very possible that I’m writing my IFF statement 
> > > wrong but I’m not even sure if I should be taking this approach.
> > > 
> > > Note:  I want my %Columns to be Billable hours / total possible hours in the 
> > > month…in this case 160.  When I do my IIF statement for this calculation, it 
> > > thinks it is a parameter. 
> > > 
> > > How should I handle building this query?  You’re help will be greatly 
> > > appreciated.
> > > 
> > > Thanks,
> > > Kay
> > > 
0
Utf
3/8/2010 6:06:01 PM
Duane,

I should have put Joe’s Billable as 60 and not 62 (sorry about that).  
Thanks for the input about not creating separate tables.  I sure didn’t want 
to do that.  I’m wanting to put all of this in a query because I’m going to 
later transfer it to Excel to populate a graph.  I haven’t got this far yet 
but I’m assuming I can just drag and drop it in there…hopefully.

I like the crosstab query results but as I mentioned, I’m unable to do any 
calculations for the type to get my percentage.  Can you use SQL to read the 
type column and know that I’m looking for Billable and to then divide that by 
THour to get my percentage?  In other words, can I create a calculated field 
without that field in my table?  If so, how would I do this?

A million thanks again,
Kay


"Duane Hookom" wrote:

> I mentioned inconsistent because you had Joe's Billable of both 60 and 62. 
> That isn't consistent. I don't know why you need to do all the calculations 
> of percent in the query. Typically this would be performed in a report or 
> form. 
> 
> I would use a crosstab with the type as the Column Heading. You may need to 
> enter all types into the Column Headings property.
> 
> I would never create separate tables to store similar data.
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "AccessKay" wrote:
> 
> > Duane,
> > 
> > I put in this SQL and it worked perfectly…and yes, it’s beyond my knowledge 
> > but thank you…I will use this as a template for similar situations.  But I am 
> > trying to learn and you mentioned that my data might be inconsistent.   I’m 
> > pulling this data from Excel and I need to do so monthly.  I was hoping not 
> > to have to transpose the data and divide it up into several tables.  I gave 
> > two Types in the example, but I actually have six.   In your opinion, should 
> > I make separate tables for Type or will I be safe to use the Excel file and 
> > the code you provide me with?  Any suggestions?  
> > 
> > And to answer your question about it being 100%...no, it used to be that way 
> > but now we are including OT and the percent can exceed 100%.
> > 
> > Thanks,
> > Kay
> > 
> > 
> > "Duane Hookom" wrote:
> > 
> > > I think your data is inconsistent. Also why would you want to calculate the 
> > > T% since isn't this always 100%?
> > > 
> > > Try SQL like the following which hard-codes the two Type values:
> > > SELECT tblKay.Empl, 
> > >  Sum(Abs([Type]="Billable")*[THours]) AS Billable, 
> > >  Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, 
> > >  Sum(tblKay.THours) AS THrs, 
> > >  Sum(Abs([Type]="Billable")*[THours])/Sum([THours]) AS BillablePct, 
> > >  Sum(Abs([Type]="Indirect")*[THours])/Sum([THours]) AS IndirectPct
> > > FROM tblKay
> > > GROUP BY tblKay.Empl;
> > > 
> > > -- 
> > > Duane Hookom
> > > Microsoft Access MVP
> > > 
> > > 
> > > "AccessKay" wrote:
> > > 
> > > > Hi,
> > > > 
> > > > I keep running into this same problem when I’m building my query and I’ve 
> > > > tried several different approaches.  It could be that it is beyond my 
> > > > knowledge but I don’t know what direction to take to find the solution.  What 
> > > > is the "cleanest" way to build the following query.
> > > > 
> > > > I have a table (I simplified the example):
> > > > Empl	Type	THours
> > > > Joe	Billable	60	
> > > > Joe	Indirect	100
> > > > Mary	Billable	160
> > > > 
> > > > And I want my query to look like this:
> > > > Empl	Billable	Indirect	THours	Billable%	Indirect%	T%
> > > > Joe	62	100	162	39%	63%	101%
> > > > Mary	160	0	160	100%	0%	100%
> > > > 
> > > > I tried a cross-tab query but was unable to perform the calculations because 
> > > > my Type wasn’t a field and then I read about inserting IIF statements as 
> > > > columns and tried this as well but then I couldn’t group the data by employee 
> > > > (and other problems).  It’s very possible that I’m writing my IFF statement 
> > > > wrong but I’m not even sure if I should be taking this approach.
> > > > 
> > > > Note:  I want my %Columns to be Billable hours / total possible hours in the 
> > > > month…in this case 160.  When I do my IIF statement for this calculation, it 
> > > > thinks it is a parameter. 
> > > > 
> > > > How should I handle building this query?  You’re help will be greatly 
> > > > appreciated.
> > > > 
> > > > Thanks,
> > > > Kay
> > > > 
0
Utf
3/8/2010 8:20:01 PM
I gave you the SQL with hard-coded types earlier. You could add more types 
if necessary. Their expressions would be similar to the ones I already 
provided. Otherwise you could create a query based on your crosstab query.

-- 
Duane Hookom
MS Access MVP


"AccessKay" <AccessKay@discussions.microsoft.com> wrote in message 
news:090A0A46-83FC-40B6-BA2D-C97F06E47EF6@microsoft.com...
> Duane,
>
> I should have put Joe’s Billable as 60 and not 62 (sorry about that).
> Thanks for the input about not creating separate tables.  I sure didn’t 
> want
> to do that.  I’m wanting to put all of this in a query because I’m going 
> to
> later transfer it to Excel to populate a graph.  I haven’t got this far 
> yet
> but I’m assuming I can just drag and drop it in there…hopefully.
>
> I like the crosstab query results but as I mentioned, I’m unable to do any
> calculations for the type to get my percentage.  Can you use SQL to read 
> the
> type column and know that I’m looking for Billable and to then divide that 
> by
> THour to get my percentage?  In other words, can I create a calculated 
> field
> without that field in my table?  If so, how would I do this?
>
> A million thanks again,
> Kay
>
>
> "Duane Hookom" wrote:
>
>> I mentioned inconsistent because you had Joe's Billable of both 60 and 
>> 62.
>> That isn't consistent. I don't know why you need to do all the 
>> calculations
>> of percent in the query. Typically this would be performed in a report or
>> form.
>>
>> I would use a crosstab with the type as the Column Heading. You may need 
>> to
>> enter all types into the Column Headings property.
>>
>> I would never create separate tables to store similar data.
>> -- 
>> Duane Hookom
>> Microsoft Access MVP
>>
>>
>> "AccessKay" wrote:
>>
>> > Duane,
>> >
>> > I put in this SQL and it worked perfectly…and yes, it’s beyond my 
>> > knowledge
>> > but thank you…I will use this as a template for similar situations. 
>> > But I am
>> > trying to learn and you mentioned that my data might be inconsistent. 
>> > I’m
>> > pulling this data from Excel and I need to do so monthly.  I was hoping 
>> > not
>> > to have to transpose the data and divide it up into several tables.  I 
>> > gave
>> > two Types in the example, but I actually have six.   In your opinion, 
>> > should
>> > I make separate tables for Type or will I be safe to use the Excel file 
>> > and
>> > the code you provide me with?  Any suggestions?
>> >
>> > And to answer your question about it being 100%...no, it used to be 
>> > that way
>> > but now we are including OT and the percent can exceed 100%.
>> >
>> > Thanks,
>> > Kay
>> >
>> >
>> > "Duane Hookom" wrote:
>> >
>> > > I think your data is inconsistent. Also why would you want to 
>> > > calculate the
>> > > T% since isn't this always 100%?
>> > >
>> > > Try SQL like the following which hard-codes the two Type values:
>> > > SELECT tblKay.Empl,
>> > >  Sum(Abs([Type]="Billable")*[THours]) AS Billable,
>> > >  Sum(Abs([Type]="Indirect")*[THours]) AS Indirect,
>> > >  Sum(tblKay.THours) AS THrs,
>> > >  Sum(Abs([Type]="Billable")*[THours])/Sum([THours]) AS BillablePct,
>> > >  Sum(Abs([Type]="Indirect")*[THours])/Sum([THours]) AS IndirectPct
>> > > FROM tblKay
>> > > GROUP BY tblKay.Empl;
>> > >
>> > > -- 
>> > > Duane Hookom
>> > > Microsoft Access MVP
>> > >
>> > >
>> > > "AccessKay" wrote:
>> > >
>> > > > Hi,
>> > > >
>> > > > I keep running into this same problem when I’m building my query 
>> > > > and I’ve
>> > > > tried several different approaches.  It could be that it is beyond 
>> > > > my
>> > > > knowledge but I don’t know what direction to take to find the 
>> > > > solution.  What
>> > > > is the "cleanest" way to build the following query.
>> > > >
>> > > > I have a table (I simplified the example):
>> > > > Empl Type THours
>> > > > Joe Billable 60
>> > > > Joe Indirect 100
>> > > > Mary Billable 160
>> > > >
>> > > > And I want my query to look like this:
>> > > > Empl Billable Indirect THours Billable% Indirect% T%
>> > > > Joe 62 100 162 39% 63% 101%
>> > > > Mary 160 0 160 100% 0% 100%
>> > > >
>> > > > I tried a cross-tab query but was unable to perform the 
>> > > > calculations because
>> > > > my Type wasn’t a field and then I read about inserting IIF 
>> > > > statements as
>> > > > columns and tried this as well but then I couldn’t group the data 
>> > > > by employee
>> > > > (and other problems).  It’s very possible that I’m writing my IFF 
>> > > > statement
>> > > > wrong but I’m not even sure if I should be taking this approach.
>> > > >
>> > > > Note:  I want my %Columns to be Billable hours / total possible 
>> > > > hours in the
>> > > > month…in this case 160.  When I do my IIF statement for this 
>> > > > calculation, it
>> > > > thinks it is a parameter.
>> > > >
>> > > > How should I handle building this query?  You’re help will be 
>> > > > greatly
>> > > > appreciated.
>> > > >
>> > > > Thanks,
>> > > > Kay
>> > > > 
0
Duane
3/9/2010 5:15:09 AM
Duane,

Thank you, thank you, thank you!!!  You are very kind to answer all of my 
questions.  This was my first post to this forum and it was definitely a 
positive experience.  I’ve been learning Access on my own and I’m so thankful 
that there are people out there like you that are willing to share their 
knowledge for no charge…it’s a beautiful thing.

Kay

"Duane Hookom" wrote:

> I gave you the SQL with hard-coded types earlier. You could add more types 
> if necessary. Their expressions would be similar to the ones I already 
> provided. Otherwise you could create a query based on your crosstab query.
> 
> -- 
> Duane Hookom
> MS Access MVP
> 
> 
> "AccessKay" <AccessKay@discussions.microsoft.com> wrote in message 
> news:090A0A46-83FC-40B6-BA2D-C97F06E47EF6@microsoft.com...
> > Duane,
> >
> > I should have put Joe’s Billable as 60 and not 62 (sorry about that).
> > Thanks for the input about not creating separate tables.  I sure didn’t 
> > want
> > to do that.  I’m wanting to put all of this in a query because I’m going 
> > to
> > later transfer it to Excel to populate a graph.  I haven’t got this far 
> > yet
> > but I’m assuming I can just drag and drop it in there…hopefully.
> >
> > I like the crosstab query results but as I mentioned, I’m unable to do any
> > calculations for the type to get my percentage.  Can you use SQL to read 
> > the
> > type column and know that I’m looking for Billable and to then divide that 
> > by
> > THour to get my percentage?  In other words, can I create a calculated 
> > field
> > without that field in my table?  If so, how would I do this?
> >
> > A million thanks again,
> > Kay
> >
> >
> > "Duane Hookom" wrote:
> >
> >> I mentioned inconsistent because you had Joe's Billable of both 60 and 
> >> 62.
> >> That isn't consistent. I don't know why you need to do all the 
> >> calculations
> >> of percent in the query. Typically this would be performed in a report or
> >> form.
> >>
> >> I would use a crosstab with the type as the Column Heading. You may need 
> >> to
> >> enter all types into the Column Headings property.
> >>
> >> I would never create separate tables to store similar data.
> >> -- 
> >> Duane Hookom
> >> Microsoft Access MVP
> >>
> >>
> >> "AccessKay" wrote:
> >>
> >> > Duane,
> >> >
> >> > I put in this SQL and it worked perfectly…and yes, it’s beyond my 
> >> > knowledge
> >> > but thank you…I will use this as a template for similar situations. 
> >> > But I am
> >> > trying to learn and you mentioned that my data might be inconsistent. 
> >> > I’m
> >> > pulling this data from Excel and I need to do so monthly.  I was hoping 
> >> > not
> >> > to have to transpose the data and divide it up into several tables.  I 
> >> > gave
> >> > two Types in the example, but I actually have six.   In your opinion, 
> >> > should
> >> > I make separate tables for Type or will I be safe to use the Excel file 
> >> > and
> >> > the code you provide me with?  Any suggestions?
> >> >
> >> > And to answer your question about it being 100%...no, it used to be 
> >> > that way
> >> > but now we are including OT and the percent can exceed 100%.
> >> >
> >> > Thanks,
> >> > Kay
> >> >
> >> >
> >> > "Duane Hookom" wrote:
> >> >
> >> > > I think your data is inconsistent. Also why would you want to 
> >> > > calculate the
> >> > > T% since isn't this always 100%?
> >> > >
> >> > > Try SQL like the following which hard-codes the two Type values:
> >> > > SELECT tblKay.Empl,
> >> > >  Sum(Abs([Type]="Billable")*[THours]) AS Billable,
> >> > >  Sum(Abs([Type]="Indirect")*[THours]) AS Indirect,
> >> > >  Sum(tblKay.THours) AS THrs,
> >> > >  Sum(Abs([Type]="Billable")*[THours])/Sum([THours]) AS BillablePct,
> >> > >  Sum(Abs([Type]="Indirect")*[THours])/Sum([THours]) AS IndirectPct
> >> > > FROM tblKay
> >> > > GROUP BY tblKay.Empl;
> >> > >
> >> > > -- 
> >> > > Duane Hookom
> >> > > Microsoft Access MVP
> >> > >
> >> > >
> >> > > "AccessKay" wrote:
> >> > >
> >> > > > Hi,
> >> > > >
> >> > > > I keep running into this same problem when I’m building my query 
> >> > > > and I’ve
> >> > > > tried several different approaches.  It could be that it is beyond 
> >> > > > my
> >> > > > knowledge but I don’t know what direction to take to find the 
> >> > > > solution.  What
> >> > > > is the "cleanest" way to build the following query.
> >> > > >
> >> > > > I have a table (I simplified the example):
> >> > > > Empl Type THours
> >> > > > Joe Billable 60
> >> > > > Joe Indirect 100
> >> > > > Mary Billable 160
> >> > > >
> >> > > > And I want my query to look like this:
> >> > > > Empl Billable Indirect THours Billable% Indirect% T%
> >> > > > Joe 62 100 162 39% 63% 101%
> >> > > > Mary 160 0 160 100% 0% 100%
> >> > > >
> >> > > > I tried a cross-tab query but was unable to perform the 
> >> > > > calculations because
> >> > > > my Type wasn’t a field and then I read about inserting IIF 
> >> > > > statements as
> >> > > > columns and tried this as well but then I couldn’t group the data 
> >> > > > by employee
> >> > > > (and other problems).  It’s very possible that I’m writing my IFF 
> >> > > > statement
> >> > > > wrong but I’m not even sure if I should be taking this approach.
> >> > > >
> >> > > > Note:  I want my %Columns to be Billable hours / total possible 
> >> > > > hours in the
> >> > > > month…in this case 160.  When I do my IIF statement for this 
> >> > > > calculation, it
> >> > > > thinks it is a parameter.
> >> > > >
> >> > > > How should I handle building this query?  You’re help will be 
> >> > > > greatly
> >> > > > appreciated.
> >> > > >
> >> > > > Thanks,
> >> > > > Kay
> >> > > > 
0
Utf
3/9/2010 2:24:02 PM
AccessKay,
We are glad to provide the free assistance. 

Actually, "free" is optional. I would like you to "pay it forward" if you 
can offer some assistance to anyone anywhere anytime any way ;-)

-- 
Duane Hookom
Microsoft Access MVP


"AccessKay" wrote:

> Duane,
> 
> Thank you, thank you, thank you!!!  You are very kind to answer all of my 
> questions.  This was my first post to this forum and it was definitely a 
> positive experience.  I’ve been learning Access on my own and I’m so thankful 
> that there are people out there like you that are willing to share their 
> knowledge for no charge…it’s a beautiful thing.
> 
> Kay
> 
> "Duane Hookom" wrote:
> 
> > I gave you the SQL with hard-coded types earlier. You could add more types 
> > if necessary. Their expressions would be similar to the ones I already 
> > provided. Otherwise you could create a query based on your crosstab query.
> > 
> > -- 
> > Duane Hookom
> > MS Access MVP
> > 
> > 
> > "AccessKay" <AccessKay@discussions.microsoft.com> wrote in message 
> > news:090A0A46-83FC-40B6-BA2D-C97F06E47EF6@microsoft.com...
> > > Duane,
> > >
> > > I should have put Joe’s Billable as 60 and not 62 (sorry about that).
> > > Thanks for the input about not creating separate tables.  I sure didn’t 
> > > want
> > > to do that.  I’m wanting to put all of this in a query because I’m going 
> > > to
> > > later transfer it to Excel to populate a graph.  I haven’t got this far 
> > > yet
> > > but I’m assuming I can just drag and drop it in there…hopefully.
> > >
> > > I like the crosstab query results but as I mentioned, I’m unable to do any
> > > calculations for the type to get my percentage.  Can you use SQL to read 
> > > the
> > > type column and know that I’m looking for Billable and to then divide that 
> > > by
> > > THour to get my percentage?  In other words, can I create a calculated 
> > > field
> > > without that field in my table?  If so, how would I do this?
> > >
> > > A million thanks again,
> > > Kay
> > >
> > >
> > > "Duane Hookom" wrote:
> > >
> > >> I mentioned inconsistent because you had Joe's Billable of both 60 and 
> > >> 62.
> > >> That isn't consistent. I don't know why you need to do all the 
> > >> calculations
> > >> of percent in the query. Typically this would be performed in a report or
> > >> form.
> > >>
> > >> I would use a crosstab with the type as the Column Heading. You may need 
> > >> to
> > >> enter all types into the Column Headings property.
> > >>
> > >> I would never create separate tables to store similar data.
> > >> -- 
> > >> Duane Hookom
> > >> Microsoft Access MVP
> > >>
> > >>
> > >> "AccessKay" wrote:
> > >>
> > >> > Duane,
> > >> >
> > >> > I put in this SQL and it worked perfectly…and yes, it’s beyond my 
> > >> > knowledge
> > >> > but thank you…I will use this as a template for similar situations. 
> > >> > But I am
> > >> > trying to learn and you mentioned that my data might be inconsistent. 
> > >> > I’m
> > >> > pulling this data from Excel and I need to do so monthly.  I was hoping 
> > >> > not
> > >> > to have to transpose the data and divide it up into several tables.  I 
> > >> > gave
> > >> > two Types in the example, but I actually have six.   In your opinion, 
> > >> > should
> > >> > I make separate tables for Type or will I be safe to use the Excel file 
> > >> > and
> > >> > the code you provide me with?  Any suggestions?
> > >> >
> > >> > And to answer your question about it being 100%...no, it used to be 
> > >> > that way
> > >> > but now we are including OT and the percent can exceed 100%.
> > >> >
> > >> > Thanks,
> > >> > Kay
> > >> >
> > >> >
> > >> > "Duane Hookom" wrote:
> > >> >
> > >> > > I think your data is inconsistent. Also why would you want to 
> > >> > > calculate the
> > >> > > T% since isn't this always 100%?
> > >> > >
> > >> > > Try SQL like the following which hard-codes the two Type values:
> > >> > > SELECT tblKay.Empl,
> > >> > >  Sum(Abs([Type]="Billable")*[THours]) AS Billable,
> > >> > >  Sum(Abs([Type]="Indirect")*[THours]) AS Indirect,
> > >> > >  Sum(tblKay.THours) AS THrs,
> > >> > >  Sum(Abs([Type]="Billable")*[THours])/Sum([THours]) AS BillablePct,
> > >> > >  Sum(Abs([Type]="Indirect")*[THours])/Sum([THours]) AS IndirectPct
> > >> > > FROM tblKay
> > >> > > GROUP BY tblKay.Empl;
> > >> > >
> > >> > > -- 
> > >> > > Duane Hookom
> > >> > > Microsoft Access MVP
> > >> > >
> > >> > >
> > >> > > "AccessKay" wrote:
> > >> > >
> > >> > > > Hi,
> > >> > > >
> > >> > > > I keep running into this same problem when I’m building my query 
> > >> > > > and I’ve
> > >> > > > tried several different approaches.  It could be that it is beyond 
> > >> > > > my
> > >> > > > knowledge but I don’t know what direction to take to find the 
> > >> > > > solution.  What
> > >> > > > is the "cleanest" way to build the following query.
> > >> > > >
> > >> > > > I have a table (I simplified the example):
> > >> > > > Empl Type THours
> > >> > > > Joe Billable 60
> > >> > > > Joe Indirect 100
> > >> > > > Mary Billable 160
> > >> > > >
> > >> > > > And I want my query to look like this:
> > >> > > > Empl Billable Indirect THours Billable% Indirect% T%
> > >> > > > Joe 62 100 162 39% 63% 101%
> > >> > > > Mary 160 0 160 100% 0% 100%
> > >> > > >
> > >> > > > I tried a cross-tab query but was unable to perform the 
> > >> > > > calculations because
> > >> > > > my Type wasn’t a field and then I read about inserting IIF 
> > >> > > > statements as
> > >> > > > columns and tried this as well but then I couldn’t group the data 
> > >> > > > by employee
> > >> > > > (and other problems).  It’s very possible that I’m writing my IFF 
> > >> > > > statement
> > >> > > > wrong but I’m not even sure if I should be taking this approach.
> > >> > > >
> > >> > > > Note:  I want my %Columns to be Billable hours / total possible 
> > >> > > > hours in the
> > >> > > > month…in this case 160.  When I do my IIF statement for this 
> > >> > > > calculation, it
> > >> > > > thinks it is a parameter.
> > >> > > >
> > >> > > > How should I handle building this query?  You’re help will be 
> > >> > > > greatly
> > >> > > > appreciated.
> > >> > > >
> > >> > > > Thanks,
> > >> > > > Kay
> > >> > > > 
0
Utf
3/9/2010 3:47:02 PM
Hi Duane,

I thought I was set with this part of my database but I don’t want to use 
THours for my calculation to get my Billable Pct.  I want to use the total 
possible hours in a month which will either be 160 or 200 depending if the 
month has five Fridays in it or four Fridays.  I’d like to avoid hard coding 
and changing this value each month but that might be a separate issue to deal 
with down the road. 

I inserted a field into my query called TPossHrs and made it equal 160.  
Then I tried to use the SQL you gave me to input TPossHrs instead of THours.  
It gave me a different percentage.  I’m thinking that possibly the field I 
inserted is not being recognized as a value???  Maybe I need to build another 
query???  My SQL is as follows:

TRANSFORM Sum(tblKay.THours) AS SumOfTHours
SELECT tblKay.Empl, Sum(tblKay.THours) AS [Total Of THours], 
Sum(Abs([Type]="Billable")*[THours])/Sum([THours]) AS BillablePct, 160 AS 
TPossHrs, Sum(Abs([Type]="Billable")*[TPossHrs])/Sum([TPossHrs]) AS 
BillablePct2
FROM tblKay
GROUP BY tblKay.Empl, 160
PIVOT tblKay.Type;

How can I get this SQL to divide by 160 or do I need to take another 
approach to this?

Again…thank you for your help.

Kay


"Duane Hookom" wrote:

> AccessKay,
> We are glad to provide the free assistance. 
> 
> Actually, "free" is optional. I would like you to "pay it forward" if you 
> can offer some assistance to anyone anywhere anytime any way ;-)
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "AccessKay" wrote:
> 
> > Duane,
> > 
> > Thank you, thank you, thank you!!!  You are very kind to answer all of my 
> > questions.  This was my first post to this forum and it was definitely a 
> > positive experience.  I’ve been learning Access on my own and I’m so thankful 
> > that there are people out there like you that are willing to share their 
> > knowledge for no charge…it’s a beautiful thing.
> > 
> > Kay
> > 
> > "Duane Hookom" wrote:
> > 
> > > I gave you the SQL with hard-coded types earlier. You could add more types 
> > > if necessary. Their expressions would be similar to the ones I already 
> > > provided. Otherwise you could create a query based on your crosstab query.
> > > 
> > > -- 
> > > Duane Hookom
> > > MS Access MVP
> > > 
> > > 
> > > "AccessKay" <AccessKay@discussions.microsoft.com> wrote in message 
> > > news:090A0A46-83FC-40B6-BA2D-C97F06E47EF6@microsoft.com...
> > > > Duane,
> > > >
> > > > I should have put Joe’s Billable as 60 and not 62 (sorry about that).
> > > > Thanks for the input about not creating separate tables.  I sure didn’t 
> > > > want
> > > > to do that.  I’m wanting to put all of this in a query because I’m going 
> > > > to
> > > > later transfer it to Excel to populate a graph.  I haven’t got this far 
> > > > yet
> > > > but I’m assuming I can just drag and drop it in there…hopefully.
> > > >
> > > > I like the crosstab query results but as I mentioned, I’m unable to do any
> > > > calculations for the type to get my percentage.  Can you use SQL to read 
> > > > the
> > > > type column and know that I’m looking for Billable and to then divide that 
> > > > by
> > > > THour to get my percentage?  In other words, can I create a calculated 
> > > > field
> > > > without that field in my table?  If so, how would I do this?
> > > >
> > > > A million thanks again,
> > > > Kay
> > > >
> > > >
> > > > "Duane Hookom" wrote:
> > > >
> > > >> I mentioned inconsistent because you had Joe's Billable of both 60 and 
> > > >> 62.
> > > >> That isn't consistent. I don't know why you need to do all the 
> > > >> calculations
> > > >> of percent in the query. Typically this would be performed in a report or
> > > >> form.
> > > >>
> > > >> I would use a crosstab with the type as the Column Heading. You may need 
> > > >> to
> > > >> enter all types into the Column Headings property.
> > > >>
> > > >> I would never create separate tables to store similar data.
> > > >> -- 
> > > >> Duane Hookom
> > > >> Microsoft Access MVP
> > > >>
> > > >>
> > > >> "AccessKay" wrote:
> > > >>
> > > >> > Duane,
> > > >> >
> > > >> > I put in this SQL and it worked perfectly…and yes, it’s beyond my 
> > > >> > knowledge
> > > >> > but thank you…I will use this as a template for similar situations. 
> > > >> > But I am
> > > >> > trying to learn and you mentioned that my data might be inconsistent. 
> > > >> > I’m
> > > >> > pulling this data from Excel and I need to do so monthly.  I was hoping 
> > > >> > not
> > > >> > to have to transpose the data and divide it up into several tables.  I 
> > > >> > gave
> > > >> > two Types in the example, but I actually have six.   In your opinion, 
> > > >> > should
> > > >> > I make separate tables for Type or will I be safe to use the Excel file 
> > > >> > and
> > > >> > the code you provide me with?  Any suggestions?
> > > >> >
> > > >> > And to answer your question about it being 100%...no, it used to be 
> > > >> > that way
> > > >> > but now we are including OT and the percent can exceed 100%.
> > > >> >
> > > >> > Thanks,
> > > >> > Kay
> > > >> >
> > > >> >
> > > >> > "Duane Hookom" wrote:
> > > >> >
> > > >> > > I think your data is inconsistent. Also why would you want to 
> > > >> > > calculate the
> > > >> > > T% since isn't this always 100%?
> > > >> > >
> > > >> > > Try SQL like the following which hard-codes the two Type values:
> > > >> > > SELECT tblKay.Empl,
> > > >> > >  Sum(Abs([Type]="Billable")*[THours]) AS Billable,
> > > >> > >  Sum(Abs([Type]="Indirect")*[THours]) AS Indirect,
> > > >> > >  Sum(tblKay.THours) AS THrs,
> > > >> > >  Sum(Abs([Type]="Billable")*[THours])/Sum([THours]) AS BillablePct,
> > > >> > >  Sum(Abs([Type]="Indirect")*[THours])/Sum([THours]) AS IndirectPct
> > > >> > > FROM tblKay
> > > >> > > GROUP BY tblKay.Empl;
> > > >> > >
> > > >> > > -- 
> > > >> > > Duane Hookom
> > > >> > > Microsoft Access MVP
> > > >> > >
> > > >> > >
> > > >> > > "AccessKay" wrote:
> > > >> > >
> > > >> > > > Hi,
> > > >> > > >
> > > >> > > > I keep running into this same problem when I’m building my query 
> > > >> > > > and I’ve
> > > >> > > > tried several different approaches.  It could be that it is beyond 
> > > >> > > > my
> > > >> > > > knowledge but I don’t know what direction to take to find the 
> > > >> > > > solution.  What
> > > >> > > > is the "cleanest" way to build the following query.
> > > >> > > >
> > > >> > > > I have a table (I simplified the example):
> > > >> > > > Empl Type THours
> > > >> > > > Joe Billable 60
> > > >> > > > Joe Indirect 100
> > > >> > > > Mary Billable 160
> > > >> > > >
> > > >> > > > And I want my query to look like this:
> > > >> > > > Empl Billable Indirect THours Billable% Indirect% T%
> > > >> > > > Joe 62 100 162 39% 63% 101%
> > > >> > > > Mary 160 0 160 100% 0% 100%
> > > >> > > >
> > > >> > > > I tried a cross-tab query but was unable to perform the 
> > > >> > > > calculations because
> > > >> > > > my Type wasn’t a field and then I read about inserting IIF 
> > > >> > > > statements as
> > > >> > > > columns and tried this as well but then I couldn’t group the data 
> > > >> > > > by employee
> > > >> > > > (and other problems).  It’s very possible that I’m writing my IFF 
> > > >> > > > statement
> > > >> > > > wrong but I’m not even sure if I should be taking this approach.
> > > >> > > >
> > > >> > > > Note:  I want my %Columns to be Billable hours / total possible 
> > > >> > > > hours in the
> > > >> > > > month…in this case 160.  When I do my IIF statement for this 
> > > >> > > > calculation, it
> > > >> > > > thinks it is a parameter.
> > > >> > > >
> > > >> > > > How should I handle building this query?  You’re help will be 
> > > >> > > > greatly
> > > >> > > > appreciated.
> > > >> > > >
> > > >> > > > Thanks,
> > > >> > > > Kay
> > > >> > > > 
0
Utf
3/10/2010 2:43:02 PM
BTW...I posted a seperate thread for the hours issue

"AccessKay" wrote:

> Hi Duane,
> 
> I thought I was set with this part of my database but I don’t want to use 
> THours for my calculation to get my Billable Pct.  I want to use the total 
> possible hours in a month which will either be 160 or 200 depending if the 
> month has five Fridays in it or four Fridays.  I’d like to avoid hard coding 
> and changing this value each month but that might be a separate issue to deal 
> with down the road. 
> 
> I inserted a field into my query called TPossHrs and made it equal 160.  
> Then I tried to use the SQL you gave me to input TPossHrs instead of THours.  
> It gave me a different percentage.  I’m thinking that possibly the field I 
> inserted is not being recognized as a value???  Maybe I need to build another 
> query???  My SQL is as follows:
> 
> TRANSFORM Sum(tblKay.THours) AS SumOfTHours
> SELECT tblKay.Empl, Sum(tblKay.THours) AS [Total Of THours], 
> Sum(Abs([Type]="Billable")*[THours])/Sum([THours]) AS BillablePct, 160 AS 
> TPossHrs, Sum(Abs([Type]="Billable")*[TPossHrs])/Sum([TPossHrs]) AS 
> BillablePct2
> FROM tblKay
> GROUP BY tblKay.Empl, 160
> PIVOT tblKay.Type;
> 
> How can I get this SQL to divide by 160 or do I need to take another 
> approach to this?
> 
> Again…thank you for your help.
> 
> Kay
> 
> 
> "Duane Hookom" wrote:
> 
> > AccessKay,
> > We are glad to provide the free assistance. 
> > 
> > Actually, "free" is optional. I would like you to "pay it forward" if you 
> > can offer some assistance to anyone anywhere anytime any way ;-)
> > 
> > -- 
> > Duane Hookom
> > Microsoft Access MVP
> > 
> > 
> > "AccessKay" wrote:
> > 
> > > Duane,
> > > 
> > > Thank you, thank you, thank you!!!  You are very kind to answer all of my 
> > > questions.  This was my first post to this forum and it was definitely a 
> > > positive experience.  I’ve been learning Access on my own and I’m so thankful 
> > > that there are people out there like you that are willing to share their 
> > > knowledge for no charge…it’s a beautiful thing.
> > > 
> > > Kay
> > > 
> > > "Duane Hookom" wrote:
> > > 
> > > > I gave you the SQL with hard-coded types earlier. You could add more types 
> > > > if necessary. Their expressions would be similar to the ones I already 
> > > > provided. Otherwise you could create a query based on your crosstab query.
> > > > 
> > > > -- 
> > > > Duane Hookom
> > > > MS Access MVP
> > > > 
> > > > 
> > > > "AccessKay" <AccessKay@discussions.microsoft.com> wrote in message 
> > > > news:090A0A46-83FC-40B6-BA2D-C97F06E47EF6@microsoft.com...
> > > > > Duane,
> > > > >
> > > > > I should have put Joe’s Billable as 60 and not 62 (sorry about that).
> > > > > Thanks for the input about not creating separate tables.  I sure didn’t 
> > > > > want
> > > > > to do that.  I’m wanting to put all of this in a query because I’m going 
> > > > > to
> > > > > later transfer it to Excel to populate a graph.  I haven’t got this far 
> > > > > yet
> > > > > but I’m assuming I can just drag and drop it in there…hopefully.
> > > > >
> > > > > I like the crosstab query results but as I mentioned, I’m unable to do any
> > > > > calculations for the type to get my percentage.  Can you use SQL to read 
> > > > > the
> > > > > type column and know that I’m looking for Billable and to then divide that 
> > > > > by
> > > > > THour to get my percentage?  In other words, can I create a calculated 
> > > > > field
> > > > > without that field in my table?  If so, how would I do this?
> > > > >
> > > > > A million thanks again,
> > > > > Kay
> > > > >
> > > > >
> > > > > "Duane Hookom" wrote:
> > > > >
> > > > >> I mentioned inconsistent because you had Joe's Billable of both 60 and 
> > > > >> 62.
> > > > >> That isn't consistent. I don't know why you need to do all the 
> > > > >> calculations
> > > > >> of percent in the query. Typically this would be performed in a report or
> > > > >> form.
> > > > >>
> > > > >> I would use a crosstab with the type as the Column Heading. You may need 
> > > > >> to
> > > > >> enter all types into the Column Headings property.
> > > > >>
> > > > >> I would never create separate tables to store similar data.
> > > > >> -- 
> > > > >> Duane Hookom
> > > > >> Microsoft Access MVP
> > > > >>
> > > > >>
> > > > >> "AccessKay" wrote:
> > > > >>
> > > > >> > Duane,
> > > > >> >
> > > > >> > I put in this SQL and it worked perfectly…and yes, it’s beyond my 
> > > > >> > knowledge
> > > > >> > but thank you…I will use this as a template for similar situations. 
> > > > >> > But I am
> > > > >> > trying to learn and you mentioned that my data might be inconsistent. 
> > > > >> > I’m
> > > > >> > pulling this data from Excel and I need to do so monthly.  I was hoping 
> > > > >> > not
> > > > >> > to have to transpose the data and divide it up into several tables.  I 
> > > > >> > gave
> > > > >> > two Types in the example, but I actually have six.   In your opinion, 
> > > > >> > should
> > > > >> > I make separate tables for Type or will I be safe to use the Excel file 
> > > > >> > and
> > > > >> > the code you provide me with?  Any suggestions?
> > > > >> >
> > > > >> > And to answer your question about it being 100%...no, it used to be 
> > > > >> > that way
> > > > >> > but now we are including OT and the percent can exceed 100%.
> > > > >> >
> > > > >> > Thanks,
> > > > >> > Kay
> > > > >> >
> > > > >> >
> > > > >> > "Duane Hookom" wrote:
> > > > >> >
> > > > >> > > I think your data is inconsistent. Also why would you want to 
> > > > >> > > calculate the
> > > > >> > > T% since isn't this always 100%?
> > > > >> > >
> > > > >> > > Try SQL like the following which hard-codes the two Type values:
> > > > >> > > SELECT tblKay.Empl,
> > > > >> > >  Sum(Abs([Type]="Billable")*[THours]) AS Billable,
> > > > >> > >  Sum(Abs([Type]="Indirect")*[THours]) AS Indirect,
> > > > >> > >  Sum(tblKay.THours) AS THrs,
> > > > >> > >  Sum(Abs([Type]="Billable")*[THours])/Sum([THours]) AS BillablePct,
> > > > >> > >  Sum(Abs([Type]="Indirect")*[THours])/Sum([THours]) AS IndirectPct
> > > > >> > > FROM tblKay
> > > > >> > > GROUP BY tblKay.Empl;
> > > > >> > >
> > > > >> > > -- 
> > > > >> > > Duane Hookom
> > > > >> > > Microsoft Access MVP
> > > > >> > >
> > > > >> > >
> > > > >> > > "AccessKay" wrote:
> > > > >> > >
> > > > >> > > > Hi,
> > > > >> > > >
> > > > >> > > > I keep running into this same problem when I’m building my query 
> > > > >> > > > and I’ve
> > > > >> > > > tried several different approaches.  It could be that it is beyond 
> > > > >> > > > my
> > > > >> > > > knowledge but I don’t know what direction to take to find the 
> > > > >> > > > solution.  What
> > > > >> > > > is the "cleanest" way to build the following query.
> > > > >> > > >
> > > > >> > > > I have a table (I simplified the example):
> > > > >> > > > Empl Type THours
> > > > >> > > > Joe Billable 60
> > > > >> > > > Joe Indirect 100
> > > > >> > > > Mary Billable 160
> > > > >> > > >
> > > > >> > > > And I want my query to look like this:
> > > > >> > > > Empl Billable Indirect THours Billable% Indirect% T%
> > > > >> > > > Joe 62 100 162 39% 63% 101%
> > > > >> > > > Mary 160 0 160 100% 0% 100%
> > > > >> > > >
> > > > >> > > > I tried a cross-tab query but was unable to perform the 
> > > > >> > > > calculations because
> > > > >> > > > my Type wasn’t a field and then I read about inserting IIF 
> > > > >> > > > statements as
> > > > >> > > > columns and tried this as well but then I couldn’t group the data 
> > > > >> > > > by employee
> > > > >> > > > (and other problems).  It’s very possible that I’m writing my IFF 
> > > > >> > > > statement
> > > > >> > > > wrong but I’m not even sure if I should be taking this approach.
> > > > >> > > >
> > > > >> > > > Note:  I want my %Columns to be Billable hours / total possible 
> > > > >> > > > hours in the
> > > > >> > > > month…in this case 160.  When I do my IIF statement for this 
> > > > >> > > > calculation, it
> > > > >> > > > thinks it is a parameter.
> > > > >> > > >
> > > > >> > > > How should I handle building this query?  You’re help will be 
> > > > >> > > > greatly
> > > > >> > > > appreciated.
> > > > >> > > >
> > > > >> > > > Thanks,
> > > > >> > > > Kay
> > > > >> > > > 
0
Utf
3/10/2010 4:30:12 PM
Reply:

Similar Artilces:

BOM changes using out date
Is it possible to change the quantity on a compnent item in a BOM and have that change be date sensitive? We have a situation where the current BOM calls for 3 of an item. Begining on a certain date that quantit will change to 2 and a new item will be added to the BOM. Haven't found a way to handle this yet, other than remembering to change the particular BOM on the specific date. Any ideas? -- Jim@TurboChef Jim, Have you tried using the Effective and Obsolete Dates? Make the item with quantity of 3 obsolete on a specific date. Add the item to the BOM a second time with a q...

How can I Change recurrence settings with OUT updating details????
Hello, I have a recurring appointment that I use to keep daily notes. I set it up for 4:30 and would like to change it to 3:30 -- however, when I actually make the change it clears ALL of my previous entires notes. So I lose about 2 years worth of notes when I make this change. Anyone know how I can make the change without this loss of information? Thanks Shawn Create a new recurring appointment for 3:30 and end the recurrence of the old one. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name ...

Using Outlook 2007 and Gmail Apps Premier
My wife and I are Realtors and occasionally when one of us is working from home and the other one is in the office, we email attachments to eachother. They show has being sent by "me" in our gmail apps premier accounts. These messages go to the All Mail folder and not the Inbox. We are now using apps sync and Outlook 2007 with a single domain name. When we send these emails they show up in the sent mail folders of Outlook but not in the Inbox. They are in the All mail folder in gmail. This is a bit confusing. Is there away to send them to the Inbox instead of All...

Cannot use RPC over HTTPS with Cox cable
I have two users who were on DSL for over a year with zero problems. They switched to Cox cable and now cannot connect to Exchange (RPC over HTTP). No settings have changed. They can switch the connection back to DSL and connect in seconds. Switch to cable and never connect. All the tests I can think of pass. They can connect to the /rpc share on the Exchange server using IE. It appears that Cox is blocking them, but I don't see how. Isn't the rpc over https encrypted? Could Cox even tell they were connecting to Exchange? All other users using various other ISP's work perfe...

Using money on two computers ?
I wish to organise my personal accounts from both home and work. Can work done on money be saved and brought home to be continued on ? Are there any synchronisation features that money has ?? TIA Simon In microsoft.public.money, Simon77 wrote: >I wish to organise my personal accounts from both home and work. Can work >done on money be saved and brought home to be continued on ? > >Are there any synchronisation features that money has ?? > Do Help->MicrosoftMoneyHelp and enter "synchronize" into the Ask Money box. To the extent that this does not answer your...

Query on two tables with matching null fields
Suppose I have two tables: Table 1: ID a b c 1 1 2 2 3 4 3 5 6 4 7 8 9 Table2 ID a b c 1 1 2 2 3 4 3 5 6 4 7 8 9 The following SQL: SELECT Table1.ID, Table2.ID FROM Table1 INNER JOIN Table2 ON (Table1.c = Table2.c) AND (Table1.b = Table2.b) AND (Table1.a = Table2.a); Returns: Table1.ID Table2.ID 4 4 I do want these tables connected by inner joins not left or right (in other words I want only those records where all t...

Financial planning using Money?
My wife and I are new to online banking and are considering getting Money 2005 when it comes out in a couple weeks. I have 2 questions: 1) Does the standard version of Money have long-term (i.e., lifetime) financial planning capabilities, or do you have to upgrade to Deluxe or even Premium for that? 2) Can anyone who has used Money's financial planning functionalities comment on how useful/efficient they found them to be? Thanks a lot! Tim With respect to your second question concerning the usefulness of the Lifetime Planner in Money 2004, my own assessment is that the tool is gene...

some problem using schema
Hello there I change some of my procedures as schema It works fine. but when i saw the definition of these procedures in sys.sql_modules the create statement still goes to DBO. why? Roy Goldhammer (royg@yahoo.com) writes: > I change some of my procedures as schema With ALTER SCHEMA? > but when i saw the definition of these procedures in sys.sql_modules the > create statement still goes to DBO. You would need to use ALTER PROCEDURE to fix that part, if it is an issue for you. The same thing if you rename a procedure - the stored text is left unc...

Booklet used to print correctly
My company just updated to MS Office 2007. I have a small booklet in Publisher that used to print perfectly, now pages are upside down. I’ve read previous posts, even tried smaller paper. Ahhh, please help… ...

Saving changes to the default tab in a document
Hi all, I seem unable to save a change to the default tab setting in an individual document. I make the change (ie. from 0.5" to 0.25") and save the document. Upon restarting Publisher and reopening the document the default tab may or may not be 0.25"! This is extremely irritating since when the default tab changes back to 0.5" it does so in all text boxes in the document thus badly messing up the document layout. Is it me or is it Microsoft??? TNX, Chris Did you compose this publication in 2000? Custom tabs that you previously set in Publisher 2000 are not saved ...

How do I get set up to send email from outlook?
I am just setting up outlook and was wondering how I can send emails..not posts. I have used outlook before but that was for school. Can someone please help??? -- Tina Miller This is the error message that I'm getting.... Unable to logon to the server using Secure Password Authentication. Account: 'ComcastOnline', Server: 'mail.comcast.net', Protocol: POP3, Server Response: '-ERR authorization not enabled', Port: 110, Secure(SSL): No, Server Error: 0x800CCC90, Error Number: 0x800CCC18 Can someone please help???? "Tina Miller" <nursetiny27@co...

how to backup mail account settings
I have lots of email accounts and would like to backup my mail account settings. I know that the passwords will not be saved but I would just like to save the settings. I am happy to also save all my mail with the mail account settings if necessary. Can you please tell me if this is possible. I have tried using outlook backup but this only saves mail and not settings and I think that it used to be possible using File and Settings Transfer but cannot seem to do this with Office 2003. I am using Microsoft Outlook 2003. Many thanks Outlook 2003 does not have the ability to export your account...

Sum using probably a VLOOKUP
Hi, I'm looking for a way to make a sum of values in a column depending on the respective value of another column. Example: A1 = 1 A2 = 2 A3 = 1 B1 = 100 B2 = 300 B3 = 125 I'd like to be able to make a sum of B values when A values = 1. In that case, my sum would be 225. Thanks, Alex One way: =SUMIF(A:A,1,B:B) In article <OQ#LYB6hEHA.356@tk2msftngp13.phx.gbl>, "Alex Langlois" <alex.nospam@nospam.net> wrote: > Hi, > I'm looking for a way to make a sum of values in a column depending on > the respective value of another column. ...

Send All emails in Outbox and Quit using VBA
Hi, I have MS Access creating emails objects in Outlook using a macro. I want to send them all and close outlook after. 1-Open Outlook 2-Send all emails in the outbox folder 3-Close Outlook How can I achieve it using VBA? Using command buttons does not seem to work since my Outlook is in French, I'm using Outlook 2003 SP3. Thank You Stephane You're not trying to use command button captions, are you? Use the IDs instead, with the FindControl method. They're language-neutral. The ID for Send All should be 5577. -- Sue Mosher, Outlook MVP Author o...

Using Form view in sharepoint
Hi, I am attempting to use a form view in SharePoint designer and connect this to an Access Database, which works fine. However, when I view this in Sharepoint I am able to add a record but I am not able to edit or delete records as I get an error message (saying "An unexpected error has occurred) and the record does not change in the database. Does anyone know why this might be happening and more importantly how to resolve it? Many thanks in advance, Emma ...

Set Userform label text if form is not loaded???
My userform contains various text label controls which display the contents of a row of cells. I achieve this with the following code: Private Sub Worksheet_Change (ByVal Target As Range) UserForm1.Label1.Caption = Sheet1.Range("A1").Value UserForm1.Label2.Caption = Sheet1.Range("B1").Value UserForm1.Label3.Caption = Sheet1.Range("C1").Value ' .... ' etc, etc... End Sub I was wondering, is it wise to run the above code even when my userform is not loaded??? The code above seems to work fine even when my form is NOT loaded, b...

Hiding Personal.xls using Novell Groupwise integration
Hi All, I use Excel in Novell Groupwise integration. Trying to hide and save Personal.xls the GW Save macro stops with runtime error 91 at ActiveWorkbook.Save line, the GW Close macro at If Application.ActiveWindow.WindowNumber = 1 Then line. Obviously, the integration macros are not prepared for saving a hidden workbook. Has anybody heard that this bug has been fixed? Stefi I'm not sure how much else will break, but maybe you could code around it: If Application.ActiveWindow Is Nothing Then 'do nothing Else If Application.ActiveWindow.WindowNumber = 1 T...

Button Parameter Query
I am a COMPLETE NOVICE struggling with designing a database... Have Mercy! I need my user to be able to select from a drop down list of queries from which the user can then further choose a a single or combination of 7 regions as the final criteria. I believe I would need a drop down list of existing queries, for example, "All Active By Region#", and then allow the user to choose which region(s) they need to generate the report from a set of buttons on a parameter query form? The reports will provide Region#, Name, Id#, address, phone numbers, e-mail, and teachable qualif...

cross hairs when I print
How do I get rid of the cross hairs when I print I assume you are talking about the crop marks in the corners. I just got a new computer on Friday and don't have Pub 2003 on this one yet so I can't give you the steps. Use the Help file and look up crop marks. The answer is in there. -- JoAnn Paules Microsoft MVP - Publisher "sciacqua1" <sciacqua1@discussions.microsoft.com> wrote in message news:ACDEA501-6343-417B-9772-E1062AC34904@microsoft.com... > How do I get rid of the cross hairs when I print JoAnn Paules wrote: > I assume you are talking about ...

tab sequence?
Is there a way to set the order in which cells are highlighted when pressing the tab key? i.e. I have a cell in which data is entered, and then a button which fires a macro next to it. After entering the data I would like to be able to tab out of the cell and have the button highlighted such that pressing enter on the keyboard would trigger the button - much like a web page. Any suggestions? --- Message posted from http://www.ExcelForum.com/ ...

Setting Axis scale
Greetings ! When I address a different data file, I have code to generate a chart to display the new data. For some s t r a n g e reason, the code SOMETIMES comes to a grinding halt at this line - .SeriesCollection(4).XValues = _ "=Ali2!R" & VTPStartRow & _ "C2:R" & VTPEndRow & "C2" VTPStartRow has a value of say 80, and VTPEndRow about 100, and it works just FINE if I enter them "manually". It never stops anywhere else, always at this line ! But sometimes it works OK.... Robin - Before you get to ...

Date Calculations, Corss Tab?
Hello! First of all, thank you in advance for taking the time to help me! I am trying to create queries that will show future revenues, for invoices being amortized over defined periods, to be grouped by months for the first twelve months (from a date selected), and the remainder in a “+12” column. This report would be similar to an AR Aging that puts an invoice in to 30, 60, 90, +120 buckets. The amounts are stored in my table “tblRA – Amortization”, the Report date (“the date selected”) will be user defined and entered on a form. As if this is not complicated enough, depending on...

alphabet tabs
I am trying to create a draft a dictionary-type document where I would have a tmplate with a letter for each page (i.e. A,B,C....X,Y,Z). Then I could readily select a page where I could add an item or word for a specific letter (i.e. quickly go to M or P or Q) On Tue, 9 Feb 2010 15:31:02 -0800, David Glover <David Glover@discussions.microsoft.com> wrote: >I am trying to create a draft a dictionary-type document where I would have a >tmplate with a letter for each page (i.e. A,B,C....X,Y,Z). Then I could >readily select a page where I could add an item or word for...

If Yes checked, display set of questions
I saw an access database that had a very cool form. There were 5 Yes/No options on the form. These were questions that applied to Quality Control. When a box was checked to indicate yes, additional questions with Yes/No options displayed below. Those questions that displayed asked questions about that particular Quality Control Topic question. I'm stuggling to figure out how that form was so interactive. Did each of the 5 questions have it's own subform, or was it as basic as "if this box is checked then display these questions". Any insight is appreciated. Tha...

Using Windows Backup?
Has anyone successfully used Windows built-in backup to backup and restore Exchange? Will this backup get all the correct information without stopping the services? The box in question is running Win2K Server and Exchange 2000. Thanks, Mike On Thu, 5 May 2005 20:59:17 -0600, "Mike G." <wet@dog.com> wrote: >Has anyone successfully used Windows built-in backup to backup and restore >Exchange? Will this backup get all the correct information without stopping >the services? The box in question is running Win2K Server and Exchange >2000. > >Thanks,...