Queries :: Data To Display In Matrix Format
Feb 5, 2014I 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 Replies
ADVERTISEMENT
Tables :: Data Entry In Matrix Format
Nov 25, 2012I 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).
Tables :: Display And Populate In 2D Matrix
Apr 27, 2015I'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?
What Is Best Control To Display Data In Grid Format?
Jun 30, 2006I have a search page and would like to display the search results in a grid/table format. User will be able to select a row to go to a detail screen. What is the best component type to use for this?
View 1 Replies View RelatedMatrix Of Data
Oct 13, 2004I 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 RelatedTables :: How To Hold Matrix Data In A Table
Aug 17, 2014In 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 RelatedGeneral :: 2D Matrix That Can Be Populated With Data And Scanned Once Printed
Jul 30, 2014I 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?
Queries :: Pass Through Query Results To Matrix Which Is Then Exported To Excel
Sep 5, 2013I 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]....
Queries :: How To Retrieve Only Numeric Data From F1 And Display That Data In A Field
Oct 1, 2013Background I have a query (Q1) that retrives data from a table (Table 1). One of the fields in Table (F1) contains both text and numeric data (ie: 24 eggs). I want to separate these values in Q1.
Questions
How can i in Q1 retrive only numeric data from F1 and display that data i a field?
How can i in Q1 retrive only text from F1 and display that data i a field?
Queries :: Cannot Format Data From A Pass Through Query From SQL Server
Jan 10, 2014I get a result set from a pt qry from a sql server 2008 in vba. Me.Total is an unbound field in the report foot( correct translation of german "Berichtsfu"??)
Private Sub Berichtsfu�_Print(Cancel As Integer, PrintCount As Integer)
SELECT SUM((DATEPART(hour, Calctime) * 60 + DATEPART(minute, Calctime)) * Price / 60) AS TtlPrice ...
Set rs = CurrentDb.OpenRecordset("qryPT_TtlPrice")
Me.Total = rs("TtlPrice ")
On the access report I can format me.Total as currency, decimal, integer whatever I want (unless it makes no sense exept currency) and it works.
but
SUM(((DATEPART(hour, Calctime) * 60 + DATEPART(minute, Calctime)) * Price/ 60)* (case when CompanyID=1 then 1.66 else 1 end)) AS TtlPrice
The detail should be multiplied with 1,66 when the record belongs to company 1, for all others ist remains the same. In the Management Studio the results of both looks similar, same in the debug window of VBA editor, but I cannot format the result or lets say, the formating is wrong for the second example.
Neither in VBA
Me.Total = format(rs("TtlPrice "), " 0.000,00") or
Me.Total = format(rs("TtlPrice "),"0.000,00")
nor in the report int the properties of the field I can avoid this result: in the debug window of access the result looks OK, for example 29555.670000 (don't know where all the 0 come from), which should read as 29.555,67. But on the report the result with formated curreny in field property (currency, 2 decimals) shows 29.555.670.000,00
How To Display Data From 3 Queries In A Form?
Aug 10, 2005Hi all,
Wondering if anyone can help here? I am currently working on a form that needs to display data from 3 unique queries - a crosstab query and two standard queries (one has line by line data and another summary data) that do not have a common link.
What is the best way to do this? Would it be possible to display the results of each of these queries in a single form by creating 2 subforms from 2 of the queries and placing them in the form of the 3rd?
Any suggestions on how to combine the data from the 3 queries into the 1 form would be much appreciated!!!
Thanks in advance.
G.
Queries :: How To Get Query To Display 0 If There Is No Data
Jan 15, 2014I have a table where I record attendance. In the table they select the type of training in via a check box "Training, Drill or Other". I created a query to show how many times someone has attended the Drills in each quarter (counts the check box). I am wanting to be able to specify which year is displayed as atm it is showing all te quarters for years. The SQL is below.
From this I have a report where it displays the data and have conditional formatted it so that if they have not attended more than 4 times in one quarter the "cell" turns red - the only problem is is that some of the cells have no data in it. How can I get the query to display 0 if there is no data?
QUERY SQL:
TRANSFORM Sum(Abs([ATTENDANCE 2011].DRILL)) AS SumOfDRILL
SELECT [MEMBERS LIST].[NUMBER/NAME], Sum(Abs(Nz([ATTENDANCE 2011].DRILL,0))) AS [Total Of DRILL]
FROM [MEMBERS LIST] INNER JOIN [ATTENDANCE 2011] ON [MEMBERS LIST].ID = [ATTENDANCE 2011].ATTENDED.Value
GROUP BY [MEMBERS LIST].[NUMBER/NAME]
PIVOT "Qtr " & Format([DATE],"yyyy/q");
Queries :: Carriage Return - Format Data So It Will Export With 2 Decimals?
Nov 4, 2014When using the carriage return how do I format the data so it will export with 2 decimals?
In the example below I need the Estimated Material Cost to export in in format: 500.00 --- NOT 500.
MTLCost: [Estimated Material Cost] & Chr(13) & Chr(10) & [C]
Queries :: Display Data From 2 Different Tables In One Form
Apr 21, 2013I currently have two different excel spreadsheets linked to access 2003. The first few columns are very similar with similar headings. What i have now is a form displaying the data from one spread sheet (Engine Status), and another form displaying the data from the other (Engine Health).
As I am very new to access is there a way that i can link the two spreadsheets together and display all the data on one single form. I would like a combo box to find the required serial number (tail number) and once the serial number is selected the data from both spreadsheets will be displayed.
The spreadsheets i have to work with are of set standard for ease to input data from numerous other sources. I have attached examples of the two spreadsheets with the headings that are the same between the two although i have attached these to a single work book in reality they are seperate work books.
What i would like is for a combo box to display the Tail number then have single boxes to display core serial numbers across the form in order of position, then all the other data associated with that core serial number displayed in seperate boxes below the core serial number. I hope this is possible as it will prevent a lot of toggling between forms.
Queries :: 3 Tables Display Data From 1 Depending On Value In Another
May 12, 2015I'm building a database to record which books Pupils at my school have read.I have 3 Tables:
Pupils - ID, Forename, Surname, ID
Books - BookID, BookTitle, Author, Level, Genre
Read - ID, BookID, DateOut
I'd like to create a query that shows the BookTitle of the latest DateOut.
Queries :: Grouping Data By Week And Display
Mar 19, 2013I am trying to create a query that will group my data by each week so I can sum up some numbers and display them on a per-week basis... Prefer the week to start on Sunday but really not picky about it.
Using the following in the query design window.
Week of Year: Format([Date],"ww")
This is returning:
1
10
11
12
2
3
4
and so on.. See the problem?
I tried sorting but that doesn't work.
I also tried the following:
Format([Date],"mmmm,ww")
But this is returning
December,51
December,52
February,8
February,9
January,1
January,2
January,3
Yes, there are some weeks without data but that's not the problem. The issue is that its putting February before January.. Why is this? Again, I tried sorting options and several other techniques but no success.
Queries :: Link Table To Represent Data - Format Text As Date
Nov 15, 2013I have a link table that has a field that represents dates - but they are actually just text. It's a long story but the source is not going to change - so I have to try and deal with it. I need it to behave as a date - and am hoping to do this in a query. The data looks like this:
7/24/12
10/08/13
I have tried various things but it does not seem to totally do the trick - if I sort on it it still does not sort as one would expect from an actual date field. How do I do this?
Queries :: Display Empty Rows For Missing Data
Jul 28, 2014I'm trying to make a sub form that displays the hours of an employee selected in a listbox. I've got most of it working but having a bit of an issue.
The info for thre query is in 3 tables:
tblStaff (name etc)
tblShifts (start and end times for days that this employee works)
tblDays (a list of days names so I can use numbers elsewhere)
My query looks like this:
Code:
SELECT tblDays.dayName, IIf(Nz([startTime],"")="","NWD",[startTime]) AS start, IIf(Nz([endTime],"")="","NWD",[endTime]) AS [end], tblStaff.staffName
FROM tblDays LEFT JOIN (tblStaff RIGHT JOIN tblShifts ON tblStaff.staffPK = tblShifts.staffFK) ON tblDays.dayPK = tblShifts.workingDay
WHERE (((tblStaff.staffName)=[Forms]![frmMain]![lst_myTeam] Or (tblStaff.staffName) Is Null));
This worked fine with a single user and some test data - it correctly displayed all days of the week, with start/end times on Mon and Tues where I had entered shift information, and "NWD" against all other days.
However, when I add a couple more employees to the mix it shows the correct info for the first employee, but anybody else it will only display days where person 1 doesn't have any hours. I haven't entered any hours for the new employees, but the query should still display Mon-Sun with NWD in every column. It shows Wed-Sun but Mon and Tues are missing.
I've tried different join types but they all come back with "ambiguous joins" error when I try to run.
Queries :: Group And Display Data On Weekly Basis Starting On Sunday
Apr 2, 2013I'm trying to group and display data on a weekly basis starting on Sunday. I'm using the following in a totals query with "Group By" Week Number: DatePart("ww",[Date])
My problem is that the results are returning some unexpected things.. March seems to have 6 weeks and the query is returning two months containing week number 14.. (as shown below).
2013 March 9
2013 March 10
2013 March 11
2013 March 12
2013 March 13
2013 March 14
2013 April 14
Display Date Format
May 21, 2006Can someone help me?
is there a way about how to display date format inside on date field?
(but if user want to type the data then the date format automatically be deleted)
currently i just put the date format label below of the date field like this picture:
http://img62.imageshack.us/img62/4936/date6kq.jpg (http://imageshack.us)
please help me to solve it..
thank you:)
Date Format Display
Mar 6, 2008Can anyone quickly tell me how to display the date in a field in a form or report as
Year - Month - Day?
example 1999 - Feb - 03
Do I do this in the format function or should I use the input mask and... and how do I do this?
Format Textbox To Display Currency And Percentage
May 22, 2006Hi,
I have a query with 3 fields - PaymentAmount and PaymentTotal. The third field is a percentage of the two e.g:
PaymentAmount = 10000
PaymentTotal = 12000
PaymentPercentage = 83.333333333333
In my form I would like a text box to display the two combined i.e. £10.000.00 (83.33%).
At the moment it displays 10000 83.3333333333333. I can use £#,##0.00 to format the current half, but how can I then format the percentage to display 83.33%?
Thanks
Tables :: Display Input Field Format
Apr 5, 2013I have the following input mask for a certain field in an ACCESS 2010 table : 00-000-a-a-a;0;-
It works fine. That is when I tab down to it and if I know that the format is : xx-xxx-x-x-x
but if someone does not know the format , things can be out of position.how do I code the field such that the format (xx-xxx-x-x-x) is displayed in the input field awaiting the actual keying of the data?
Yes/No Feilds Display In Numeric Format When In Union Query
Jan 25, 2008Hi all,
I have looked everywhere for a solution to this and found none. Its an annoying issue which I would like to get around.
Here is what happens.
I have a union query that gathers rows with different criteria together. The query works execept that the yes/no feild which is set to display as "Yes" or "No" is displayes as 0 for No and -1 for Yes.
I understand that the numeric values are the underlying values of yes/no feilds in access, however they are displayed as yes/no in all my queries and reports execept my union query.
I will include the union query here in case.
The following code is a function that I use to build the union query on the fly based on some parameters. If you are asking why I am doing it like this is becasue I need the rows that have a value for the feild schFeilds(0) to be on top of the final results followed by the rows that don't have any values for the above mentioned feild, and I want them sorted. I found this method to be the only one that gives me the result in the order I need.
' ----------------------------------------------------------
Public Function makeMainPanelQry()
On Error GoTo Err_makeMainPanelQry
Dim db As Database
Dim qd_window As QueryDef
Dim qd_nowindow As QueryDef
Dim strSQL As String, sql_window As String, sql_nowindow As String
Set db = CurrentDb
Set qd_window = db.QueryDefs("Q1")
Set qd_nowindow = db.QueryDefs("Q2")
strStatus = cmbStatus.Value
strReboot = cmbARStatus.Value
strFilter = makeHostFilter(strStatus, strReboot)
schFeilds = Split(getRSchechuleFeild(), ",")
sql_window = "SELECT " & pbl_ReleaseTable & ".Hostname, " & pbl_ReleaseTable & ".Status, " & pbl_ReleaseTable & ".Excluded, " & _
pbl_ReleaseTable & ".AutoReboot as Auto, " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & ", " & _
HEAT_PROFILE_NODECOM & "." & schFeilds(1) & _
", Technician.FirstName as Assigned, 1 as Priority FROM (" & pbl_ReleaseTable & " LEFT JOIN " & HEAT_PROFILE_NODECOM & " ON " & _
pbl_ReleaseTable & ".Hostname = " & HEAT_PROFILE_NODECOM & ".DeviceName) " & _
"LEFT JOIN Technician ON " & pbl_ReleaseTable & ".TechID = Technician.TechID " & _
"WHERE ((" & pbl_ReleaseTable & ".Excluded) = False) " & _
strFilter & "and " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & " Not Like """" "
'" ORDER BY " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & ", " & pbl_ReleaseTable & ".Status"
qd_window.sql = sql_window
sql_nowindow = "SELECT " & pbl_ReleaseTable & ".Hostname, " & pbl_ReleaseTable & ".Status, " & pbl_ReleaseTable & ".Excluded, " & _
pbl_ReleaseTable & ".AutoReboot as Auto, " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & ", " & _
HEAT_PROFILE_NODECOM & "." & schFeilds(1) & _
", Technician.FirstName as Assigned, 2 as Priority FROM (" & pbl_ReleaseTable & " LEFT JOIN " & HEAT_PROFILE_NODECOM & " ON " & _
pbl_ReleaseTable & ".Hostname = " & HEAT_PROFILE_NODECOM & ".DeviceName) " & _
"LEFT JOIN Technician ON " & pbl_ReleaseTable & ".TechID = Technician.TechID " & _
"WHERE ((" & pbl_ReleaseTable & ".Excluded) = False)" & _
strFilter & "and " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & " Like """" or " & _
HEAT_PROFILE_NODECOM & "." & schFeilds(0) & " is NULL"
'" ORDER BY Priority, " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & _
'", " & HEAT_PROFILE_NODECOM & ".Status;"
qd_nowindow.sql = sql_nowindow
strSQL = "SELECT * FROM Q1 " & _
"UNION ALL SELECT * FROM Q2 " & _
"order by Priority, " & schFeilds(0)
Set qd_window = Nothing
Set qd_nowindow = Nothing
Set db = Nothing
makeMainPanelQry = strSQL
Exit_makeMainPanelQry:
Exit Function
Err_makeMainPanelQry:
MsgBox Err.Description
Resume Exit_makeMainPanelQry
End Function
' ----------------------------------------------------------
I woudl really appreciate any assitance as its my last hope for a solution.
Thanks and regards
Sep
Table With Multiple Like Fields That Need To Display Into Array-like Format
Jan 8, 2013I have a table that contains the following;Company name, PositionID, Firstname1, Lastname1, title1, gender1, Firstname2, Lastname2, title2, gender2, Firstname3, Lastname3, title3, gender3 [...up to 50 names]
The data is uploaded to my table from a 3rd party database source via .csv file.I can capture up to 50 names, titles and gender per record. I would like to display this data in a table/array like format. I'm not too good at VBA but I can use expressions for unbound fields.I have attached a file that shows the data in a format I would like to see in Access displayed in a form.
Apparel Matrix
May 1, 2006I'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.