I have a small database that tracks staff training. Main tables are:
tb_staff
tb_courses
tb_courses_done
1 staff can take many courses. I want to introduce booking - staff can book a course but not necessarily attend. Can I just add a table, say tb_course_booked and use an update query to add to tb_courses_done as needed? Would that work?
I have a report which is bound to a query.The body of the report comes from the query.I also have additional data that is displayed on the form that is currently 'hard' coded into an a label.
I would call this data reference data. It's not specific to a client so there is no logical place to store it other than a reference table which will have no links to other tables. ie it's not truly relational. can I get data from this reference table in the same way I could do on a form by using a combo box and having a SELECT statement?
i want to add list of 2015 courses and have a seperate tab for 2015 courses(just like 2012,2013,2014).All the courses are setup as queries, which is why i can not seem to add to them.If you go in 2014, 2013 tab you will see list of coures for those years, i just want exactly same thing for 2015. All the courses are same, juwst their name is slightly diff.
I need to create a report with multiple subforms. I set up my first report and then I believe I need to add "more detail" sections or another page to this report so i can drag in my additional forms.
How do i separate out sub forms? Ideally I will want to have the main table on the top then the sub form in the middle, then the next on the bottom of the page.
I am using Access 2010 on a Windows 7-64bit machine. I am trying to begin an employee database using the faculty template. It would be exceptionally useful if the "Add from Outlook" command that is built into the "Faculty list" form would fill more than just the name, email address, and phone number fields. There are fields on the form for Department which match available info from the Outlook address book, but isn't imported.How can I tweak the command to fill the additional fields?
I have a db with three linked (related) tables. I have a form that has, at present, only one of these tables as a data source. Now I want to bring a couple of fields from another table into that form.
I know how to link a table to two data sources when first creating a table. But how do I do it when the table already exists?
I tried Table Design View | View Properties | Data source, but that allows only one data source. If I link to another, the first disappears.
I have a db with three linked (related) tables. I have a form that has, at present, only one of these tables as a data source. Now I want to bring a couple of fields from another table into that form.
I know how to link a table to two data sources when first creating a table. But how do I do it when the table already exists?
I tried Table Design View | View Properties | Data source, but that allows only one data source. If I link to another, the first disappears.
Hi! Solution is maybe simple, but I don't know it! When I put some value in a control (textbox) on a form, and after that if I close a form (by x button) that recordset is added to a table, but I don't want to do that- I just want to exit (close) the form! How to avoid adding that recordset to a table? Same things happens when I, by VBA, set focus to control, assign some value to it, and just want to exit the form.
I'm creating a database for a travel agency. There are 2 tables, one for customers and one for their bookings. I have established a relationship between the tables so the AutoNumber for customer ID links to a field on the add booking table.
In practice though, it would be easier to enter the customer NAME into the booking table and have it retrieve their ID that way to link them. This seems like I am missing something simple but can't fathom a way to do it.
The other factor is obviously more than one customer will have the same name so I may need to link first name as well to differentiate?
I have a form where user can chose his own criteria for the report, like time period, ppl involved and other parameters. i open report filtered with these conditions. I also create a nice text (as public variable) bout what where the search criteria and put it in the header/footer. Then I export the report as xls file. it's nicely saved as column names and data, just what i need.how to add a field that would store the criteria, ie that string public variable that has it all neatly explained?
What I am trying to create is a Despatch database for our warehouse.
Records in table: Date () Customer Invoice # Qty of parcels sent Courier used consignment number
In most cases we will send one invoice per consignment number (database works fine for this) But on occasion we may send multiple invoices. What I want to be able to do is have a list box to select the number of invoices, this will make available additional fields for Invoice # and Qty of parcels sent. The idea is to get away from keying in the other records for each invoice going to the same place.
I'm using Allen Browne's search form in an application for demographic purposes.
The form is working fine, but the user is asking for a new functionality. He wants at lease 3 state combo boxes so he can filter that [State] field for at lease three choices and the way it is build now he can only have one. I tried the following code (with the corresponding text boxes in the form), but it didn't, the result was no search at all.
Code:
If Not IsNull(Me.txtState01) Then strWhere = strWhere & "([State] Like ""*" & Me.txtState01 & "*"") AND " End If If Not IsNull(Me.txtState02) Then strWhere = strWhere & "([State] Like ""*" & Me.txtState02 & "*"") AND " End If If Not IsNull(Me.txtState03) Then strWhere = strWhere & "([State] Like ""*" & Me.txtState03 & "*"") AND " End If
In the Allen Browne's documentation he explains about using the AND and OR's and I tried a few combinations, but the result this time was no record found at all.
I am having a problem with the code in the Close event on a report when asking the user if they want to view additional reports after closing the current report in preview. Report Closes after the Yes/No answer is selected. If yes selected it does nothing. If No is selected it does nothing, This code works fine in a data entry form after changing the close and open commands. Using Access 2010
Code Using Now.
Private Sub Report_Close() Dim IntAnswer As Integer IntAnswer = MsgBox("Would you like to View/Print additional Reports?", vbQuestion + vbYesNo, "Yes") If IntAnswer = vbYes Then
The main table for the database I am working on contains the following fields:
ID Mfr Control Number Initial or Follow-Up Follow-up Number Suspect Date of Initial Email Date Received Date Submitted Date of Report Serious Brief Description Causality Notes
With some additional qualifications I wanted to find records that had an intial report but no follow-up. Which translates too I want records that are unique in the Mfr Control Number field (no duplicates).
I am trying to build a query and keep getting hung up on the unique aspect of fields. I started by trying to query only "Mfr Control Number" fields that are unique (no duplicates). As best I can figure for some reason I can not add any additional fields to that query. My current query is set up in the query build table such that I have added "Mfr Control Number" in two columns. The first column in Total I have "Group By". In the next column I have set Total to "count" with a criteria of 1. If I try to add any other fields from my table than I seem to lose those unique results. But I need to further filter to get the exact information I need.
I want criteria on the "Initial or Follow-up" field to only bring "initial" I want criteria on the "Serious" field to only bring "serious" I want criteria on the "Date of This Report" field of "<Date()-"15""
Is there some way to take the results of that initial query to then build a another query based just on those records? I could then apply the further criteria and run my report. Or is there a way to do this in one step?
I've tried to make clear my intention but know it can be difficult to get this kind of stuff down in writing in a clear fashion. I have to be careful to keep information confidential also so some of the details are vague.
I have a form where we fill in information for supply of PPE to employees.
Each item must be signed for on a printed report.
I am encountering problems trying to create enough rows in my report detail for each signature of the number of items supplied.
For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.
The Query gives the results below: (Item name is linked to a PPE table containing Item ID, Item Name, Cost etc..)
IssueIDDateProvidedItem_Name AmountIssued 0001 01/11/2013 Gloves (Orange) 10 0001 01/11/2013 Hard Hats (Black) 2 0002 02/11/2013 Hi Viz (Large) 5
The report I aim to generate from this should look as attached ...
The query/report is set up as a parameter report so I will enter the issueID such as 0001 and only those items will appear on the report to print and sign.
I am trying to return the MIN in a numeric field (single result) from my database and I have no issues doing this BUT the problem is that I also need to display additional fields in order to make the result useful. The additional field can't be grouped by b/c that changes the result of the MIN function.
I created an instructor form with a sub form table on the bottom - [pic at the bottom] I would like to add columns in the subform from the instructor_info subform but it should not be editable, just display it as per the column relationship key, and the relationship key for the 2 tables (instructor_info & course_table) is course_id.
Course Table - Course Date (instead of Course)
- Course Location - Station Demo - No. of Teachings - Hours Taught
Instructor Teaching Info Table - Instructor ID - Alone - Course - Course Date - No. of Teachings - Course Type - Notes
I need to add an additional filter to my unbound form and can't seem to get the syntax right -
I have a text field in my form "[B Style]" that has either a "Y" or "N"
I want to add a checkbox on my main form where, if checkbox=True then [B Style]="Y"
Private Sub ckBStyle_Click() Dim stFilter As String Dim stDocName As String stDocName = "Modify_OpenItems" If Me.ckBStyle.Value = True Then DoCmd.OpenForm stDocName, , , ("[B Style]" = "Y") Else: DoCmd.OpenForm stDocName End If End Sub
I need to add two additional columns for each imported spreadsheet, which are not in the original files.
First column, named 'Customer Name', needs to retrieve information from cell A6 in Excel file, which is not part of range "name" that is imported to Access. If necessary, I could somehow include it in this range but then I still would have a problem of how to repeat a value of A6 in each row of table in Access.
Second column, named "File Name", I would like to include an Excel file name from which data was imported. Again, I would like to repeat value for each row in the table, i.e. if I have 6 rows with data from file X, I would like to repeat X.xlsx in each row in column "File Name".
Code: Dim strFile As String DoCmd.SetWarnings False DoCmd.RunSQL "DELETE * FROM Data;" ' Set file directory for files to be imported strPath = "C: est"
I've got a report, which on the screen is three chrts and a page break in between. Page is set up to print landscape.
When I print the report or save to pdf it adds a lot of pages in between 2 and 3. It seems to be of chart no.2 getting smaller and smaller. I've attached a copy of the pdf so you can see what I mean.
I am trying to use transferspreadsheet to import access worksheet and then I want to append additional fields. I am able to import the excel sheet into access, but need appending the other fields.
Code:
Dim fd2 As FileDialog Dim xlapp As New Excel.Application Dim xlsht As Excel.Worksheet Dim xlWrkBk As Excel.Workbook Dim db As DAO.Database Dim tb2 As TableDef Dim fdx, fld As DAO.Field
The code pasted below creates a union query for a set of tables (J000171, J000174, J000178 etc) and stores the results of the query in a table called temp.
The first piece of code queries the ‘status’ field of a table rjobs for those records with a ‘status’ field of “Live”. Another field within this rjobs table, ‘JobID’, happens to be the name of a table where additional information relating to that job record is held eg. J000178 All of the tables selected in the query on rjobs are then included in the union query.
The second piece of code stores this information in a table called temp
I would like to be able to do 2 things with this;
1.add an additional field to the union query which holds the JobID field value from rjobs (or alternatively the table name from which the data originates eg J000178 etc as that is the same as the JobID file din rjobs)
2.create an option to clear the info in the temp table. Currently additional info is appended, so whenever the query is refreshed new data is simply added to old data. I would like to be able to clear that data where possible.
The union query is run from the on click of a command button on a simple form. Perhaps an additional button could be used to clear the records from the table temp.
Any ideas greatly appreciated.
Here is the existing code …
Option Compare Database Option Explicit
Private Sub Command0_Click() Dim db As Database Dim rsRjobs As Recordset Dim rsRapps As Recordset Dim LengthofUnionSQL As Long Dim sql As String Dim UnionSQL As String Set db = CurrentDb Set rsRjobs = db.OpenRecordset("Select * from rjobs where Status = 'Live'", dbOpenSnapshot) Do While Not rsRjobs.EOF UnionSQL = UnionSQL & "Select ObjectID, SearchNo, DateSearched, Consultant, from " & rsRjobs!jobID & " Union " rsRjobs.MoveNext Loop 'following two lines are to remove the trailing word Union from the string unionsql LengthofUnionSQL = Len(UnionSQL) UnionSQL = Mid(UnionSQL, 1, LengthofUnionSQL - 7) ' Now variable Unionsql will hold the value something like ' Select ObjectID, SearchNo, DateSearched, Consultant from J000145 ' Union Select ObjectID, SearchNo, DateSearched, Consultant from J000146 ' Union Select ObjectID, SearchNo, DateSearched, Consultant from J000147 MsgBox UnionSQL
Set db = CurrentDb Dim rsUnionquery As Recordset Dim rstemp As Recordset Set rstemp = db.OpenRecordset("temp", dbOpenDynaset, dbSeeChanges) Set rsUnionquery = db.OpenRecordset(UnionSQL) Do While Not rsUnionquery.EOF rstemp.AddNew rstemp!ObjectID = rsUnionquery!ObjectID rstemp!SearchNo = rsUnionquery!SearchNo rstemp!DateSearched = rsUnionquery!DateSearched rstemp!Consultant = rsUnionquery!Consultant rstemp!jobID = rsUnionquery!jobID