Trouble with sendkeys - Alternative?

[Jennifer]

I import a file monthly into Access and through a series of queries I output
one table that lists customers and the counts of items and how much it costs
summed up to the current date.

IE:
Upload:
May 2008
Customer Item 1 Cost 1 Item 2 Cost 2
C1 25 $25 4 $100
C2 10 $10 0 $0

Output:
Customer Item 1 Cost 1 Item 2 Cost 2
C1 254 $254 64 $256
C2 213 $213 0 $0

The Output represents the sum of all of the current months.

In order to get the sum, I run a macro that sends a lot of keys in order to
add the new month to the query and then to add it to the sum.

Is there a way to use VBA code to do this? I'm having trouble with the
sending keys, sometimes it works and most of the time it breaks.

 

[Graham Mandeno]

Hi Jennifer

There is almost always an alternative to SendKeys.

Tell us more about what you are trying to do and how you are currently
achieving it (or not!) with SendKeys.

 

[Jennifer]

My query looks like this:
Sort Customer Description 01/2008 02/2008 03/2008
SUM: [01/2008] +[02/2008]+[03/2008]

When I send keys I basically use arrows to get to the SUM column, insert a
column, use arrows to get to the most recent month, enter, save. Then I tab
over to get back to SUM, build, add +, add most recent month, enter, save,
close.

I have to do this for 2 queries and its the exact same process. (one for
cost and the other for count)

Do you need the exact sendkeys?

Hi Jennifer

There is almost always an alternative to SendKeys.

Tell us more about what you are trying to do and how you are currently
achieving it (or not!) with SendKeys.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I import a file monthly into Access and through a series of queries I
output
one table that lists customers and the counts of items and how much it
costs
summed up to the current date.

IE:
Upload:
May 2008
Customer Item 1 Cost 1 Item 2 Cost 2
C1 25 $25 4 $100
C2 10 $10 0 $0

Output:
Customer Item 1 Cost 1 Item 2 Cost 2
C1 254 $254 64 $256
C2 213 $213 0 $0

The Output represents the sum of all of the current months.

In order to get the sum, I run a macro that sends a lot of keys in order
to
add the new month to the query and then to add it to the sum.

Is there a way to use VBA code to do this? I'm having trouble with the
sending keys, sometimes it works and most of the time it breaks.

 

[Graham Mandeno]

Hi Jennifer

Do you need the exact sendkeys?

No - that would be far too scary!

Actually, what is most scary here is that it looks like you have a separate
column (field) in your table for each month. Is that the case? Or is this a
crosstab query?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

My query looks like this:
Sort Customer Description 01/2008 02/2008 03/2008
SUM: [01/2008] +[02/2008]+[03/2008]

When I send keys I basically use arrows to get to the SUM column, insert a
column, use arrows to get to the most recent month, enter, save. Then I
tab
over to get back to SUM, build, add +, add most recent month, enter, save,
close.

I have to do this for 2 queries and its the exact same process. (one for
cost and the other for count)

Do you need the exact sendkeys?

Hi Jennifer

There is almost always an alternative to SendKeys.

Tell us more about what you are trying to do and how you are currently
achieving it (or not!) with SendKeys.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I import a file monthly into Access and through a series of queries I
output
one table that lists customers and the counts of items and how much it
costs
summed up to the current date.

IE:
Upload:
May 2008
Customer Item 1 Cost 1 Item 2 Cost 2
C1 25 $25 4 $100
C2 10 $10 0 $0

Output:
Customer Item 1 Cost 1 Item 2 Cost 2
C1 254 $254 64 $256
C2 213 $213 0 $0

The Output represents the sum of all of the current months.

In order to get the sum, I run a macro that sends a lot of keys in
order
to
add the new month to the query and then to add it to the sum.

Is there a way to use VBA code to do this? I'm having trouble with the
sending keys, sometimes it works and most of the time it breaks.

 

[Jennifer]

Okay let me start by saying I am pretty new to Access. And yes the months
are the columns. I didn't know how else to get a sum.

I do have master tables and queries that list out each record...if that helps.

Hi Jennifer

Do you need the exact sendkeys?

No - that would be far too scary!

Actually, what is most scary here is that it looks like you have a separate
column (field) in your table for each month. Is that the case? Or is this a
crosstab query?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

My query looks like this:
Sort Customer Description 01/2008 02/2008 03/2008
SUM: [01/2008] +[02/2008]+[03/2008]

When I send keys I basically use arrows to get to the SUM column, insert a
column, use arrows to get to the most recent month, enter, save. Then I
tab
over to get back to SUM, build, add +, add most recent month, enter, save,
close.

I have to do this for 2 queries and its the exact same process. (one for
cost and the other for count)

Do you need the exact sendkeys?

Hi Jennifer

There is almost always an alternative to SendKeys.

Tell us more about what you are trying to do and how you are currently
achieving it (or not!) with SendKeys.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I import a file monthly into Access and through a series of queries I
output
one table that lists customers and the counts of items and how much it
costs
summed up to the current date.

