Make A Table From A Crosstab Query

Aug 24, 2007

I want to make a table from the results of a crosstab query and am struggling to find a way. Is this possible?

Any help gratefully received!

View Replies


ADVERTISEMENT

Queries :: Unable To Make Crosstab Query To Filter Records From Table

Sep 17, 2013

I am trying to make a crosstab query to filter my records from my table.

Here is the scenario.

I want to make a query that will return me my Rep ID, Rep Name, his Bonus and his GV-Q (another value) based on every month.

Now I make a crosstab query and here is the syntax.

Code:
TRANSFORM First([TBL Qualification Data India].[Bonus Rank]) AS [FirstOfBonus Rank]
SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
FROM [TBL Qualification Data India]
GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
PIVOT [TBL Qualification Data India].Period;

This resulted in a column for Rep Number, one column for Rep Name and columns for all the period of Bonus I am going to have., so there are basically 9 columns for this till this month for each month and bonus value shows as values for all these month (period) columns.

Now in this same syntax, I want to have my Rep GV-Q value as well as his bonus to show in the same query, I read and came to know that it's not possible to directly have two values or two column headings in a crosstab query, I must have to make a new crosstab query and then use a normal select query to display records from these two crosstab queries, so I went ahead and made a new similar but with one value field changed crosstab query and here is the syntax for that.

Code:

TRANSFORM First([TBL Qualification Data India].[GV-Q]) AS [FirstOfGV-Q]
SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
FROM [TBL Qualification Data India]
GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
PIVOT [TBL Qualification Data India].Period;

Now after this how to make a select query to show the data from these two queries.

I can make a normal query based on these two crosstab queries and manually add all fields and then I would have my result but then after every month I have to manually enter these two extra month details from both crosstab queries to my final query and that's not what I want.

Is there any method to do this by gathering data from these two queries into one and achieve the result I want or if there is any other approach to tackle this.

To explain my database and my need for output, I am attaching few pics to make things easier if I made some mistakes in explaining my problem. It's included in attached zip since I am not able to post images or links.

View 5 Replies View Related

How Do You Make A Multiple Crosstab Query?

Dec 18, 2005

Hi there thanks for all your help on the last problem got it working now. What I need now is 2 crosstab queries to become one, example below on how I want it to look:

Created By-----Total Of Date-----Total Of Date Taken-----Policy Name Type (eg Breakdown,Caravan going across counting Date and Date qouted in separate columns)

Here is the SQL for each crosstab query:

TRANSFORM Count([Date]) AS [The Value]
SELECT [Created By], Count([Date]) AS [Total Of Date]
FROM qryPresonalReport
GROUP BY [Created By]
PIVOT [Policy Name Type];

TRANSFORM Count(qryPresonalReport.[Date Taken]) AS [The Value]
SELECT qryPresonalReport.[Created By], Count(qryPresonalReport.[Date Taken]) AS [Total Of Date Taken]
FROM qryPresonalReport
GROUP BY qryPresonalReport.[Created By]
PIVOT qryPresonalReport.[Policy Name Type];

Cheers

Headintheclouds

View 1 Replies View Related

Reports :: Attendance Sheet - How To Make Crosstab Query

Mar 15, 2013

I am making some attendance sheet on access as follows

Name !09:00!10:00!11:00!12:00!13:00!14:00!15:00
------!-----!------!-----!------!-----!------!
Test1 !Prsnt! ! ! ! ! !
test2 !Absnt! ! ! ! ! !
test3 !Vction! ! ! ! ! !

I have tables like

