Creating A Crosstabulation / Matrix
Feb 20, 2007
Hi,
I have a dataset with the following structure.
ColA------------>ColB----------->ColC---------->ColD
ClassA -------->OtherStud----->WorkWith----->PlayWith
AA --------------> DD ----------> 1 ------------> 0
AA --------------> EE ----------> 0 ------------> 1
AA --------------> BB ----------> 1 ------------> 1
BB --------------> FF ----------> 1 ------------> 0
BB --------------> GG ----------> 1 ------------> 1
BB --------------> KK ----------> 0 ------------> 1
CC -------------> AA -----------> 1 ------------> 1
CC -------------> BB -----------> 1 ------------> 1
ColumnA refers to students in ClassroomA, and Column B refers to "Other Students" in the school who could act as work and/or playmates. Note, ColumnB could also include some of the same students in ClassroomA who work or play with each other (For example, Students: AA and BB).
If a Student in Classroom A works with a student in ClassroomB, ColumnC takes the value of 1, and 0(zero) if otherwise
If a student in Classroom A plays with a student in ClassroomB, ColumnD takes the value of 1, and 0 (zero), if otherwise
So, looking at ColumnA, we find that there are three students: AA, BB and CC.
Student_AA for instance interacts with three students but only works with DD and BB. Similarly, Student_CC interacts with two students and works and plays with each of them.
Now, I am interested in constructing 2 separate matrices indicating which students in ClassroomA (1) Work and (2) Play with each other. This means that student_AA for instance works with student_BB, who is in his/her classroom, hence the cell in their matrix will have a "1". Again, Student_CC in ClassroomA works with and plays with 2 other members of his/her own class. So CC would have two "1s" in the matrix.
The resulting matrix for the "Works With" relation (ColumnC) among the 3 students in ClassroomA would look like this.
------>AA----->BB----->CC
AA---->0------>1------>0
BB---->0------>0------>0
CC---->1------>1------>0
This matrix is the output I am interested in. I would need to produce this matrix for the relation specified in columnC and ColumnD.
I would certainly appreciate some ideas to help me produce this.
Thanks in advance for your assistance.
Sincerely, DK
View Replies
ADVERTISEMENT
May 1, 2006
I'm trying to setup an apparel matrix that will allow me to validate user entry on my inventory database.
We have garment styles and each style can have a variety of different colors and sizes. Some garment styles are available in some colors/sizes and not others. I created a table each for Style, Color and Size which contain the possible values for each of those. I then setup a form that lets the user pick a style and then in a subform create however many combinations of color and size they need. From this I would like to generate an item number such as 1100-Red-XL(style-color-size). I would also like to create a table from this that is essentially a list of all possible item numbers. Then later, when the user is adding/removing inventory I want to check to make sure that the style/color/size combo they are trying to Add/Remove exists.
I would also like to use the item number list to generate inventory status reports.
I'm stuck at how how to take the matrix form data, concatenate each style/color/size into a single field and then generate a new table with that.
View 2 Replies
View Related
Oct 13, 2004
I need to have MS access track various quantities of each of 500 items at 200 different locations. What is the best way to handle this.
View 9 Replies
View Related
Mar 2, 2006
Hey all,
I'm not sure whether I should put this in two separate posts, but here goes...
I have just started constructing a database which has many levels.
Some of the field records may not have an entry (as they may not be known) and calculations must be done between these levels. The bottom level is in the form of matrices and I am unsure of what implications this could cause to implementing them in the calculations.
1) I assume from what I know that non-values (or empty record fields), will not work in queries (i.e. they will not be ignored, they will be given a value such as 0 to be used in the calculation). I therefore ask, is it easier to exclude these non-values from the query - or is it better to assign a default value such as -9999, then filter these values out?
Would it be easier to do this exclusion using a filter, or using code?
2) My second question may not even be possible but...
My calculations will involve using values from matrices. I am not sure of the most effective way of using these values in a query? I think there are several options, of which, these two may be the best options.
- turn a matrix from Excel into a table in Access (will mean lots of records - but can be done)
- link a matrix in Excel to an existing query in Access.
Now, the second option may not be possible, but what I would like to do is link a certain cell from Excel to a certain query - depending on the calculation.
i.e. Cell A4 used in Query1 and Cell A5 used in Query2.
I am sorry that I am so ignorant in these matters, but I have been asked to find out what the best way of approaching this is.
Thankyou all very much for your time, I hope what I am trying to achieve is clear!
Kindest regards,
Matt.
View 3 Replies
View Related
Aug 4, 2006
Hi,
I am looking to create a matrix like table in a form. It would be used for accessing the capability of Project Managers for a recruitment company. On the X axis there will be different industries (ie IT, Finance etc). On the Y axis there will be different categories (like strategy, applications etc). Then I will need some kind of drop down box to link it all together and give each person a score out of 5. So for example somebody could have a score of 2 for their Strategy knowledge in IT Companies but a score of 5 for their Strategy knowledge in Applications etc...
Does anyone know if this is possible and if so any help on how to construct this would be much appreciated,
Many thanks in advance, Alex
View 5 Replies
View Related
Mar 25, 2015
I have produced a query, which counts the number of records fulfilling each set of criteria, but I can't now convert that into the matrix presentation.
I have a attached a copy of what is produced currently through Excel.
Basically each count on the query represents one of the boxes within the matrix. For example if the record Impact is 1, and the Likelihood is 1, then it would be counted within the bottom left hand box.
View 6 Replies
View Related
Apr 27, 2015
I've been asked to consolidate data from a number of different sources, rationalise and set up some access controls to restrict viewing/editing. The raw data is combination of personal data, cost codes and dates.
My company supply labour and materials to offshore facilities, where our staff work on a rotational basis. These swings typically run 2 weeks on/2 weeks off, but often can run shorter periods. We need to track who is offshore and at which facility, both to prevent double bookings and identify when we can book people in for training etc. This data also needs to form the basis for our timekeeping application.
I've created the base tables successfully, rationalising where possible, however I cannot for the life of me figure out how to replicate the current process of assigning people to their rotation.
The travel team currently use a matrix where the user info is broken down by trade and name in the left hand column, then the dates are displayed in a row across the top. The process of allocation is then simply colour code the dates where the person is booked on or off.
__________________|1 Feb|2 Feb|3 Feb|
Electrician__________|____|____|_____|
___Fred___________|BLUE|BLUE|_____|
___Tom___________|____|____|BLUE_|
Mechanic__________|____|____|_____|
___Dave___________|____|BLUE|BLUE_|
Is this possible in Access at all, or if not, is there a means of at least displaying the data like this?
View 3 Replies
View Related
Aug 17, 2014
In my DB there is a set of company-running rules that are addressed to different groups within the company, like drivers, bookkeeping, warehouse, electricians etc. Both groups and workers scope and number might change from time to time. I need to make a table that holds which worker belongs to which of these groups (one worker to one or more groups). The easiest way of setting this for the user would be kind of a matrix-look form where lines would hold the name of workers, columns would hold the groups and at the cross points there would be check boxes to set or unset membership. However I can't find the way in what table sturcture this could be utilized.
View 4 Replies
View Related
May 20, 2014
I have a form in which I am trying to build a matrix scoring system.I have two fields one called Offending Consequences and one called Offending Likelihood. The first field is limited to a list 1 to 5 and the second to a list A to E. What I want to do in a field called Offending Score is a kind of grid where:
1A scores 2 and is coloured green
up to
5E scores 10 and is coloured red
How do I get the Offending Score field to register the whole list of 25 possible combinations, 9 possible scores and 5 possible colours?My knowledge and understanding of access dbs is only moderate.
View 1 Replies
View Related
Nov 8, 2014
I am starting out creating a training database to track training needs and expiring training etc.I'm just looking for some tips as to how to structure the tables and relationships..I have an employee table, department table and training type table.However im wondering how i can set up requirments and then match these to check if the person is trained up to date on all required skills?
View 2 Replies
View Related
Feb 5, 2014
I am trying to get this data to display in a matrix format.How can I get it to display multiple entries in there, such as Fiona and Chloe in the FR box?
View 14 Replies
View Related
Nov 25, 2012
I am designing a little database to hold physical activity log info for 2 school districts, required by a grant. Four-five times each year every kid (2000) will complete a log form for 3 days that lists separately for 34 intervals (1/2 hour each, from 7am - midnight) in which physical activity s/he engaged (59 options) and the level of effort of each (4 options, from light to very hard).
The log sheets look like a datasheet with 5 blank columns: #1 enter the activity code (1-59) and #2-5 mark an "X" in the column describing level of effort (light to very hard). There are 34 rows, each labeled from 7am thru midnight in intervals of 30 minutes. The kids **have to** enter an activity code and a level of effort on every row. They do this for three days in a row, 4-5 times during the year. (believe it or not ... federal grant requirement)
.........................#1 .......... #2 .........#3 ........#4 ....... #5
TimePeriod ......ActivityCode .Light ....Moderate .Hard ...VeryHard
7-730........ | .................. | ....... | ............ | ...... |............. |
730-8........ | .................. | ....... | ............ | ...... |............. |
8-830........ | .................. | ....... | ............ | ...... |............. |
I am maintaining survey responses (also 4-5 times a year) in this same database. I have a basic demographic table (Id, name, grade level, school). I am also maintaining eating habits (4-5 times per year) from food logs, like the activity logs, but these are for 5-7 days at a time. Finally, I will be importing number of steps walked for 4-7 days (also 4-5 times each year) from pedometer downloads, one per kid.
I have all tables and data entry forms except for this activity log set up, all data entered for the baseline period. What I would like to do with the activity log is something like a main form showing kid name, ID. I think I want to enter each row of the log (34 rows) into something like a datasheet, each row with a date field (I will use CTRL-' to copy down for one page of the paper log), and an activity code field (1-59 as codes) and a level of effort field (1-4, light to very hard).
What I think I want is one table for activity info, each record with these fields:
Kid_ID
Date_Logged
Time_Interval (these are the 34 rows, 7am - midnight, in 1/2 hour intervals)
Activity_Code (options 1-59)
Level_of_Effort (options 1-4)
I can see this in a datasheet format, but entering 2000 kids * 3 pages * 4-5 times a year [* 3 years] into Excel does not seem to be an efficient solution.
If I could open a form on the screen with a combo box to pick a kid and then pick a date, then fill in 34 rows of activity codes (1-59 as options) with a level of effort for each (1-4 as options), that would be great.
My obstacle right now is how to display 34 empty rows already loaded with an index related to the time interval, so that I don't have to enter that for each row -
Hence my subject descriptor, "data entry in matrix format". I am using Access 2007, Windows XP Pro (all service packs etc. up to date).
View 2 Replies
View Related
Oct 6, 2012
if I got a 4 x 4 matrix table - 4 rows and 4 columns - MS Access 2007/2010 the values should exist as below with no repetition of any number in any of the cells.
1 2 3 4
5 6 7 8
9 10 11 12
13 14 15 16
a number should not repeat in any of the cells.I set a primary key on cloumn 1 and defined unique on all the fileds but that doesn't work since 1 columns does not know what the other cell contains and no relationship exists.
View 6 Replies
View Related
Aug 13, 2012
I'm trying to convert a matrix within a table (m x n) to a single column. So basically
1 2 3
4 5 6
7 8 9
to
1
2
3
4
5
6
7
8
9
I would do this in excel, but the problem is I have already over 1 million rows, and I'm converting 48 columns into one (too much for excel). Is there a way to do this via a macro or query?
The other catch is that I want to repeat Column A (which has the username) with each row of data, for example
Joe 1 2 3
Sam 4 5 6
Jeff 7 8 9
to
Joe 1
Joe 2
Joe 3
Sam 4
Sam 5
Sam 6
Jeff 7
Jeff 8
Jeff 9
View 4 Replies
View Related
Jul 30, 2014
I found the IDatomation Datamatrix barcode in the ActiveX controls.
Is buying their software the only way to actually get this barcode to work?
Any other way to create a 2D matrix that can be populated with data and scanned once printed?
View 12 Replies
View Related
Sep 5, 2013
I have wrote some code which calls a query using querydefs and then pulls all the records into a table. This table is then exported to excel, however I seem to be having some trouble with the export and am finding it difficult to identify where the problem is coming from. When I run my code in step by step, more of than not the code passes fine and exports to excel. However, most of the time when I run the code as a whole without step by step, then the excel application will load but the workbook will not. The application then continues to close and the code completes without error? The code for the opening of the excel file is below.
The pause is a function i created to see if it was a problem of giving the exel application some time to load!
Code:
Sub MiseEnForme1_Excel()
Dim AppExcel As Excel.Application
Dim WkbExcel As Excel.Workbook
Dim WksExcel As Excel.Worksheet
[code]....
View 12 Replies
View Related
May 29, 2013
What I would like to do it take all 'RoleTitle' from RolesList, all 'CourseID' from courselist and show them as a pivot table/matrix with a checkbox that creates or deletes entries from the RoleRequirements table. So, if it exists in the RoleRequirements table then it should appear a checked, unchecking it would delete it from the table.
I am using access 2010 with a SQL server 2005 backend.
View 3 Replies
View Related
May 6, 2015
I have a training matrix that lists employee names and certifications on various operations. The objective is to choose an operation and run a query to display everyone who is certified on that op. There are additional variables.
Code:
Name EMP ID OP1 OP2 OP3 OP4 OP5
-----------------------------------------------------------------------------
John Doe 526261 C C C
Bob Doe 555622 C C C
Sheila Doe 066600 C C C
Okay that looks about right for the data itself. The listbox has all the ops, you choose an op and hit a button and it goes and finds everyone who has a 'C' in that op column and pulls their record.
View 14 Replies
View Related
Oct 23, 2006
Hi all, this is my first post.
i have created a simple access database for keeping student and attendance record.
student table fileds are:
studentId - primary key
forname
surename
dob
gender
accademic year
attendance table fields:
studentid - primary key
date - primary key
attendance (yes/no boolean field)
paid (yes/no boolean filed)
this database is just ment to keep records of students attending at particualr dates.
for example if attendance table cotain records like:
http://www.crazyanime.pwp.blueyonder.co.uk/table.JPG
for the form layout, what i want to do is
http://www.crazyanime.pwp.blueyonder.co.uk/form.JPG
i want this to be editable. how would i do this using access form, or do i have to wrtite VBA code
PLUS i want the form to automatially have new records when i add for example a student, with ID 10011 OR if i add new records for a different date say 11/11/2006, then i want that to be viewd on the form just like the 21/09/2006 and the 04/11/2006.
please help :) been looking for a solution for long time.
thanks
View 4 Replies
View Related
Mar 9, 2014
I have 2 tables
- Staff Position(Unique Position Name, Description, Hierarchy)
- Training (ID, Name, Description etc.)
I essentially want a table with Staff Position as the Field, and Training as the Rows. The intersecting entries/matrix will be Yes/No to say whether that staff positions requires that training.
Simple Example
..................... Worker ........ Senior ........ Principal
IT Training ......YES ............. YES .............. YES
Accounts ......... NO ............. YES .............. YES
Management ... NO ............. NO ............... YES
I need the user to be able to add as many training entries and as many staff positions as they want. It doesn't HAVE to be that sort of format...
View 4 Replies
View Related
May 20, 2015
I am creating a database for creating quotations. The quotation number is generated using the date, for example the first quote today would be quote number "05202015-1" because it is the first one today. The next quote today would be quote number "05202015-2" and so on. Is there a way to make access automatically generate these quote numbers based on the date?
View 3 Replies
View Related
Nov 10, 2006
Hi, I've been trying to create an MDB file of my database with litle luck.
when I try and click the make MDE, the computer locks up and the processor goes into meltdown! I have to quit Access through TaskManager to bring it round.
I previously exported every element of the database one at a time to a new DB, and on each tested to see if the MDE would work. I identified 4 forms and 1 report which would prevent it making the MDE. I started the new database from there. Now I have re- created the forms and I get the same problem as before,
Does anybody have a clue what is going on or where i should look to resolve this , as I dont want to have to do all that exporting/testing again..????
View 3 Replies
View Related
Jul 15, 2005
I wondered if some can advise me as to how to make help files and linking them in to the dabase. i.e. if someone clicked on a certain section for help then it would go to the help for that section. Is there an easy program to help write the files??
View 3 Replies
View Related
Sep 6, 2005
I am trying to create an MDE file from my .mdb, but receive an error "Unable to create MDE file".
Can anyone suggest how I can find out why this cannot be created?
Thanks
View 1 Replies
View Related
Nov 6, 2005
Does anyone have sample code or a sample mdb that shows how to create an INI file and then also be able to import an INI file back in to a table in the mdb?
Thanks
View 1 Replies
View Related
Feb 12, 2006
Not sure if this is the right place to post - if not please move.
I am creating a database for my college project and I would like to create a log to record when people log in an create a record. This is for audit trails and for a system to present the user with any newly added records since their last log in.
I dont know if there is a really easy way to do this, so please post if there is, and dont ridicule me too much :P
I was thinking of having a table to store all the logs (one for each type of log - e.g: Log in and adding new records)
Taking logging in as an example, the user types in the username and pasword, when they click log in, the button will send the username and a time stamp to the table and add it into a new field.
Then when the user who has access to the logs wishes to view them, then run a query and select the date they wish to view, then all the records in the table will be displayed.
Please add any helpful comments to that theory, thanks :)
View 2 Replies
View Related