IE:
Upload:
May 2008
Customer Item 1 Cost 1 Item 2 Cost 2
C1 25 $25 4 $100
C2 10 $10 0 $0

Output:
Customer Item 1 Cost 1 Item 2 Cost 2
C1 254 $254 64 $256
C2 213 $213 0 $0

The Output represents the sum of all of the current months.

In order to get the sum, I run a macro that sends a lot of keys in
order
to
add the new month to the query and then to add it to the sum.

Is there a way to use VBA code to do this? I'm having trouble with the
sending keys, sometimes it works and most of the time it breaks.

 

[Graham Mandeno]

Hi Jennifer

I do have master tables and queries that list out each record...if that
helps.

That is GREAT news! it means we don't have to go back and redesign your
tables

Can you please post a description of the relevant tables with field names
and data types?

Also, can you please post the SQL from the query that produces the month
columns (open the query, then click View>SQL view and copy and paste from
the window).

I'm thinking you can probably generate a query on the fly to get what you
want, so please tell me as accurately as possible what you want to display
in your form/report.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Okay let me start by saying I am pretty new to Access. And yes the months
are the columns. I didn't know how else to get a sum.

I do have master tables and queries that list out each record...if that
helps.

Hi Jennifer

Do you need the exact sendkeys?

No - that would be far too scary!

Actually, what is most scary here is that it looks like you have a
separate
column (field) in your table for each month. Is that the case? Or is
this a
crosstab query?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

My query looks like this:
Sort Customer Description 01/2008 02/2008
03/2008
SUM: [01/2008] +[02/2008]+[03/2008]

When I send keys I basically use arrows to get to the SUM column,
insert a
column, use arrows to get to the most recent month, enter, save. Then
I
tab
over to get back to SUM, build, add +, add most recent month, enter,
save,
close.

I have to do this for 2 queries and its the exact same process. (one
for
cost and the other for count)

Do you need the exact sendkeys?

:

Hi Jennifer

There is almost always an alternative to SendKeys.

Tell us more about what you are trying to do and how you are currently
achieving it (or not!) with SendKeys.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I import a file monthly into Access and through a series of queries I
output
one table that lists customers and the counts of items and how much
it
costs
summed up to the current date.

IE:
Upload:
May 2008
Customer Item 1 Cost 1 Item 2 Cost 2
C1 25 $25 4 $100
C2 10 $10 0 $0

Output:
Customer Item 1 Cost 1 Item 2 Cost 2
C1 254 $254 64 $256
C2 213 $213 0 $0

The Output represents the sum of all of the current months.

In order to get the sum, I run a macro that sends a lot of keys in
order
to
add the new month to the query and then to add it to the sum.

Is there a way to use VBA code to do this? I'm having trouble with
the
sending keys, sometimes it works and most of the time it breaks.

 

[Jennifer]

I have a temp table (TEMP) that is what the data is uploaded into. (Field1 -
Text, Field 2 - Number, Field 3 - Currency, Field 4 - Number, Field 5 -
Currency ...Field 16 - Number, Field 17 - Currency)

I have a another temp table (Resource TEMP) that renames the fields of the
temp table. (Customer - Text, Count01 - Number, Cost01 - Currency, Count02 -
Number, Cost02 - Currency ...Count08 - Number, Cost08- Currency)

I have a resource table (Resources) that assigns a resource number to items
that I'm charging for. (Ie: Blackberry...etc.) (Resource - Text, Description
- Text, Active - yes/no)

I have a SortOrder table (SortOrder) that sorts the customers so that
they're in the correct order in the end. (SortOrder - Number, Customer - text)

And I have a Master table (Master Table) that lists each record (Month -
date, SortOrder - number, Customer - text, Resource - text, Count - Number,
Cost - Currency)

