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.
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.
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
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.
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.
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?
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?
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?
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?
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...
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.
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.
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?
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.
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
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
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.
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.
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])
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.
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.
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.
