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 Replies


ADVERTISEMENT

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 9 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

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

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

Comparing Records In A Table

Jun 2, 2006

I have been searching on here and have not found an answer, could be due to me not seeing it or its not here. I have a multi part question.

Anyways, here is what I have. I am getting data from a time clock, which dumps the data into an access database. It dumps the data as EmployeeNumber (Number), DateTime(text). I use the format command and make a new table to give me EmployeeNumber(Number), Date(text), Time(text).

Question 1:How can I convert these to date and time fields instead of text fields?

After I do that I need to be able to compare the records within the table, such as: I need to check to see if the date is the same between one record and the one above it (assume that I have sorted this correctly) if the date is the same then I need to subtract the first record from the second record to give me the number of hours between the two records.

Question 2: How would I go about comparing those records in the same table?

Thanks in advance to those who can help. Even ways not to do this would be good.

View 5 Replies View Related

Modules & VBA :: Comparing A String To Criteria In Another Table

Sep 9, 2014

In my database, I use TextStream.ReadLine to read a .txt file line by line and store pertinent parts of each line into specific fields in a table. One of these fields is called "Remarks", which is basically a descriptive paragraph of text explaining a task. Everything works great so far.

However, my leadership would like a condensed version of the "Remarks" field. The only way to really do this right now is for someone to manually read each "Remarks" field and create their own like condensed version of it. Let me give you an example...

Remarks: "Conduct Project Delta tests in association with IBS/SCADA systems and CIKR (Critical Infrastructure/Key Resource) cyberspace terrain, develop CPT certification processes and checklist."

Condensed Version: "Project Delta for IBS/SCADA and CIKR"

I've played with using Select Case to automatically create a condensed version of the Remarks field:

Code:
Select Case True
Case InStr(strRemarks, "SCADA") > 0 and _
InStr(strRemarks, "Project Delta") > 0:
!Condensed = "Project Delta for IBS/SCADA and CIKR"
End Select

However, this is too much VBA maintenance for each different thing that needs condensed. If a new tasking comes out, then I'll have to go into VBA and custom create a new Case for it. Multiply that by 10-20x each week.

Instead, I would like a form where my users can specify the criteria themselves. The Remarks field would be compared against the criteria to create a new condensed version of the Remarks field. I'm not sure how to go about this though. What I'm envisioning is this...

So each Remarks string would get compared against each criteria. If the Remarks string contains the words "SCADA" and "Project Delta", then the condensed version would be "Project Delta for IBS/SCADA and CIKR". If the Remarks string contains "OPSEC Assessment" then the condensed version would be "OPSEC Assessment". In the pic above, the form is based of another table that contains those fields in the form. I'm not sure if this is the most efficient method for my goal or not. Either way, I don't know how I would compare the Remarks string to records in this new table in order to create a condensed version.

View 14 Replies View Related

Comparing Data From Two Tables And Appending Into 3rd Table

Dec 8, 2012

I have 2 tables as below

Table 1
ID RID StartM EndM
1 xyz 1100 040.0935 040.1254
2 xyz 1100 029.0110 038.1003
3 xyz 1100 029.0110 038.1010

Table 2RID
StartM
EndM

[code]...

Now I need to create 3rd table based on above two tables

for table1.XYZ 1100 table1.StartM>Table2.StartM then table1.ID=2or 3 and
for table1.xyz 1100 table1.EndM< Table2.EndM then table1.ID=1

View 5 Replies View Related

Query: Comparing Two Tables

Mar 22, 2006

I'm trying to write a report which initially I didnt think would be hard to do, but when it came time to write the underlying query I realized it was a bit above me.

I cant give away too much of the database structure here, due to legal issues, so sorry for being vauge. Hopefully I can give enough information that someone can get me started.

I have three tables that will be used in this query. One is called NAMES and has all of personal details of contacts. The only fields that are needed from this table are the primary key and citizenship field.

The other two tables are virtually the same, and they are the two being compared. One is REGISTRATION and the other APPLICATION. Essentially what I want here is to be able to compare the number of who applied to the number who actually registered.

Here are the pseudo-tables to give you a better picture:

NAMES
nID
FName
LName
DoB
Citizenship

REGISTERED
programCode
startDate
endDate
nID (foreign key)

APPLIED
programCode
startDate
endDate
nID (foreign key)

I've tried a few different approaches which worked in my head, but when it came time to run the query the results were different. Any suggestions on how I should tackle this?

View 3 Replies View Related

Query Comparing Two Other Queries?

Mar 24, 2006

I want to compare two queries as follows: Select all the records in query1 where the value in fieldA does NOT also appear in any record in fieldA of query2. Is there an easy way to do this using the query tools or will this require a vba function?

View 3 Replies View Related

Comparing Rows In A Query

Aug 26, 2006

is it possible to compare values in 2 different rows? for example if the query returns this:

1 | $5.00
2 | $6.00
3 | $20.00
4 | $30.00

