Variable Table Name

[adam.sherratt]

Dear all

I am trying to summarise some data from my main table to individual
tables that are then used to link to excel.

The main table contains the data for individual work packs, the user
that completed it, the date it was done, and how many points it is
worth.

I want run a query that tells me how many points a user did in a month.
Note, I am seriously oversimplyfing here, what I want to do is much
more complex but this is the point I am getting stuck.

The data that is output to a table includes months, and the number of
points done by User(x) within that Month. i am creating the table
using vb, based on the Users(x) variable. this variable is looked up
from a list within the code.

So each time I run the code I want to do the following.

Condense the Main table down to a monthly view - tblMonthDataCut.
This contains all jobs, all points, and all users within a month.

Sumaries the tblMonthDataCut to count the number of jobs done by
user(x) and sum up their point value. I create a table that contains
that information. UserID, Jobs, Points.

I then want to write the points and jobs to individual tables that are
linked to Excel so that I can create graphis in excel. The individual
table is created by code using the User(x) name. I have been able to
loop the list of users and create a table for each.

I now want to use the Users(x) variable to lookup the name from the
tblPointsSummary to find the number of points and jobs done, and write
it to the individual table. This is how I was planning to do it:

Dim Points As Integer

DLookup("[Points]", "tblPointsSummary", "[UserID]='" & Users(x)) =
Points

However, this was giving me an error
"Sytax error in string in query expression '[UserID]='JRedfern'

So i know that it is getting the value for UserID, but it is not able
to use it as a lookup.


Any suggestions?

I know that this is a really awkward way to do this, but I am seriously
constrained by the current database, and can't make many changes to it
as it is very widely used. If you can suggest a better way of doing
this, i'm all ears.

Thanks

 

[Douglas J. Steele]

It looks as though you're trying to assign a value to the DLookup. DLookup
doesn't work that way: it's for looking up values, not writing them.

You're also appear to be missing the closing quote:

DLookup("[Points]", "tblPointsSummary", "[UserID]='" & Users(x) & "'")

 

[adam.sherratt]

HI guys,

Sorted it:

Points = DLookup("[Points]", "tblPointsSummary", "[UserID]=""" &
Users(x) & """")

not enough """"""""""".......

 

[John Vinson]

Dear all

I am trying to summarise some data from my main table to individual
tables that are then used to link to excel.

It's not necessary to put data into a Table to export it to Excel.

You're just adding an extra step; rather than using a MakeTable or
Append query, simply use a Query (the one that you would use as the
append query) as the source of your TransferSpreadsheet action.

Condense the Main table down to a monthly view - tblMonthDataCut.
This contains all jobs, all points, and all users within a month.

Again... you can base a Query on a Query. It's (probably!) not
necessary to create tblMonthDataCut; just base your totals query on
the query which pares data down, or (better) simply include criteria
which select the desired records.

Sumaries the tblMonthDataCut to count the number of jobs done by
user(x) and sum up their point value. I create a table that contains
that information. UserID, Jobs, Points.

A Totals query can do all of the above in one operation with no extra
tables.

I then want to write the points and jobs to individual tables that are
linked to Excel so that I can create graphis in excel. The individual
table is created by code using the User(x) name. I have been able to
loop the list of users and create a table for each.

I now want to use the Users(x) variable to lookup the name from the
tblPointsSummary to find the number of points and jobs done, and write
it to the individual table. This is how I was planning to do it:

Dim Points As Integer

DLookup("[Points]", "tblPointsSummary", "[UserID]='" & Users(x)) =
Points

However, this was giving me an error
"Sytax error in string in query expression '[UserID]='JRedfern'

You need the closing quote; you are also apparently trying to set the
value of DLookUp to the variable Points, and I think you want to do
the opposite. Try

Points = DLookup("[Points]", "tblPointsSummary", _
"[UserID]='" & Users(x) & "'")

So i know that it is getting the value for UserID, but it is not able
to use it as a lookup.


Any suggestions?

I know that this is a really awkward way to do this, but I am seriously
constrained by the current database, and can't make many changes to it
as it is very widely used. If you can suggest a better way of doing
this, i'm all ears.

Well... you ARE making major changes to it, adding dozens (?) of
redundant, unneeded tables. If you use Queries instead to link to
Excel, you will be doing LESS.

John W. Vinson[MVP]

 

[adam.sherratt]

Hi All,

Thanks for the advice! I am currently looking at the ways and means of
exporting directly to excel. I'm still learning as I go along, so this
advice is pretty damn useful!

Think its time to actually read that book.......

cheers again