Crosstab query and Table

[dorandoran]

Here is the db with sample data.

http://download.yousendit.com/AAA18BC7520F196E

I want to use qryCrosstab and table "ALL" to achieve the result that would look like this query "qryFinalResult".

Table [ALL] has a field called filename and it stores the table names that qryFinalResult is using.

Is it Possible ????

Can someone please please help ? stuck with this for last 48 hours.

 

[dorandoran]

Can someone pleas help ?

1. I use a vba to import bunch of excel files into individual tables.
Tables are named after the excel file. A field is created in these new
tables called "FileName" and it's populated with the filename.

2. I am doing this so I know what file the data came from

3. table "ALL" gets populated with all of these excel data and the
name of the file.

The crosstab is taking [ALL] table and aggegating hours, comp and
other data elements.

FinalResult is takign the crosstab and i m doing right join to pull
data from crosstab and all the data from each tables.

Is there a way I can only use table [ALL] and qrycrosstab to make my
look like qrufinalresult ?

Is there a vba that will do right join automatically between crosstab
and all the table in import (except table ALL)?

Here is the query that gives me all the table name.

SELECT DISTINCT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "msys*" And (MSysObjects.Name) Not
Like "all*") AND ((MSysObjects.Type)=1));

 

[dorandoran]

Forum Owners: DELETE THIS POST IF NO ONE GOING TO ANSWER IT. I AM REALLY FRUSTRATED. THIS FORUM NEVER WORKED FOR ME.

 

[PHV]

Please, read carefully this: FAQ181-2886

 

[dorandoran]

Why I am reading rules and regulations ? Take my list off since it's not getting answered. What is wrong with that? I am not being rude or anything. I just hate to check every 5 mintues and find out there is no one going to answer it.

 

[dhookom]

From the looks of your final result query, you need to create a union query that selects individual columns into a single resulting column with the previous column name as a value in a new column. You would then be able to create a crosstab the the column heading is generated from the FileName and the previous field name which is now a value.

The value in the crosstab will be the "First" of your previous field value.

Before I would create a working solution for you with the union query and final crosstab query, I would like to know why the final result is needed. This is one of the weirdest requests I have read so I would hesitate at taking my time to provide a more complete solution without a legitimate justification.

Also, please don't be so impatient. I am answering this in the middle of my work day (taking a break from the job that earns my pay).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

check every 5 mintues
Why not simply click the E-mail Notification link ?

I suggested you read the FAQ in the hope you'll ask better formulated question (instead of hoping someone download your DB).

 

[dhookom]

dorandoran,
How long do you think it should take to have a question answered based on the amount you are paying?

How many people do you expect to take the time to download and review your file?

Why don't you just check the box or whatever to be notified if someone posts. If you would have looked at the Who's Marked This Thread? box near the top right of this page, you would have seen earlier that I had marked it so I would be notified. I didn't have time earlier but was interested in helping if needed.

If you don't like TT then don't use it.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]