i want to find out the cost difference between 1 and 2 which will be $6-$5 and then 2 and 3 which will be $20-$6 and so on. how can i make this happen in SQL? any suggestions?

View 6 Replies View Related

Comparing Two Fields In A Query?

Jun 18, 2007

I am trying to compare two different fields in the same query and return the most recent date in some another field. Would I be able to do this and if so how?

Any help would be greatly appreciated.

View 14 Replies View Related

UNION Query Using For Comparing?

Oct 2, 2007

Hello again...

Is it possible to use a UNION query for comparison between to tables or queries?

If so, please advise how to

example:
one query consists of a selection of books, grouped by category for a student
the second query consists of all books, grouped by category for a student

The purpose of the new union query is to check if the student has all the applicable books in his booklist; if the student has forgotten to pick a book for his list, this new query will show the one(s) the student forgot.

Kind of cryptical this example, hope i could make it clear enough...

View 4 Replies View Related

Comparing Values In A Query

Dec 7, 2007

Okay I have a query that pulls from a table. It's pretty simple - nothing big.The query will be used to correct information in some sales tax data.I have 3 colums with sales tax info, and they should all be the same. I want to compare each column to show only those which don't match each other.NetTax, TotalTax, and TaxB are the fields.In the query, for the criteria of NetTax, I put in:<> [TotalTax]When I run the query, I get results, but they are all equal.EG:4.35 4.35 4.35I don't want them to show, and not sure if that's the right way to go about it or what.If I run without that criteria, I get 21,600 results.With that in the criteria, I get 304 results. So it is working somewhat...but for whatever reason some don't seem to be seen as being equal? I don't get it. Data type is number and they are formatted the same way.

View 14 Replies View Related

Comparing Values In A Query

Dec 17, 2007

Hi...
I will try to keep this succinct.

I have a query that totals a column in a sub-form. The query also gets a sum from the main form for the same record. Is it possible to have a message/colour change/warning if the total of the column in the sub-form exceeds the other number in the query? I would need this comparison to take place within this query, rather than creating a new one.

Thanks!
Kathryn

View 8 Replies View Related

Comparing 4 Tables With A Query

Oct 13, 2004

I have 4 tables (one for each quarters data) each with the same fields:
Fund
FundFamily
Value

I have a query for each table that sums the values for each FundFamily (if there are multiple funds with the same FundFamily, it only shows the sum for the Family).

I am wanting a query that will show all of the different FundFamily fields from each table, but only once (say there is a Value in one quarter, but not the next). How is the best way to go about this?

Does what I'm trying to do here make sense?

-GorillaBoze

View 2 Replies View Related

Queries :: Comparing A Text Field In A Table To Textbox On A Form

Jul 8, 2013

I have a table that has a date field set to text. I use the Mid function and get just the month in a query. I have a textbox on a form that when the user adds a month or removes a month from a selected listbox it adds or removes that months number from the textbox.

I want to us that text box on the form as the criteria for the month (the Mid function getting the month) and I can seem to get it to read what's in the textbox and use it as the criteria. I can manually type in a number and it works fine.

View 6 Replies View Related

Update Query Comparing 2 Tables?

Dec 8, 2005

Hi
I have such situation:
i have tables [k] and [r] with street and city field.

I would like set on field[dubel] in the second table [r] in the rows where the street and the city are the same for the both tables.
There could be one to many relations. It means many fields in [k] could have the same as in [r]

I've tried with this query but it marks all the fields....

UPDATE r SET dubel=1 where EXISTS ( SELECT r.str, cit
FROM k, r
WHERE (([k].[str]=[r].[str]) AND ([k].[cit]=[r].[cit])));

when i'm using just select part, it gives me good results.....
Can Anyone help ME?
THANKS

View 1 Replies View Related

Update Query Comparing 2 Tables?

Dec 8, 2005

Hi
I have such situation:
i have tables [k] and [r] with street and city field.

I would like set on field[dubel] in the second table [r] in the rows where the street and the city are the same for the both tables.
There could be one to many relations. It means many fields in [k] could have the same as in [r]

I've tried with this query but it marks all the fields....

UPDATE r SET dubel=1 where EXISTS ( SELECT r.str, cit
FROM k, r
WHERE (([k].[str]=[r].[str]) AND ([k].[cit]=[r].[cit])));

when i'm using just select part, it gives me good results.....
Can Anyone help ME?
THANKS

View 1 Replies View Related

Queries :: Comparing 2 Fields Query?

Mar 11, 2014

Trying to Compare 2 fields Status with answers Yes or No, from 2 Tables PipeLine and Pipe, and get the fields that don't match, what am i doing wrong?

