Non-Values & Matrix Linking
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 Replies
ADVERTISEMENT
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
Apr 16, 2005
Hello,
I'm not familiar with Access very much and this is my situation:
I've got a table Routes with RouteID, Route, Route_price; a tabe Invoices with InvoiceID, Units, Route, Route_price, Total.
I've got a form, where I input Units, choose Route from the combo box.
Needed action:
I input Units, then choose Route, the corresponding Route_price should be chosen, shown and multiplicated with Units. The Total must be shown.
The Route, Route_price, Units, Total must be saved in table Invoices.
I don't know how:
pick Route_price according to selected Route and multiply it with entered Units and then show Total.
store all values in Invoice.
Will be gratefull for help
View 1 Replies
View Related
Aug 6, 2007
I have parts that go into and out of inventory. Each time a part is removed or returned, the user inputs the number into the computer. I was wondering how I could link the numbers so that as parts were removed or added, the total number in inventory and the total number on the floor automatically changed. Thanks so much.
View 1 Replies
View Related
Aug 28, 2006
I need to display a value from a table in a text field on a form based on another text field on the same form.
The table has records as follows:
Month/Year xx Month No xx Year No xx Month ID (xx shows break in columns)
Jan-06 xx 1 xx 2006 xx 24
Feb-06 xx 2 xx 2006 xx 25
Mar-06 xx 3 xx 2006 xx 26
Apr-06 xx 4 xx 2006 xx 27
May-06 xx 5 xx 2006 xx 28
Jun-06 xx 6 xx 2006 xx 29
Jul-06 xx 7 xx 2006 xx 30
In one text field I use the DateSerial function to calculate the value of the previous month (e.g.Jul-06). I need to return the MonthID value to the second text field based on the value stored against it is the table, so in case of Jul-06 it would show 30 in text field 2.
View 1 Replies
View Related
Sep 26, 2014
i have 2 tables that i am linking on a field that matches with null values on both tables but it either pulls nothing or when i put a right join pulls just one tables data.
i have also confirmed both are pulling from the same data source, so no issues with field properties and confirmed they are null values in each field within both tables.
View 6 Replies
View Related
Nov 11, 2005
OK - I'm a bit of a novice at access and the answer to this may be very simple but any help would be much appreciated.
I have a problem getting a Combo box field in a table to link to another table and input a relevant value in another field of the same table. Here is what I've got:
Table 1 - Client
Fields - CLIENT ID, CLIENT NAME
Table 2 - Client Rates (The rates charged to the client for 3 specific items that do not change)
Fields - CLIENT ID, SC, LX, SX. (These last three are the codes for the items and the values in the field are the currency values for the items as they are charged to the specific client)
Thanks
Table 3 - Job Details
Fields - JOB ID (Autonumber for the Job), CLIENT ID (Who the job is for), ITEM (This is a combo box that selects between the items i.e. SC, LX and SX), RATE (I want this to recognise the item chosen in the previous field and display the corresponding rate for that item and for that client - This is my problem!) :mad:
I will attach the database as I currnetly have it so that you can see what I'm talking about. As I say - any help is muchly appreciated
Thanks muchley
View 1 Replies
View Related
Sep 8, 2014
I have a combo box field called "investigations" on a patient database which can allow multiple values. Each of its values represent the different investigations a patient can do. Now those investigations are tables and forms in the database. how can i program the database to simultaneously open these forms "on update" of that field-investigations?
View 1 Replies
View Related
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
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 2 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
Feb 16, 2014
I'm pretty good with setting up a very simple database such as inventory, profiles, etc.. However I'm creating a database to keep track of a football (soccer) team's players and match statistics.What I have so farsample attached)
Tables:
* Players - PlayerID, Fname, Lname, position, goals, assists, etc (all details regarding a player)
* Position - Positons (Table containing positions eg: defender. Data is selected in player's form as a combo-box)
* Competition - Competition types (Cup, League, Friendly. Data is selected in Match's form as a combo-box
* Venue - similar to Competition table
* Opponent - Similar to above two tables
* Match - MatchID, Competition, Venue, etc (form corresponding to table attached)
Forms:
* Player form
* Match form
Now as shown in the sample, I choose players using the combo-box. Then whatever stats they had during the match are entered on the fields provided. How to link the player (selected using combo box) to the stat fields (goals, assist, YC, etc).
View 1 Replies
View Related
May 29, 2012
I am creating a customer database for an independent insurance agency. Within that customer database, we have a lookup field that references another table "Carriers". This is where we select the insurance carrier we have placed the clients insurance with (ex. Geico, progressive, etc). This field allows multiple entries since clients can have policies with different companies.
We also have contact detail forms for each of our insurance carriers where we store information like web logins, contact phone numbers, and other notes. These forms are based on the same table as the lookup field.
I would like to create a hyperlink so, if we are looking at a clients form with all of their contact info we can click the insurance company in the carrier field and it will bring us to the contact details form of that carrier.
View 7 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
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
