See my program: test.mdb
How to categorize same type in report? I mean I have two "prt1" in form, now in report show it like this:
type
weight
meter
prt1
4 | 2
2 | 1
prt2
3
3
prt3
5
10
I have a requirement to categorize a field in the employee database.
The requirement is: for any given user designation, if the total number is 10 or more, I need the actual designation itself as the output. However, if a particular designation in the organization is less than 10 in total, the output should be 'Misc - and the grade'.
Also, this will need to be done for each grade as well. For example, though the staff designation 'Software Engineer' count is 15, the designation is present across two grades A and B. So, in the output, for the staff with Grade A will show the actual designation as the count is 11, and for Grade B staff with the desgination 'Software Engineer', since the count is 4, it should display the output as 'Misc - Grade B'.
I would like to achieve this in MS Access 2010.
I have a repairs db that records [serial number] and [date in for repair], and other info.
I've been asked to make a report to summarize the number of units [serial number] that come back for repair by time interval between [ship date] and the first [date in for repair].
the [serial number] and [ship date] are in a linked excel file table which contains unique [serial number] entries.
The [serial number] and [date in for repair] are in the access table, there may be multiple [serial number]s entered with different [date in for repair] as in repaired multiple times.
1st query: I want to list all the [serial numbers] in the access table with only the earliest [date in for repair] returned.
2nd query: then I need to compare the 1st query results list of [serial numbers] and [date in for repair]to the excel table list of [serial numbers] and [ship dates],
I've found the =DateDiff('d'[ship date],[date in for repair])
I haven't done a query with two data sources before and not sure how to go about getting the DateDiff for each serial number
Once I get results from the DateDiff, for each serial number, I am comfortable handling the Sum(IIf statements to summarize the results and do the serial number range select criteria in the form.
I have two tables.
The ITEM RECD table containing the fields -- recd_invoice_ no, invoice_date,part_no,qty.
The ITEM ISSUE table containing fields- issue_no, date, part_no,qty.
The two tables are related by the part_no field.
How do I print a stock book type report for each item individually with all the recd item details on left side and the issued item details on the right side? All the recd & issue details for all dates need to be there in the report.
Someone please help!!!!!
I've got a table that has several yes/no data type fields. I'm trying to the data into a report and show either yes or no as opposed to -1 or 0.
View 3 Replies View RelatedI have a report in access that currently works like the image Current Report.png.
I was hoping it is possible to make it look like the image in What I Want.png
More a spreadsheet type report that I can then print out.
This code runs to the set frm part then i get a type mismatch? ive tried a few different things and still nothing?
Code:
Private Sub Report_Open(Cancel As Integer)
' Create underlying recordset for report using criteria entered in
' EmployeeSalesDialogBox form.
Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form
' Set database variable to current database.
Set dbsReport = CurrentDb
[code]....
I have a DB with Start Date (dd.mm.yyyy), End Date (dd.mm.yyyy) and Quarters. We have 4 Quarters (Jan-March, April-June, July-Sep, Oct-Dec) and extra columns of month (xxx) and year (yyyy) too.
The problem is the front end users have access permissions to modify the dates. if they change the start date and end date then they are manually gonna change other fields like quarter, month and year too (Which they don't want). The users don't need to manually update the column values for Quarter, Month and Year
For Example,
If the user modifies Start Date to: 22.05.2014 and End Date to: 24.06.2014 (then the quarter column should be fixed to 2nd quarter and month should be June and year should be 2014).
2nd Example: We need to consider the End Date for classifying the quarters, Month and Year
If the Start Date is: 22.05.2014 and End Date is: 24.12.2014 (then the quarter column should be fixed to 4th quarter and month should be December and year should be 2014).
The solution is when ever the front end user modifies the dates then automatically the quarter, month and year columns need to be changed.
Sample piece of Access data sheet with just 3 examples. Column names Start date, End date, Quarter, Month, Year.
I have a DB with Start Date (dd.mm.yyyy), End Date (dd.mm.yyyy) and Quarters. We have 4 Quarters (Jan-March, April-June, July-Sep, Oct-Dec) and extra columns of month (xxx) and year (yyyy) too.
The problem is the front end users have access permissions to modify the dates. if they change the start date and end date then they are manually gonna change other fields like quarter, month and year too (Which they don't want). The users don't need to manually update the column values for Quarter, Month and Year
For Example,
If the user modifies Start Date to: 22.05.2014 and End Date to: 24.06.2014 (then the quarter column should be fixed to 2nd quarter and month should be June and year should be 2014).
2nd Example: We need to consider the End Date for classifying the quarters, Month and Year
If the Start Date is: 22.05.2014 and End Date is: 24.12.2014 (then the quarter column should be fixed to 4th quarter and month should be December and year should be 2014).
The solution is when ever the front end user modifies the dates then automatically the quarter, month and year columns need to be changed.
Need sample piece of Access data sheet with just 3 examples. Column names Start date, End date, Quarter, Month, Year.
I will see the logic and will sort it out.
The database is to track and evaluate call center associate phone calls. I created yes/no fields for multiple common errors (accurate/complete, Security, and client experience. and under each are @ 20 common errors)
(the problem is that I did not create a seperate table for each main category which I think might have made things easier)
Now what I am trying to do is create a report that allows me to show (or select) an error and have it show all the associates that had that error in a given time frame.
I already have a report to show all associates and all the errors that each had in a certain time frame. (so vertically I have associate detail and horizontal I have error detail).
is there a way, without creating 25 separate reports, to show all associates under an error type?
I want to be able to type a nickname for a contact and it populate the full name in a query for a report. Is there a way to effectively get this done? I have tried combo box and I am not sure how to use the drop down list function.
View 9 Replies View Relatedhi friends,
i have tried had to connect sub type tabels (Saving, Checking, Loan... they have their own ids...) with super type (Account...it has account id...) on the condition of account_type (either "S","C" or "L") attribute in ACCOUNT entity.
how to joint them??? with query or with expression??
i expect help from you.........please.
........thanks.
I have a table with a field with names set to text data type and i want to change it to number data type but when i do it in design view the data get lost. I want to know if there is a way to convert the data in the field as number type and keep the data in the field.
View 7 Replies View RelatedDoes anyone know what a QueryDef Type of 3 is?
If I run the following code, as well as returning all queries in the db, it also returns a few that are of Type=3.
For Each qd In db.QueryDefs
Debug.Print qd.Name & " - " & qd.Type
Next
For the Type 3 ones, the qd.Name returns something like
~sq_rREPORTNAME
where REPORTNAME is the name of a report in the database .
However, it's only a couple of reports that appear this way.
Where or what is a Type 3 - even if I view Hidden & System objects I can't see them!
Hi
I posted this on the end of the audit trail thread but nobody was really looking at it. I have used the script for the audit trail, and when I start changing any of the records, it comes up with the error:
13 - Type Mismatch
When I ctrl - break, and debug, it highlights the last line of the script:
On Error GoTo Form_BeforeUpdate_Err
Call Audit_Trail(Me)
Form_BeforeUpdate_Exit:
Exit Sub
Form_BeforeUpdate_Err:
MsgBox Err.Number & " - " & Err.Description
Resume Form_BeforeUpdate_Exit
Anyone any ideas on what is happening?
Hi all,
I have a problem with the following piece of code:
Private Sub cmdCautare_Click()
Dim strSQL As String, strOrder As String, strWhere As String
'Select Case Me.cmbTipVersus
'Case 1
'If Me.cmbTipVersus = "" Then
strSQL = "SELECT tblDosare.DosarID, tblDosare.DenumireDosar, tblDosare.CodDosar, tblDosare.DataDosar, tblInstante.Localitate, tblStadiu.Data, tblStadiu.Stadiu FROM tblInstante INNER JOIN (tblDosare LEFT JOIN tblStadiu ON tblDosare.DosarID = tblStadiu.Dosar) ON tblInstante.InstantaID = tblDosare.Instanta"
strWhere = "WHERE"
strOrder = "ORDER BY DosarID"
If IsNull(Me.txtDenumire) Or Me.txtDenumire = "" Then
Else
strWhere = strWhere & "(DenumireDosar) Like '*" & Me.txtDenumire & "*'"
End If
If IsNull(Me.cmbStadiu) Or Me.cmbStadiu = "" Then
Else
strWhere = strWhere & "(DenumireDosar) Like '" & Me.txtDenumire & "*' & " And " & (Stadiu) Like '" & Me.cmbStadiu & "*'"
End If
DoCmd.OpenForm "frmRezultateCautare", acNormal
Forms!frmRezultateCautare!lstRezultate.RowSource = strSQL & " " & strWhere & "" & strOrder
'End If
'End Select
End Sub
And on this line strWhere = strWhere & "(DenumireDosar) Like '" & Me.txtDenumire & "*' & " And " & (Stadiu) Like '" & Me.cmbStadiu & "*'"
i have an error message type mismatch. If i do the search only with the first text box the code works hjust fine but if i make the search w.r. with the second control (combo box cmbStadiu) the error appears. If anyone can help i will appreciate it.
Thank you all!
When exporting a table to excel is there anyway to presever the data structure - ie a text field of length 30 will only take text length 30, Interger would only take integer and date would only take date etc, etc ,etc.
View 1 Replies View RelatedSome fool has chosen number for a date column and the date data show up like
8031976
12022001
If I just change the data type back to data, it's going to delete a lot of them.
Is there a way to make the change and preserve my data?
Thanks in advance.
Hi,
Trying to get back into Access after a long time, just need some refreshing :) tips.
I have 2 tables, one of the tables stores titles (mr, mrs, etc). I tried using the 'lookup' data type to link the data from the other table. This however stores the field as a number and causes problems on my form as only a number is shown rather that the text.
What do I need to do to resolve this? :confused:
Thank you.
Hey guys-
I have a db where I'm not sure what kind of relationship I need to establish between the tables. I have a db that manages real estate properties and realtor information. Each record can have 3 separate Realtors represented (or, as little as 1 realtor). Each Realtor has their own office, or, might have the same office as the other realtors. So, one record can have anywhere from 1 to 3 Realtors associated with it.
I have a Property Table (holds all the info on the property as well as the Realtor's unique alphnumerica ID Code).
I have a Realtor Table (holds all the contact info for the Realtor, as well as their unique alphanumeric Office Code)
And I have a Realtor Office Table (holds the contact info of the Realtor's Office).
In the Properties table- I have 3 fields for the 3 separate Realtor's ID Codes (like an employee number). Let's call it Agent1Code, Agent2Code, and Agent3Code. There won't always be an Agent2 and Agent 3 in the data- but sometimes there is.
How do I set this up and establish the proper relationships?
I have a table products
product id - autonum, key
ref - txt
serial - txt
typeId - num (gets type from types table)
and a table types
typeId - autonum, key
type - txt
I want a query to allow my form in a choicelist to pick one of the types and display all available products on that type.
I got :
SELECT types.TypeId, types.Type, products.Reference
FROM types, products
WHERE (((types.TypeId)=[products].[typeid]));
But it doesn't give me that result when I make the form based on that query.
I have a field defined with a Yes/No type. When it appears on the form instead of showing up with a Box with a checkmark, it shows up as -1 or 0. Any thoughts as to what could be the problem ?
Thanks.
I'm not really looking for code, just a clue as to what to read up on as I have been beating my head against the wall with this one. I can do it in about three steps but I am trying to do it in one. :confused:
The first table1 is like this:
id Field1 Field2 Field3
1 TagNo44 Y Yes
2 TagNo5 C No
3 TagNo127 Q Maybe
4 TagNo4 T Perhaps
5 TagNo88 Z Probably Not
6 TagNo17 P Could be
The second table2 is like this:
id Field1 Field2 Field3
1 TagNo44 Outfit6 30
2 TagNo44 Outfit14 70
3 TagNo127 Outfit14 100
4 TagNo4 Outfit3 100
5 TagNo88 Outfit3 50
6 TagNo88 Outfit8 50
7 TagNo5 Outfit7 100
8 TagNo17 Outfit92 100
Output table to be like this
id Field1 Field2
4 TagNo4 Outfit3&100
2 TagNo5 Outfit7&100
6 TagNo17 Outfit92&100
1 TagNo44 Outfit14&70&Outfit6&30
5 TagNo88 Outfit3&50&Outfit8&50
3 TagNo127 Outfit14&100
Table1Field1 is in a one to many relationship to table2.field1
All fields are strings.
I have two text boxes - on different forms. I can copy the text from one and paste it into the other one ok. However, if I cut (Ctrl-X) from the first text box the second one is not enabled and I cannot do anything in it. Hope you can help.:confused:
View 5 Replies View RelatedI am persistently getting the "Error Number:13; Type Mismatch" with the code below.
Any help is greatly appreciated...
BTW, in the SQL string, i will like to have the wildcard as "*" but the VB Editor automatically changes it to " * "... can that be a problem??
Code:
Dim db As DAO.database
Dim qdf As DAO.QueryDef
Dim sqlsearch As String
Set db = CurrentDb
If Not QueryExists("Q_Paint") Then
Set qdf = db.CreateQueryDef("Q_Paint")
Else
Set qdf = db.QueryDefs("Q_Paint")
End If
sqlsearch = "SELECT M_Paint.* From M_Paint " & _
"WHERE M_Paint.Color_Number = '" * " & "Me.txb_pcn_colornumber" & " * "';"
qdf.SQL = sqlsearch
DoCmd.OpenForm "F_Display_Paint"
DoCmd.Close acForm, "F_Paint_Catcodesearch"
DoCmd.Close acForm, "F_Search_Paint"
If I have a table with day numbers (1 - 20) and there is a corresponding score for each day between 1 and 5. Is there a way of working out on what day (1-20) the score has been above 3 for the three previous days.
Generally if I get pointed in the right direction I can nut out the code (but not always!)