SELECT PipeLine.[Project Name], PipeLine.[Capacity DC], PipeLine.[Project Num], PipeLine.[Status]
FROM PipeLine LEFT JOIN Pipe ON PipeLine.[Project Name] = Pipe.[Project Name]
WHERE (((Pipe.[Status]) ="Yes");

View 1 Replies View Related

Creating A Table To Cross Index Items In Another Table

Sep 14, 2005

How do I create a table that can cross index items in another table. Maybe I am not using the right terms here so let me show a small example.

Say I have a tables of words.
tblWords
numWordID
txtWord

Then I have some entries, all more or less synonyms of each others
fresh
new
clean

Now I want to create a cross-index table, related to the table "tblWords" where I can select synonyms from words already in the table "tblWords", so if I for the word "fresh" add "new" and "clean" as synonyms or entries, if I then go and look at the word "new" it will already have the synonyms "fresh" and "clean", likewise the entry "clean" will then have the synonyms "fresh" and "new".

Kind of a many to many relationship junction table but only with one table!

I hope my explanation have not been to confusing, but let me know if you need a clarification.

Thanks

View 6 Replies View Related

Comparing Multiple Dates And Times In A Query

Mar 8, 2007

Hi there,

I'm trying to 'rank' x5 categories that overlap. To determine the ranking i'm utilising date & time (if the dates are equal). I've managed to do this comparing x2 of the categories but struggling with the logic for x3,x4 & x5 categories

I have the data in separate tables by category date & time and in one large table.

Hoping someone can assist as my head is starting to hurt.

Thanks

View 4 Replies View Related

Queries :: Comparing Pairs Of Records In A Query

Aug 9, 2013

I need to identify pairs of records by their ID and date fields, then compare their number values to get a text result.

I have a query that has the following fields

AuthInstanceID, ConsumerID, AuthNumberID, AuthStart, AuthEnd, PSRUnits, BSTUnits.

Sample data is below (PSRChange and BSTChange are fields I want to self populate based on the comparison I'm trying to do)

AuthInstanceID....ConsumerID....AuthNumberID....Au thStart....AuthEnd....PSRUnits....BSTUnits - PSRChange -BSTChange
1374006036.........356679..........20255102....... ....4/22/13.......7/21/13....0.............416~~~~-Loss~~~~~-None
-432536491.........356679...........20255102....... ...1/21/13.......4/21/13....104..........416~~~~-Loss~~~~~-Loss
-124970517.........356679...........20147863....... ...10/23/12.....1/20/13....208..........520~~~~-None~~~~~-Null
504564357...........469432..........20254788...... ....4/22/13.......7/21/13....0.............520~~~~-None~~~~~-None
282523535...........469432..........20254788...... ....1/21/13.......4/21/13....0.............520~~~~-None~~~~~-Gain
356661326...........469432..........20254788...... ....11/20/12......1/20/13....0.............416~~~~-None~~~~~-Null

What I need to do is compare these records in the following fashion:

Identify pairs of records where ConsumerID is the same AND AuthStart dates are successive. Then compare PSRUnits of more recent record (by AuthStart) with the previous record (by AuthStart) and determine the change of PSRUnits between them. If change is positive, "Gain" in field PSRChange, "Loss" for a loss, "None" for no change, and "Null" if there is an error due to no previous record to compare with. Do the same with BSTUnits/BSTChange

I've tried adding the following fields, but this did not work

PrevEnd: (Select Max(AuthEnd) from tblAuthorizations Where AuthEnd < Auths.[AuthStart])
PrevBST: DLookUp("[BSTUnits]","tblAuthorizations","AuthEnd=" & [PrevEnd] And "ConsumerID=" & [ConsumerID])

View 14 Replies View Related

General :: How To Manage Items In Set And Individual Items

Jul 31, 2013

I just want to know how to manage items in set and individual item. Suppose my product list are

individual items = A,B,C,D,E,F,G,H,I,J,K
and 5 pc set = A,B,C,D,F
and 3 pc set = G,H,K

How should I design the table. Previously I designed the table for individual items and whenever orders for set is placed user had to enter individual items with quantity.

E.g. order is for 5 pc set = 3000

A=3000
B=3000
C=3000
D=3000
F=3000

Now I need to just say 5 pc set and it should be automatically populated. And also if order have combination of individual and set items.

View 3 Replies View Related

Adding Items To A Lookup Table

Dec 6, 2006

I have made a few lookup tables in the database I'm working on using the lookup wizard and entering the text I want in them. I made a form and they come over fine on them.
My question is that I thought that there was a way when you entered something other than was in that field it would automatically add it to that list and you could just select it next time instead of typing it in again. Is that possible or am I thinking of something else.

View 2 Replies View Related

General :: Selection Of More Items In More Than One Table

Jun 30, 2014

I'm creating a database for my job. I have 4 tables with information. When I need to deliver some items i must know which items to take, They are in those 4 tables.

I want to make it that i can click a menu on my switchboard that i see a report with all the information with a clickbox behind it. If i select this box and click a button generate it creates a report with all the items i've selected with the select box.

So in short:

1.) How do I create a table with all the items from the other tables that are automatic updated when insert a items in one of those 4 tables.

2.) How do I create a report where i can select some items that are generating a report with the selected items.

View 5 Replies View Related







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