workersmaster(workerid,workername,joiningdate,stat us)
Workerdetail (Workerid,workername,entreetime,attendance)
timing(timing(0900to1500)

Now I wanted to make report as mentioned above, how can i do this?

View 4 Replies View Related

Reports :: How To Make Report Autoupdate With Crosstab Query

Mar 26, 2013

I made a report with following crosstab query.

TRANSFORM Workersdetail.workername AS CountOfedate
SELECT Workersdetail.[attendance], Count(Workersdetail.[edate]) AS [Total Of edate]
FROM Workersdetail
GROUP BY Workersdetail.[Workername], Workersdetail.[attendance], Workersdetail.[workerhourenter]
PIVOT site+Cstr([workerhourenter])

I wanted to know that, is there any option through which my report gets autoupdate or refresh incase of addition in SITE field (as mentioned with PIVOT)?

View 1 Replies View Related

Queries :: Crosstab Query Which Can Append To Table

Jan 8, 2015

I have a crosstab query which i would like to append to my table..can't change it to a append query...it changes the structure.

View 1 Replies View Related

Diagonalize A Table (Comparing Items In A Crosstab Query)

Mar 17, 2007

I posted in this section because I don't know a better category.

I have data (from a cross tab report) that looks:
16763

and I would like it to look like:
16764

So that the 'X's make a diagonal.... It helps the humans understand the data better. :D

I was thinking of exporting the query to excel and then running a script that would look for 'X's in the first column and then move those rows to the bottom. And so forth for each column, until they are in a diagonal like above. (Because the rightmost 'X's are moved to the bottom last).

Am I barking up this tree the wrong way? Does someone know how to do this?

EDIT: I have multiple groups like 'Date' in the above example. Basically for each group I would like it to be diagonal. I also have some groups that use 'Feature A, Feature B, ect' in place of Date, and for those groups I need to leave the rows undisturbed.

View 1 Replies View Related

Extracting Data From Linked Db2 Table Using Access Make Table Query

Aug 29, 2006

Hi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?

Thanks for your support
GinnyP

View 1 Replies View Related

If A Make-table Query Result Is Null, How To Have A Default Message Appear In Table ?

Jan 21, 2005

Hi everybody,

Beginner here needs help !

I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?

Thanks in advance !

View 1 Replies View Related

Crosstab Query Based On Crosstab??

Sep 21, 2007

Hi all, I am utterly unsure if what I want to do is even possible:

I have two crosstab queries, qryRewCOCredit and qryWrapCOCredit which show the changeover (CO) times for the specified machine when they are NOT zero. (all zero entries don't show up).

There are many cases when there is a CO for the Rewinder on a specific day, but not for the Wrapper, and vice versa.

I want to make another crosstab query which performs a calculation. To keep it simple:

If (RewCOCredit>WrapCOCredit) Then
5-RewCOCredit
Else 'WrapCOCredit>RewCOCredit
5-WrapCOCredit

Please help!!!

View 2 Replies View Related

Changing The Name Of The Created Table In A Make-Table Query

Sep 23, 2007

Dear Access Expert

I wanted to know if it is possible to change the name of the Table which is going to be created using a Make-Table Query via code (VBA).

For example if my Make-Table query currently creates a table with the name "Table1" I want to change it to name "Table2" and then change it Back to "Table1" or "Table3" etc.... depending on the users selection.

View 6 Replies View Related

Queries :: Make Table Query - Find Table Name

Oct 14, 2013

How can you determine the name of a table that has/is going to be created by a make-table?

View 2 Replies View Related

Make Table Query

Dec 22, 2005

When using a make table query, can you include an ID tag. in the output table? if so how

View 2 Replies View Related

Make Table Query Help

May 15, 2006

:confused: I am using a Make Table Query to filter a Linked Excel Table. Is there a way to cut/drop the first 8 characters of the text out of one of the fields as it creates the new table?

Field NameExcel DataFinal Data
Model_NameLATITUDE D600D600

Also, the final table has two Relationships with two other tables. When I run the Make Table Query once a week, I have to break the relationship to get it to run. Is there an easier way to dial with this?

View 2 Replies View Related

Make-Table Query ??

May 19, 2006

Is there a way in a Make-Table query to tell the table to open when it's created? I would assume there is a function or SQL code that would do this, but I know very little SQL. Can anyone point me in the right direction? Thanks!

View 2 Replies View Related

Make Table Query

Aug 27, 2007

I have a database named NewUpdate.mdb. I have another database named MainDatabase.mdb. Almost all the tables in NewUpdate.mdb are linked tables that are in the MainDatabase.mdb file.

I've written a simple make-table query in NewUpdate.mdb that makes a backup of a table that is located in the MainDatabase.mdb file. (See code below.)

SELECT tblProviderRate.* INTO tblProviderRateSave
FROM tblProviderRate;

The only problem is that this new table is created in the NewUpdate.mdb file. I need the query to be stored in the NewUpdate.mdb file and the "new" table to be created/stored in the MainDatabase.mdb file.

This sound simple enough, but I'm drawing a blank as to what I need to do in order for this to occur. Can someone tell me what I'm leaving out??

Thanks,
CRhodus

View 2 Replies View Related

Make Table Query

Nov 26, 2007

How to make-table query to make a certain field a memo field instead of a text field? The reason I cannot use the text field is the limitation is 255 characters while I require 2000 characters?

View 4 Replies View Related

Make Table Query

May 28, 2006

Dear friend

I need your help desperatly. In my db, I am making a "make table query" to match some text.

Help me build this please.

First a form which has text box, where I will put in some words..
Then a make table query, which will have =[form]![name of form]![control name]
A report on same table.

Means when some one puts some words in text field of form, on pressing hit button, a like statement is automatically created in one field of query which matches those words and a table is generated from them. Result is report.

Hope this is clear, could you please help me build this?

View 14 Replies View Related

Make Table Query

Dec 7, 2006

How can I set a button in an MSExcel sheet to run MSAccess make table query?

Please help. Thanks.

View 2 Replies View Related

Make-Table Query With Relationship To New Table

May 31, 2005

I've got a problem with a make-table query that creates a new table which is then populated with new values that relate back to the original table the trouble is the make-table won't run with that relationship in place.

Is there a way to delete the relationship then reinstate it after the query? or any other way round that someone can think of.

Thank You

View 1 Replies View Related

Make Table Query From Filtered Table

Jul 31, 2007

I have come across a snag in my access project I am currently undertaking and need some advise on what should be done next. . .

In a nutshell, this project's primary purpose is to perform a search on a table of information based on many fields and generate a report based on the records which match the search. I have the search form working (similar to this one here) http://allenbrowne.com/ser-62.html

It displays the results from the table based on the search criteria. The form is great for viewing the results which match search criteria; however, I am having trouble doing anything else with the data (perform operations on the data, send the results to the table, etc. . .). How should I go about moving this data to a table?:confused:

View 3 Replies View Related

Make Table Query - With Date In Table Name

Aug 20, 2007

I have used/am using the "INTO tbl_temp_copy" to create a temp table from a query called tbl_temp_comp. Everytime I run my query it deletes and then creates a new table with the same name - and this is what I have been wanting.

However now I would like to create a table with the date in the table name so:- "INTO tbl_temp_copy_Date()" but obviously this does not work and I do not know if it is even possible?

Anyone able to do this or know that it cannot be done?

View 1 Replies View Related

Make Table Query In A Report

Oct 31, 2005

How can I get a make-table query to run in a report?

View 6 Replies View Related

Make-table Query Property?

Apr 19, 2007

I am having trouble with a make-table query. I run a query that populates my website with data daily. I have a bunch of fields in the db that I wanted to be displayed as one field on the website, so in my query I concatenated them. My website is able to distinguish which fields are text and which are memos so I can display multiple lines. Everytime I run the query, it creates the concatenated field as a text field. Is there a way I can default this to a memo type?

Thanks.

View 1 Replies View Related

Make Table Query With Primary Key

Mar 24, 2008

Hi,

Anyone has the idea of creating primary key in a table using make table query?

Thanks.:confused:

View 1 Replies View Related

Make-table Query With Index

Sep 22, 2005

I am creating a table with a make-table query. The only trouble is that I can't figure out how to designate which field will be the primary key. Is there a way to do this...... other than creating the table, then manually going into desing mode and specifying the primary key there? I want this to be automated.

Thanks,
David

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved