I'm doing a table for Future Business Leaders of America competitive events assignments.
I want the same table to hold Name and Event Name and be able to sort
However, I want the database to be able to sort to tell me each event that a person has, and at the same time, how many people are in each event. Presently, I have to switch between databases.
On one table, the event name is the primary key, and on the other the person's name is the primary key.
For the event name table, categories are "Event Name" , "Participant 1" , "Participant 2", "participant 3", etc.
For the Person's name table, Categories are "Name", "event 1" , "event 2", "event 3", etc.
I want to have ONE database that will sort lists for all participants in each event AND that will sort into a report or form so that I can inform participants of their events.
How can I do this?
I probably was not very clear, so ask lots of questions if you're unsure.
ok, i'm not the strongest SQL programmer, so i can't figure this out. i'm working on a report with the following source code:
SELECT vio_access_mid.safety_survey_date, vio_access_mid.violation, vio_access_mid.comments, vio_chemical_mid.violation AS chem_vio, vio_chemical_mid.comments AS chem_comm, vio_chemical_mid.safety_survey_date AS chem_date
FROM vio_access_mid INNER JOIN vio_chemical_mid ON vio_access_mid.lab_id = dbo.vio_chemical_mid.lab_id
WHERE EXISTS (SELECT ehs_labs.lab_id, tbl_list_building.building_desc, tbl_list_department.department_desc, ehs_labs.lab_room, ehs_list_lab_type.lab_type_desc, ehs_princ_inv.princ_inv_fname, ehs_princ_inv.princ_inv_lname FROM ehs_labs INNER JOIN tbl_list_building ON ehs_labs.building_id = tbl_list_building.building_id INNER JOIN tbl_list_department ON ehs_labs.department_id = tbl_list_department.department_id INNER JOIN ehs_list_lab_type ON ehs_labs.lab_type_id = ehs_list_lab_type.lab_type_id INNER JOIN ehs_princ_inv ON ehs_labs.lab_id = ehs_princ_inv.lab_id WHERE (ehs_labs.building_id = '0146') AND (ehs_labs.lab_room = '5678'))
AND (vio_access_mid.safety_survey_date = '6/1/2005') AND (vio_chemical_mid.safety_survey_date = '6/1/2005')
when i run the code, i get the correct amount of records (3) from vio_access_mid, but i also get 3 from vio_chemical_mid when in reality there are only 2 records. i will eventually add other vio_*_mid tables that will have a variety of results (some may have 0 while others might have 4 or 5, etc).
i would eventually like to be able to include the info from the WHERE EXISTS clause in my results, but one thing at a time...
anyone have suggestions on how to get the correct info from each vio_*_mid table?
I am writing a basic database to track performance for a stationery company.
Basically the database is used to track how long it takes certain depts to complete a task, so each dept ticks a box when they pass the task to the next dept and this populates a field with the date and time that they tick the box using the expression =IIf([Passed to Collate]="-1",Now()," ")
My problem is the box that you place the expression is also the box that you would select which column of the table the result would be sent to and I'm not sure how else you would run querys to establish total time and average time for the task.
I'm sure I'm missing something easy as I can't see why you wouldn't be able to save the result of an expression to the table and use it in querys/reports etc.
I'm sure this has been asked before, so I'm looking for search hints:
I would like to select query results displayed in a form, by check box, and add them to a table. The results displayed in the query are random and this poses a problem, at least for me anyway. :rolleyes: Any ideas?
I am trying to create custom "icons" using values retrieved from an access db. Right now, I'm pulling records that match the user's name(Fname) or "default", then assigning the returned values variables and plugging those variables in as the image, URL, and title of the "icon". The problem is that this setup will only retrieve the first record from the db that matches "Fname" or "default" and I want to create seperate "icons" for each record that matches "Fname". I can pull all matching records using the maketable(), but then I don't know how to format these as I have here to make additional "icons". My source code is included below:
Code: <% if Fname <> "" thenopencnset rs = Server.CreateObject("ADODB.RecordSet") SQL="SELECT ID, Date_Time, URL, Icon, Title, Description, Status FROM Submission WHERE Technician ='" & Fname & "' OR Technician='Default'" Set rs = cn.Execute(sql, , adCmdText)ID = rs.fields(0)URL = rs.fields(2)Icon = rs.fields(3)Title = rs.fields(4)rs.closecloseCNend if%><a href="<%=URL%>" target="new"><img border="0" title="<%=Title%>" src="menupics/<%=Icon%>.gif"></a><br><font color="#FFFFFF" face="Arial" size="2"><%=Title%></font>
Any help anyone could provide on this would be greatly appreciated. Thanks in advance. -Chris Gordon
Using Access 2000 is it possible to specify a starting location ie (c:) and using VBA search every sub folder from this point for files ending .mdb or .xls?
I want to be able to build a table with an record showing the file name and directory for each file found.
I design a form and all my work is based on that form. I have two tables which I join together as following. The unfitex field is calculating using fields from both tables as shown below.
Dim db As Database Dim rst As DAO.Recordset Dim sqlstr As String v = Combo37.Value sqlstr = "SELECT postdecgor.N1, postdecgor.Age, postdecgor.UNFITpc, postdecgor.DECENTpc, postdecgor.HHSRSpc, [UNFITpc]*[sample]/100 AS UNFITex, postdecgor.DECENTex, postdecgor.HHSRSex, [Sample size].[Age dwelling], [Sample size].sample FROM postdecgor LEFT JOIN [Sample size] ON postdecgor.Age=[Sample size].[Age dwelling]WHERE ((postdecgor.N1)='" & v & "'); " Set rst = db.OpenRecordset(sqlstr, dbOpenDynaset) Set Form.Recordset = rst
I then set up some textbox which shows the results as following.
It works, I can see the results on the screen, however I would like to save the results into a table and I am not quite sure how to go about it. Can you help??
:confused: I have 3 identical fe be database running each with a table called cars that has identical form values just obviously different data. I want to create a table or query to display all the results from these but whenever i try and make a query i cannot seem to work it out. Anyone got any ideas? :confused:
Alright, i'm almost sure this isn't the best way of doing this,
But i wanna build a scoring system out of three criteria. cost, delivery, and qaulity. I have those tables built along with a contact table where there over all score will be tallied up. Each contact can have more than one entry in the criteria.
So what I did was i built a query to make a new table for each contact to generate the score and the contact id, then using that ID, i update it to the contact table using the UPDATE function...however wheni have 3 criteria, and 400 contacts..this obviously becomes painfully slow.
The question is, is there a way to directly pull the sql query results (summing the total of each contacts score for each criteria), then storing it into the contact table without making a new table in the process?
I have a list-box(Category) and a 'ok' button on the form.
My OK button has the following [Event Procedure]: Private Sub OK_Click() Me.Visible = False DoCmd.RunSQL "Select [Barg Unit],[Medical Option],[Medical Coverage Tier] FROM RetireeCensus Group By [" & Category & "];" End Sub
First of all, is my syntax correct in the above codes? Second, what VBScript codes do I need to add in so that when I click the 'OK' button, a report or a table will show the query results from my 'Select' statement?
I have a query that prompts the user for input to generate a report. I would like to in essence copy that same record set and append it to a different table (archive table).
I have a query that checks an expiration date field and displays the word "Expired" in another field if applicable. This query is used to look up items, then the user would enter whether or not it is approved right in the resulting data grid thereby entering that approval into the table being used for the query. Can I do something with the query so that if the item returned has expired, a user would not be able to enter anything into the approval field? Something like, if field1 = "expired" then lock the table? I can't use a form, I have to just do it in the basic query or forget it. I would very much appreciate any suggestions!
I am trying to filter a form to show the entire weekend's activity on Monday but only yesterday's activity Tuesday through Friday. Using this code I can return Friday's results on Monday and yesterday's for the rest. How do I get the range Friday to Sunday?
I have an Access database with several tables and a multitude of subforms which are displayed on a single master form. The subforms are used to facilitate data entry. In several of the tables there are fields which are related and I would like to have some of these fields updated based on the results entered in the related field (i.e. the answer for one field depends on the other). Field 1Field 2 [facing][DISC_CODE] un3 up1 dn2
Field 1 is a simply a description of the basic dataset. This field is already set-up on form as a combo box that allows the user to choose one of three options. Field 2 is a code number used by another piece of software to identify a particular symbol. It is a new field being added to the database. There are 200 codes that identify a wide range of symbols for different types of data and I don’t want to have to look them up when I, or my assistants, are doing data entry.
My question is this; is there anyway to have the DISC_CODE value, Field 2, automatically entered in the table when the value for Field 1 is selected in the combo box on the form?
I am creating a table that is a master list of all of my company's product. Each "customer" that we have will always be ordering the same items, but not all of the items that we have available. I need a way to go through the master list and click a yes or no and have that item added to the "customer's list of items on a new table.
I need to create a sublist for each "customer" like individual shopping cats for each customer. These individual lists need to link back to the master list in case of product changes, description changes, and cost changes.
I would like to create a form where the end user can type in a product number, description, or manufacturer number and have that item added to the "customer's" list.
I have created a from which consists of search boxes in the above and the table in the below.
If we search for any in those search boxes then the results will be displayed at the bottom (The results will be displayed in the table which is located at the bottom of the page)
I had done with the above part.
My Requirement :
If I click on any of the row in the results table then is it possible to display only the selected row in a new form????
If I search for a keyword MS in a search box and if it returns 40 results and if I click on any one of the result then the selected result should be opened in a new form.
In another Table containing additional company information, I have the primary key as the company ID, and I want to make the query that outputs the above table, auto-fill the blank fields in the existing Company information with the same headings as Data ABC etc.
However, I don't want the query to add full new records (which I think is the Append Query?), instead I want the existing company records have additional fields (Data ABC) added, with information from the Query added.
Basically, I originally wanted to create a form which I can input data into Access with by using a button (I was unable to get Access to append the information from the forms to the table, so it didn't work out!). I did not want the fields on the form to be linked to a table, as then a record (and most importantly an auto-number) is created as soon as somebody starts typing. Should somebody stop typing halfway through and quit the form an autonumber will have been generated, which makes the number of "users" seem higher than it actually is. I got around this by changing the field from autonumber to number and then creating a query that selects the maximum value of ID in the table, then adds 1 to it (which is essentially the lowest unique number. I tend to call this newID).
The problem I now have is setting this to be written to the table alongside the data from the form (the rest of the fields on the form are now connected to the table, as I am no longer using an autonumber). I have tried the following: Setting the form to run the expression "[ID] = [qryMaximumUserID]![NewID] " on load. This returns the error "The object doesn't contain the Automation object 'qryMaximumUserID.' Setting the control source of the text box to be dLookup. This fills the textbox with the correct value, but then it doesn't write it to the table! Setting the default value of the field to be 0, then running an update query to update any ID of 0 (criteria "0" to the value of newID "update to: [qryMaximumUserID].[NewID]. "). However this doesn't work as whenever the query is run it asks for a parameter to be entered, rather than just taking the value from the other query. Writing a macro that is run on load to SetValue of item:[ID] to expression: [Forms]![qryMaximumUserID]![NewID]. However this returns an error "Microsoft Access cannot find the referenced form 'qryMaximumUserID' you entered in the expression.
Setup a query to find the result in a table containing the highest date value.
The query is linked to two tables : Payment information containing the date, and tenant information containing the tenant.
In the query i have selected the tenant name from the payment table (which is linked to the tenant name in the payment table) and the payment terms - ie weekly / monthly etc. I've then selected the payment date from the payments table.
The query should return for each tenant the latest date they paid.
On the pay date i selected the Max option.
But it shows me more than one record.
SQL query is shown here
SELECT Max(tblPayments.DateDue) AS MaxOfDateDue, tblLease.cboPaymentTerms, IIf([cboPaymentTerms]=2,DateAdd("ww",1,[DateDue]),IIf([cboPaymentTerms]=3,DateAdd("ww",2,[DateDue]),IIf([cboPaymentTerms]=4,DateAdd("ww",4,[DateDue]),IIf([cboPaymentTerms]=1,DateAdd("m",1,[DateDue]),"n/a")))) AS calcNextPayDueDate, tblPayments.cboTenant FROM tblPayments INNER JOIN tblLease ON tblPayments.cboTenant = tblLease.cboTenant GROUP BY tblLease.cboPaymentTerms, IIf([cboPaymentTerms]=2,DateAdd("ww",1,[DateDue]),IIf([cboPaymentTerms]=3,DateAdd("ww",2,[DateDue]),IIf([cboPaymentTerms]=4,DateAdd("ww",4,[DateDue]),IIf([cboPaymentTerms]=1,DateAdd("m",1,[DateDue]),"n/a")))), tblPayments.cboTenant;
Code: Outfits: ID | Top Color ID | Bottom Color ID 1 2 1 2 3 4
Colors: ID | Name 1 Red 2 Blue 3 Green 4 Orange
I'd like to have a query in design view to have the following result
Code: Outfit ID | Top Color Name | Bottom Color Name 1 Blue Red 2 Green Orange
In design view, I can link "Color ID" to "Top Color ID" and "Bottom Color ID" but I don't know how to specify in the GUI to create "Top Color Name" and "Bottom Color Name".
I have a form that users can input data into and based on that data it runs a query and generates a report. These reports can be different based on user entered data on the form. My issue is within the report I would like to sum certain fields. The problem with trying to sum theses fields is that they show up on each row so I have hidden duplicates but when trying to sum the field it still trys to count the hidden duplicates thus giving a value that is of no use.
I have tried many methods to sum but one of the problems I continue to run into when I create a text box and build an equation and reference the field I would like to sum is when the report runs it is asking for a value to be entered for the field I am attempting to sum. I shouldn't need to enter a value as I am trying to obtain the value.
I have successfully used VBA to populate select query results into an excel worksheet on open, for a co-worker. Now I am trying to populate the records from an append query to the bottom of those results, which are now in a table on an excel spreadsheet, Contractor EIF. I am trying to make this work because my co-worker modifies the results in the table, deleting rows, adding fill color etc. and as new projects begin he would like those added to the projects already in the table (without rewriting the entire table).
Private Sub Window_Open() Dim strDB As String Dim strMyPath As String Dim strDBName As String
I have created some code to get the records of a query and it puts them in the body of the email but it doesn't format it well for example the email looks like
James | halliwell | 31 Leanne | smith |27 Alexis | smith |8
I would like it to be in a table is this possible,
Code: Public Function SendEmail() Dim MyDB As DAO.Database Dim rst As DAO.Recordset Set MyDB = CurrentDb