For costs:
TRANSFORM Sum([Query 2].Cost) AS SumOfCost
SELECT [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource, [Query
2].Description
FROM [Query 2]
GROUP BY [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource, [Query
2].Description
PIVOT [Query 2].Month;

For counts:
TRANSFORM Sum([Query 2].Count) AS SumOfCount
SELECT [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource, [Query
2].Description
FROM [Query 2]
GROUP BY [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource, [Query
2].Description
PIVOT [Query 2].Month;


The goal of this project is to have one table (that looks exactly like the
table that I imported from excel) that is a sum of all the inputted months
for each customer and item.

This is the SQL view of the resulting query that I currently have.
SELECT [Query 5_Count].Customer, [Query 5_Count].[01 Blackberry], [Query
5_Cost].[01 Blackberry], [Query 5_Count].[02 Sametime], [Query 5_Cost].[02
Sametime], [Query 5_Count].[03 Docushare], [Query 5_Cost].[03 Docushare],
[Query 5_Count].[04 eRoom], [Query 5_Cost].[04 eRoom], [Query 5_Count].[05
Secure ID], [Query 5_Cost].[05 Secure ID], [Query 5_Count].[06 Verisign ICEA
Certs], [Query 5_Cost].[06 Verisign ICEA Certs], [Query 5_Count].[07 SSL
Entrust Certs], [Query 5_Cost].[07 SSL Entrust Certs], [Query 5_Count].[08
Wildcard Certs], [Query 5_Cost].[08 Wildcard Certs]
FROM [Query 5_Cost] INNER JOIN [Query 5_Count] ON [Query
5_Cost].Customer=[Query 5_Count].Customer;

Thanks for all your help so far,
Jennifer

Hi Jennifer

I do have master tables and queries that list out each record...if that
helps.

That is GREAT news! it means we don't have to go back and redesign your
tables

Can you please post a description of the relevant tables with field names
and data types?

Also, can you please post the SQL from the query that produces the month
columns (open the query, then click View>SQL view and copy and paste from
the window).

I'm thinking you can probably generate a query on the fly to get what you
want, so please tell me as accurately as possible what you want to display
in your form/report.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Okay let me start by saying I am pretty new to Access. And yes the months
are the columns. I didn't know how else to get a sum.

I do have master tables and queries that list out each record...if that
helps.

Hi Jennifer

Do you need the exact sendkeys?

No - that would be far too scary!

Actually, what is most scary here is that it looks like you have a
separate
column (field) in your table for each month. Is that the case? Or is
this a
crosstab query?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

My query looks like this:
Sort Customer Description 01/2008 02/2008
03/2008
SUM: [01/2008] +[02/2008]+[03/2008]

When I send keys I basically use arrows to get to the SUM column,
insert a
column, use arrows to get to the most recent month, enter, save. Then
I
tab
over to get back to SUM, build, add +, add most recent month, enter,
save,
close.

I have to do this for 2 queries and its the exact same process. (one
for
cost and the other for count)

Do you need the exact sendkeys?

:

Hi Jennifer

There is almost always an alternative to SendKeys.

Tell us more about what you are trying to do and how you are currently
achieving it (or not!) with SendKeys.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I import a file monthly into Access and through a series of queries I
output
one table that lists customers and the counts of items and how much
it
costs
summed up to the current date.

IE:
Upload:
May 2008
Customer Item 1 Cost 1 Item 2 Cost 2
C1 25 $25 4 $100
C2 10 $10 0 $0

Output:
Customer Item 1 Cost 1 Item 2 Cost 2
C1 254 $254 64 $256
C2 213 $213 0 $0

The Output represents the sum of all of the current months.

In order to get the sum, I run a macro that sends a lot of keys in
order
to
add the new month to the query and then to add it to the sum.

Is there a way to use VBA code to do this? I'm having trouble with
the
sending keys, sometimes it works and most of the time it breaks.

 

[Graham Mandeno]

Hi Jennifer

So doesn't all this boil down to three tables...?

Customers
========
CustID: autonumber - primary key
CustName: text
SortOrder: number

Resources
========
ResID: autonumber - primary key
ResName: text
ResDescription: text
Active: yes/no

Sales
====
ResID: autonumber - primary key
SaleCustomer: foreign key to Customers
SaleResource: FK to Resources
SaleDate: date
SaleAmount: currency

Everything else you need can be extracted by queries based on these three
tables.

You MUST remember that Access is NOT a spreadsheet application. It is a
relational database application. An Access table should NOT look like a
spreadsheet, with repeating columns or groups of columns such as: Count01,
Cost01, Count02, Cost02, etc.

Incidentally, your crosstab query SQL statements below refer to [Query 2]
and [Query 5]. How are they defined?

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a temp table (TEMP) that is what the data is uploaded into.
(Field1 -
Text, Field 2 - Number, Field 3 - Currency, Field 4 - Number, Field 5 -
Currency ...Field 16 - Number, Field 17 - Currency)

I have a another temp table (Resource TEMP) that renames the fields of the
temp table. (Customer - Text, Count01 - Number, Cost01 - Currency,
Count02 -
Number, Cost02 - Currency ...Count08 - Number, Cost08- Currency)

I have a resource table (Resources) that assigns a resource number to
items
that I'm charging for. (Ie: Blackberry...etc.) (Resource - Text,
Description
- Text, Active - yes/no)

I have a SortOrder table (SortOrder) that sorts the customers so that
they're in the correct order in the end. (SortOrder - Number, Customer -
text)

And I have a Master table (Master Table) that lists each record (Month -
date, SortOrder - number, Customer - text, Resource - text, Count -
Number,
Cost - Currency)

For costs:
TRANSFORM Sum([Query 2].Cost) AS SumOfCost
SELECT [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource, [Query
2].Description
FROM [Query 2]
GROUP BY [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource,
[Query
2].Description
PIVOT [Query 2].Month;

For counts:
TRANSFORM Sum([Query 2].Count) AS SumOfCount
SELECT [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource, [Query
2].Description
FROM [Query 2]
GROUP BY [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource,
[Query
2].Description
PIVOT [Query 2].Month;


The goal of this project is to have one table (that looks exactly like the
table that I imported from excel) that is a sum of all the inputted months
for each customer and item.

This is the SQL view of the resulting query that I currently have.
SELECT [Query 5_Count].Customer, [Query 5_Count].[01 Blackberry], [Query
5_Cost].[01 Blackberry], [Query 5_Count].[02 Sametime], [Query 5_Cost].[02
Sametime], [Query 5_Count].[03 Docushare], [Query 5_Cost].[03 Docushare],
[Query 5_Count].[04 eRoom], [Query 5_Cost].[04 eRoom], [Query 5_Count].[05
Secure ID], [Query 5_Cost].[05 Secure ID], [Query 5_Count].[06 Verisign
ICEA
Certs], [Query 5_Cost].[06 Verisign ICEA Certs], [Query 5_Count].[07 SSL
Entrust Certs], [Query 5_Cost].[07 SSL Entrust Certs], [Query 5_Count].[08
Wildcard Certs], [Query 5_Cost].[08 Wildcard Certs]
FROM [Query 5_Cost] INNER JOIN [Query 5_Count] ON [Query
5_Cost].Customer=[Query 5_Count].Customer;

Thanks for all your help so far,
Jennifer

Hi Jennifer

I do have master tables and queries that list out each record...if that
helps.

That is GREAT news! it means we don't have to go back and redesign your
tables

Can you please post a description of the relevant tables with field names
and data types?

Also, can you please post the SQL from the query that produces the month
columns (open the query, then click View>SQL view and copy and paste from
the window).

I'm thinking you can probably generate a query on the fly to get what you
want, so please tell me as accurately as possible what you want to
display
in your form/report.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Okay let me start by saying I am pretty new to Access. And yes the
months
are the columns. I didn't know how else to get a sum.

I do have master tables and queries that list out each record...if that
helps.

:

Hi Jennifer

Do you need the exact sendkeys?

No - that would be far too scary!

Actually, what is most scary here is that it looks like you have a
separate
column (field) in your table for each month. Is that the case? Or is
this a
crosstab query?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

My query looks like this:
Sort Customer Description 01/2008 02/2008
03/2008
SUM: [01/2008] +[02/2008]+[03/2008]

When I send keys I basically use arrows to get to the SUM column,
insert a
column, use arrows to get to the most recent month, enter, save.
Then
I
tab
over to get back to SUM, build, add +, add most recent month, enter,
save,
close.

I have to do this for 2 queries and its the exact same process.
(one
for
cost and the other for count)

Do you need the exact sendkeys?

:

Hi Jennifer

There is almost always an alternative to SendKeys.

Tell us more about what you are trying to do and how you are
currently
achieving it (or not!) with SendKeys.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I import a file monthly into Access and through a series of
queries I
output
one table that lists customers and the counts of items and how
much
it
costs
summed up to the current date.

IE:
Upload:
May 2008
Customer Item 1 Cost 1 Item 2 Cost 2
C1 25 $25 4
$100
C2 10 $10 0
$0

Output:
Customer Item 1 Cost 1 Item 2 Cost 2
C1 254 $254 64
$256
C2 213 $213 0 $0

The Output represents the sum of all of the current months.

In order to get the sum, I run a macro that sends a lot of keys
in
order
to
add the new month to the query and then to add it to the sum.

Is there a way to use VBA code to do this? I'm having trouble
with
the
sending keys, sometimes it works and most of the time it breaks.

 

[Jennifer]

I'm going to sound like an idiot...(I DID say I was a novice) but how do I
make foreign keys and how do I find out how a query is defined?

Hi Jennifer

So doesn't all this boil down to three tables...?

Customers
========
CustID: autonumber - primary key
CustName: text
SortOrder: number

Resources
========
ResID: autonumber - primary key
ResName: text
ResDescription: text
Active: yes/no

Sales
====
ResID: autonumber - primary key
SaleCustomer: foreign key to Customers
SaleResource: FK to Resources
SaleDate: date
SaleAmount: currency

Everything else you need can be extracted by queries based on these three
tables.

You MUST remember that Access is NOT a spreadsheet application. It is a
relational database application. An Access table should NOT look like a
spreadsheet, with repeating columns or groups of columns such as: Count01,
Cost01, Count02, Cost02, etc.

Incidentally, your crosstab query SQL statements below refer to [Query 2]
and [Query 5]. How are they defined?

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a temp table (TEMP) that is what the data is uploaded into.
(Field1 -
Text, Field 2 - Number, Field 3 - Currency, Field 4 - Number, Field 5 -
Currency ...Field 16 - Number, Field 17 - Currency)

I have a another temp table (Resource TEMP) that renames the fields of the
temp table. (Customer - Text, Count01 - Number, Cost01 - Currency,
Count02 -
Number, Cost02 - Currency ...Count08 - Number, Cost08- Currency)

I have a resource table (Resources) that assigns a resource number to
items
that I'm charging for. (Ie: Blackberry...etc.) (Resource - Text,
Description
- Text, Active - yes/no)

I have a SortOrder table (SortOrder) that sorts the customers so that
they're in the correct order in the end. (SortOrder - Number, Customer -
text)

And I have a Master table (Master Table) that lists each record (Month -
date, SortOrder - number, Customer - text, Resource - text, Count -
Number,
Cost - Currency)

For costs:
TRANSFORM Sum([Query 2].Cost) AS SumOfCost
SELECT [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource, [Query
2].Description
FROM [Query 2]
GROUP BY [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource,
[Query
2].Description
PIVOT [Query 2].Month;

For counts:
TRANSFORM Sum([Query 2].Count) AS SumOfCount
SELECT [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource, [Query
2].Description
FROM [Query 2]
GROUP BY [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource,
[Query
2].Description
PIVOT [Query 2].Month;


The goal of this project is to have one table (that looks exactly like the
table that I imported from excel) that is a sum of all the inputted months
for each customer and item.

This is the SQL view of the resulting query that I currently have.
SELECT [Query 5_Count].Customer, [Query 5_Count].[01 Blackberry], [Query
5_Cost].[01 Blackberry], [Query 5_Count].[02 Sametime], [Query 5_Cost].[02
Sametime], [Query 5_Count].[03 Docushare], [Query 5_Cost].[03 Docushare],
[Query 5_Count].[04 eRoom], [Query 5_Cost].[04 eRoom], [Query 5_Count].[05
Secure ID], [Query 5_Cost].[05 Secure ID], [Query 5_Count].[06 Verisign
ICEA
Certs], [Query 5_Cost].[06 Verisign ICEA Certs], [Query 5_Count].[07 SSL
Entrust Certs], [Query 5_Cost].[07 SSL Entrust Certs], [Query 5_Count].[08
Wildcard Certs], [Query 5_Cost].[08 Wildcard Certs]
FROM [Query 5_Cost] INNER JOIN [Query 5_Count] ON [Query
5_Cost].Customer=[Query 5_Count].Customer;

Thanks for all your help so far,
Jennifer

Hi Jennifer

I do have master tables and queries that list out each record...if that
helps.

That is GREAT news! it means we don't have to go back and redesign your
tables

Can you please post a description of the relevant tables with field names
and data types?

Also, can you please post the SQL from the query that produces the month
columns (open the query, then click View>SQL view and copy and paste from
the window).

I'm thinking you can probably generate a query on the fly to get what you
want, so please tell me as accurately as possible what you want to
display
in your form/report.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand



Okay let me start by saying I am pretty new to Access. And yes the
months
are the columns. I didn't know how else to get a sum.

I do have master tables and queries that list out each record...if that
helps.

:

Hi Jennifer

Do you need the exact sendkeys?

No - that would be far too scary!

Actually, what is most scary here is that it looks like you have a
separate
column (field) in your table for each month. Is that the case? Or is
this a
crosstab query?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

My query looks like this:
Sort Customer Description 01/2008 02/2008
03/2008
SUM: [01/2008] +[02/2008]+[03/2008]

When I send keys I basically use arrows to get to the SUM column,
insert a
column, use arrows to get to the most recent month, enter, save.
Then
I
tab
over to get back to SUM, build, add +, add most recent month, enter,
save,
close.

I have to do this for 2 queries and its the exact same process.
(one
for
cost and the other for count)

Do you need the exact sendkeys?

:

Hi Jennifer

There is almost always an alternative to SendKeys.

Tell us more about what you are trying to do and how you are
currently
achieving it (or not!) with SendKeys.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I import a file monthly into Access and through a series of
queries I
output
one table that lists customers and the counts of items and how
much
it
costs
summed up to the current date.

IE:
Upload:
May 2008
Customer Item 1 Cost 1 Item 2 Cost 2
C1 25 $25 4
$100
C2 10 $10 0
$0

Output:
Customer Item 1 Cost 1 Item 2 Cost 2
C1 254 $254 64
$256
C2 213 $213 0 $0

The Output represents the sum of all of the current months.

In order to get the sum, I run a macro that sends a lot of keys
in
order
to
add the new month to the query and then to add it to the sum.

Is there a way to use VBA code to do this? I'm having trouble
with
the
sending keys, sometimes it works and most of the time it breaks.

 

[Jennifer]

Ok, I understand what you mean by define.

I have a series of 6 queries that basically display the same data in
different ways because I didn't know how else to get the final query the way
I wanted it to look.

query 2
======
Date
SortOrder
Customer
Resource
Count
Cost
Description
Active

Query 3 - cross tab (here I split query 2 and have 1 query 3 for cost and 1
for count)
======
SortOrder - row
Customer - row
Resource - row
Description -row
Month - column
Cost - value

Query 4 - (again split into cost and count)
=====
SortOrder
Customer
Resource
Description
01/2008
02/2008
03/2008
04/2008..etc
SUM([01/2008]+[02/2008]+[03/2008]+...)

Query 5 - Cross-tab (Still separate queries for count and cost)
=====
SortOrder - row
Customer - row
Description - Column
Cost - value


I suppose I could eliminate double queries if I had a table with the what to
multiply the counts by and do that in one of the final queries...

Again thanks for all your help.

Hi Jennifer

So doesn't all this boil down to three tables...?

Customers
========
CustID: autonumber - primary key
CustName: text
SortOrder: number

Resources
========
ResID: autonumber - primary key
ResName: text
ResDescription: text
Active: yes/no

Sales
====
ResID: autonumber - primary key
SaleCustomer: foreign key to Customers
SaleResource: FK to Resources
SaleDate: date
SaleAmount: currency

Everything else you need can be extracted by queries based on these three
tables.

You MUST remember that Access is NOT a spreadsheet application. It is a
relational database application. An Access table should NOT look like a
spreadsheet, with repeating columns or groups of columns such as: Count01,
Cost01, Count02, Cost02, etc.

Incidentally, your crosstab query SQL statements below refer to [Query 2]
and [Query 5]. How are they defined?

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a temp table (TEMP) that is what the data is uploaded into.
(Field1 -
Text, Field 2 - Number, Field 3 - Currency, Field 4 - Number, Field 5 -
Currency ...Field 16 - Number, Field 17 - Currency)

I have a another temp table (Resource TEMP) that renames the fields of the
temp table. (Customer - Text, Count01 - Number, Cost01 - Currency,
Count02 -
Number, Cost02 - Currency ...Count08 - Number, Cost08- Currency)

I have a resource table (Resources) that assigns a resource number to
items
that I'm charging for. (Ie: Blackberry...etc.) (Resource - Text,
Description
- Text, Active - yes/no)

I have a SortOrder table (SortOrder) that sorts the customers so that
they're in the correct order in the end. (SortOrder - Number, Customer -
text)

And I have a Master table (Master Table) that lists each record (Month -
date, SortOrder - number, Customer - text, Resource - text, Count -
Number,
Cost - Currency)

For costs:
TRANSFORM Sum([Query 2].Cost) AS SumOfCost
SELECT [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource, [Query
2].Description
FROM [Query 2]
GROUP BY [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource,
[Query
2].Description
PIVOT [Query 2].Month;

For counts:
TRANSFORM Sum([Query 2].Count) AS SumOfCount
SELECT [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource, [Query
2].Description
FROM [Query 2]
GROUP BY [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource,
[Query
2].Description
PIVOT [Query 2].Month;


The goal of this project is to have one table (that looks exactly like the
table that I imported from excel) that is a sum of all the inputted months
for each customer and item.

This is the SQL view of the resulting query that I currently have.
SELECT [Query 5_Count].Customer, [Query 5_Count].[01 Blackberry], [Query
5_Cost].[01 Blackberry], [Query 5_Count].[02 Sametime], [Query 5_Cost].[02
Sametime], [Query 5_Count].[03 Docushare], [Query 5_Cost].[03 Docushare],
[Query 5_Count].[04 eRoom], [Query 5_Cost].[04 eRoom], [Query 5_Count].[05
Secure ID], [Query 5_Cost].[05 Secure ID], [Query 5_Count].[06 Verisign
ICEA
Certs], [Query 5_Cost].[06 Verisign ICEA Certs], [Query 5_Count].[07 SSL
Entrust Certs], [Query 5_Cost].[07 SSL Entrust Certs], [Query 5_Count].[08
Wildcard Certs], [Query 5_Cost].[08 Wildcard Certs]
FROM [Query 5_Cost] INNER JOIN [Query 5_Count] ON [Query
5_Cost].Customer=[Query 5_Count].Customer;

Thanks for all your help so far,
Jennifer

Hi Jennifer

I do have master tables and queries that list out each record...if that
helps.

That is GREAT news! it means we don't have to go back and redesign your
tables

Can you please post a description of the relevant tables with field names
and data types?

Also, can you please post the SQL from the query that produces the month
columns (open the query, then click View>SQL view and copy and paste from
the window).

I'm thinking you can probably generate a query on the fly to get what you
want, so please tell me as accurately as possible what you want to
display
in your form/report.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand



Okay let me start by saying I am pretty new to Access. And yes the
months
are the columns. I didn't know how else to get a sum.

I do have master tables and queries that list out each record...if that
helps.

:

Hi Jennifer

Do you need the exact sendkeys?

No - that would be far too scary!

Actually, what is most scary here is that it looks like you have a
separate
column (field) in your table for each month. Is that the case? Or is
this a
crosstab query?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

My query looks like this:
Sort Customer Description 01/2008 02/2008
03/2008
SUM: [01/2008] +[02/2008]+[03/2008]

When I send keys I basically use arrows to get to the SUM column,
insert a
column, use arrows to get to the most recent month, enter, save.
Then
I
tab
over to get back to SUM, build, add +, add most recent month, enter,
save,
close.

I have to do this for 2 queries and its the exact same process.
(one
for
cost and the other for count)

Do you need the exact sendkeys?

:

Hi Jennifer

There is almost always an alternative to SendKeys.

Tell us more about what you are trying to do and how you are
currently
achieving it (or not!) with SendKeys.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I import a file monthly into Access and through a series of
queries I
output
one table that lists customers and the counts of items and how
much
it
costs
summed up to the current date.

IE:
Upload:
May 2008
Customer Item 1 Cost 1 Item 2 Cost 2
C1 25 $25 4
$100
C2 10 $10 0
$0

Output:
Customer Item 1 Cost 1 Item 2 Cost 2
C1 254 $254 64
$256
C2 213 $213 0 $0

The Output represents the sum of all of the current months.

In order to get the sum, I run a macro that sends a lot of keys
in
order
to
add the new month to the query and then to add it to the sum.

Is there a way to use VBA code to do this? I'm having trouble
with
the
sending keys, sometimes it works and most of the time it breaks.

 

[Graham Mandeno]

Hi Jennifer

Don't feel bad about being a novice! We all had to start somewhere

Two tables may be joined in a "relationship", which may be one-to-one or
(more usually) one-to-many. For example, you have a table of customers and
a table of sales. One customer can be involved in many sales (but not
vice-versa) so this is a one-to-many relationship with Customers being the
"one" side and Sales being the "many" side. Each customer record can be
uniquely identified by its primary key (PK). This means that you don't need
to store any other customer information (name, shipping address, email, etc)
in the sales table. You need only have one field to store the PK value of
the customer. This field is called the "foreign key" in the relationship.

First you have set up your three tables as below (BTW, "ResID" in the Sales
table should be "SaleID" - that was just a copy/paste blooper). Note that
the FK fields (SaleCustomer and SaleResource) should be numeric fields of
size "Long" to match the autonumber data type. You can then open the
relationships window and add the three tables to the screen. Then drag from
each PK field to its corresponding FK field. Be sure to click on "Enforce
referential integrity" and this will ensure that you can never have a sale
record for a nonexistent customer or resource.

You can then create a query based on your three tables. As you add the
tables to your query design window, Access will automatically "join" them
according to the relationships you have defined. You can then add fields
from all three tables to your query grid, giving you a view of sales with
the other detailed information (CustName, ResName, ResDescription, etc) from
the related tables.

If you have a date field (SaleDate) and you want to group results in a query
by month, then you can use a calculated field:
SaleMonth: Year(SaleDate) * 100 + Month(SaleDate)

This will give you a numeric value - 200801, 200802, etc - which sorts
nicely. If you want to display this value differently in a report, you can
do so. For example:

=Format( DateSerial( [SaleMonth] \ 100, [SaleMonth] mod 100, 1 ),
"mmm yy" )
will convert 200801 to "Jan 08", and so on.

If you want to include the sum of the "Value" columns in a crosstab query,
add a "Row Heading" field with the same Sum expression. For example:

Field: TotalSales: SaleAmount
Total: Sum
Crosstab: Row Heading

Returning multiple "value" expressions for a crosstab report can be tricky.
It can get really messy if you try to combine multiple crosstabs. My
preference is to combine the different expressions into a single delimited
string, and then extract the desired element in a calculated control on the
report. For example:

Field: MyValue: Sum([SaleAmount]) & "," & Sum([SaleQuantity])
Total: Expression
Crosstab: Value

This gives you a value like this: "$123.45,5"

In your report you can have two textboxes:
=Element( [FieldName], 0 ) - gives $123.45
and
=Element( [FieldName], 1 ) - gives 5

You must also define the Element function:

Public Function Element( _
Expression As Variant, _
Index As Integer, _
Optional Delimiter As String = "," _
) As String
Dim a() As String
If Not IsNull(Expression) And Index >= 0 Then
a = Split(Expression, Delimiter)
If Index <= UBound(a) Then Element = a(Index)
End If
End Function

Another problem with reports based on crosstab queries is predicting the
values of the column headings which are, of course, field names. This is a
particular problem with dates, as you often want to produce the same report
for different date ranges.

One way around this is to use a relative expression for your column heading.
For example, instead of using the month as your heading, use an expression
based on the number of months from the start date.

Let's say you have a query that gives you six months of data, starting on
the date selected on a form. The WHERE condition in your query could be:

WHERE ( [SaleDate] >= Forms!MyForm![StartDate] )
AND ( [SaleDate] < DateAdd( "m", 6, Forms!MyForm![StartDate] ) )

Instead of using the month as your column heading, use an expression like
this:

"M" & DateDiff( "m", Forms!MyForm![StartDate], [SaleDate] )

This will give you "M0" for the first column, "M1" for the second, and so
on.

Well there is quite a bit here for you to digest. Post back and tell us how
you get on.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm going to sound like an idiot...(I DID say I was a novice) but how do I
make foreign keys and how do I find out how a query is defined?

Hi Jennifer

So doesn't all this boil down to three tables...?

Customers
========
CustID: autonumber - primary key
CustName: text
SortOrder: number

Resources
========
ResID: autonumber - primary key
ResName: text
ResDescription: text
Active: yes/no

Sales
====
ResID: autonumber - primary key
SaleCustomer: foreign key to Customers
SaleResource: FK to Resources
SaleDate: date
SaleAmount: currency

Everything else you need can be extracted by queries based on these three
tables.

You MUST remember that Access is NOT a spreadsheet application. It is a
relational database application. An Access table should NOT look like a
spreadsheet, with repeating columns or groups of columns such as:
Count01,
Cost01, Count02, Cost02, etc.

Incidentally, your crosstab query SQL statements below refer to [Query 2]
and [Query 5]. How are they defined?

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a temp table (TEMP) that is what the data is uploaded into.
(Field1 -
Text, Field 2 - Number, Field 3 - Currency, Field 4 - Number, Field 5 -
Currency ...Field 16 - Number, Field 17 - Currency)

I have a another temp table (Resource TEMP) that renames the fields of
the
temp table. (Customer - Text, Count01 - Number, Cost01 - Currency,
Count02 -
Number, Cost02 - Currency ...Count08 - Number, Cost08- Currency)

I have a resource table (Resources) that assigns a resource number to
items
that I'm charging for. (Ie: Blackberry...etc.) (Resource - Text,
Description
- Text, Active - yes/no)

I have a SortOrder table (SortOrder) that sorts the customers so that
they're in the correct order in the end. (SortOrder - Number,
Customer -
text)

And I have a Master table (Master Table) that lists each record
(Month -
date, SortOrder - number, Customer - text, Resource - text, Count -
Number,
Cost - Currency)

For costs:
TRANSFORM Sum([Query 2].Cost) AS SumOfCost
SELECT [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource,
[Query
2].Description
FROM [Query 2]
GROUP BY [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource,
[Query
2].Description
PIVOT [Query 2].Month;

For counts:
TRANSFORM Sum([Query 2].Count) AS SumOfCount
SELECT [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource,
[Query
2].Description
FROM [Query 2]
GROUP BY [Query 2].SortOrder, [Query 2].Customer, [Query 2].Resource,
[Query
2].Description
PIVOT [Query 2].Month;


The goal of this project is to have one table (that looks exactly like
the
table that I imported from excel) that is a sum of all the inputted
months
for each customer and item.

This is the SQL view of the resulting query that I currently have.
SELECT [Query 5_Count].Customer, [Query 5_Count].[01 Blackberry],
[Query
5_Cost].[01 Blackberry], [Query 5_Count].[02 Sametime], [Query
5_Cost].[02
Sametime], [Query 5_Count].[03 Docushare], [Query 5_Cost].[03
Docushare],
[Query 5_Count].[04 eRoom], [Query 5_Cost].[04 eRoom], [Query
5_Count].[05
Secure ID], [Query 5_Cost].[05 Secure ID], [Query 5_Count].[06 Verisign
ICEA
Certs], [Query 5_Cost].[06 Verisign ICEA Certs], [Query 5_Count].[07
SSL
Entrust Certs], [Query 5_Cost].[07 SSL Entrust Certs], [Query
5_Count].[08
Wildcard Certs], [Query 5_Cost].[08 Wildcard Certs]
FROM [Query 5_Cost] INNER JOIN [Query 5_Count] ON [Query
5_Cost].Customer=[Query 5_Count].Customer;

Thanks for all your help so far,
Jennifer


:

Hi Jennifer

I do have master tables and queries that list out each record...if
that
helps.

That is GREAT news! it means we don't have to go back and redesign
your
tables

Can you please post a description of the relevant tables with field
names
and data types?

Also, can you please post the SQL from the query that produces the
month
columns (open the query, then click View>SQL view and copy and paste
from
the window).

I'm thinking you can probably generate a query on the fly to get what
you
want, so please tell me as accurately as possible what you want to
display
in your form/report.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand



Okay let me start by saying I am pretty new to Access. And yes the
months
are the columns. I didn't know how else to get a sum.

I do have master tables and queries that list out each record...if
that
helps.

:

Hi Jennifer

Do you need the exact sendkeys?

No - that would be far too scary!

Actually, what is most scary here is that it looks like you have a
separate
column (field) in your table for each month. Is that the case? Or
is
this a
crosstab query?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

My query looks like this:
Sort Customer Description 01/2008 02/2008
03/2008
SUM: [01/2008] +[02/2008]+[03/2008]

When I send keys I basically use arrows to get to the SUM column,
insert a
column, use arrows to get to the most recent month, enter, save.
Then
I
tab
over to get back to SUM, build, add +, add most recent month,
enter,
save,
close.

I have to do this for 2 queries and its the exact same process.
(one
for
cost and the other for count)

Do you need the exact sendkeys?

:

Hi Jennifer

There is almost always an alternative to SendKeys.

Tell us more about what you are trying to do and how you are
currently
achieving it (or not!) with SendKeys.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I import a file monthly into Access and through a series of
queries I
output
one table that lists customers and the counts of items and how
much
it
costs
summed up to the current date.

IE:
Upload:
May 2008
Customer Item 1 Cost 1 Item 2 Cost 2
C1 25 $25 4
$100
C2 10 $10 0
$0

Output:
Customer Item 1 Cost 1 Item 2 Cost 2
C1 254 $254 64
$256
C2 213 $213 0
$0

The Output represents the sum of all of the current months.

In order to get the sum, I run a macro that sends a lot of
keys
in
order
to
add the new month to the query and then to add it to the sum.

Is there a way to use VBA code to do this? I'm having trouble
with
the
sending keys, sometimes it works and most of the time it
breaks.