I have an order table and depending on the the product grp it needs to then be able to select the appropriate size scale table.
e.g.
I order suits, and select a size grade A ( there are 7 size grades ) I then then to enter the size scale for that product. I am not sure whether to create 7 differant tables with the different size grades and link that to the table, if so how do i get it select the correct table.
please help...
H
Selecting the correct table
--------------------------------------------------------------------------------
I have an order table and depending on the the product grp it needs to then be able to select the appropriate size scale table.
e.g.
I order suits, and select a size grade A ( there are 7 size grades ) I then then to enter the size scale for that product. I am not sure whether to create 7 differant tables with the different size grades and link that to the table, if so how do i get it select the correct table.
please help...
H
OK, my database is also complete, thanks to the help of this forum!!!
My next question is:
I have a switchboard that has a button for my data entry form. The lady that will use this form will, of course, enter all her records using the form into the database. Because of what she does, she will have to go back to records on a daily basis to make corrections to individual records. I would like a way to put a command button on my switchboard that will say something like "Correct Records". She will hit this button and it will ask her for the medical record number she wants to correct and then will take her to that record to correct.
Is there a way to do this or is there a better way? Currently I have a button that will ask for a day range and then will bring up a query of those records for her to correct but I would really like to be able to somehow bring up the individual record, by medical records number, for her to correct instead of going through a long list of records that were input on a single day. Thanks so much for all your help getting this thing up and running!!!!
I am at my Login Screen, I want it to return to the Login Screen if you select "NO" and Close the DB if "YES"
Here is my current code:
Private Sub Form_Close()
If MsgBox("Would you like to EXIT the Database?", vbYesNo, "Quiting Database") = vbYes Then
Application.Quit
Else
???
End If
End Sub
The code I have is.
Code:
Private Sub Command26_Click()
If Forms![test site]![prp test].Form.[A Right Answer] = -1 Then
Forms![test site]![number correct] = Forms![test site]![number correct] + 1
End If
DoCmd.FindNext
End Sub
Then when clicked it checks a yes/no box to see if "A right Answer" is the correct yes. Then it should pop to the main form and take the number correct cell and add one to it. I am trying to get the record to go to the next record inside the sub-form but docmd.findnext seems to be wrong too.
I am in the beginning of setting up a database and have NOT messed with multiple tables. I just want to make sure that I am setting up the tables as well as the relationships correctly.Here is what I currently have, 4 tables and they are named. Employee, phone, radio and spotter. Employee table is where my primary key is located and I currently have a 1-to-many relationship between Employee and the others.
What I want to be able to do is to store information in the multiple tables from 1 form, which I don't think will be an issue and then at a later date be able to pull up information. EX.Thus being able to skip anything being put into the spotter table. Then at a later date be able to pull up only the select information I originally put it?
I am still new at database design, and cant quite come to terms with my project and access way of doing things.
I have to keep a register of people who participate in projects. The projects can be of two different kinds. BUT (here comes the tricky part) The projects are being evaluated on three different indicators, with each one of these having 4 measurements, in the range of 4-0. That was a quick introduction. Now let me break it down in parts.
The people:
I have made a Uniqe identifier (Social Security number (PK))
First Name
Last Name
Department (This can be 4 different departsment) made a drop-down menu type.
The Projects:
Unique identifier (Project ID (PK))
Social Security number
Project Type
Start date
End date
Project type:
Unique identifier (TypeID (PK)
Project type (Cti / Regular)
How might i design this the best way, so i can combine the people with the projects there on. And which type.
There can only be one person, but he can be on many projects. These projects can vary in type. My problem is ensuring there connected proberly.
Furthermore, once the basic design is made i need to make evaluations based on their performance if they are on the projecttype "Cti".
here i need 3 x this:
Evaluations:
Objective (range 0-4)
Baseline reading (range 0-4)
Midway reading (range 0-4)
End reading (0-4)
Success = Yes/NO (here i will do a End reading <= Objective formula).
That was a rather long list, but i have sat working on this in three whole days, and im getting a little fed up with not knowing up-and-down.
I'm having some trouble getting my information that I input on my form to store in my correct table. I will attach my DB so you can take a look at what I have thus far.
Here is what I am wanting to do:
I have TblEmployee, TblEquipment and TblJunction and FrmTracking and FrmUpdate
I input the bulk of my information thru FrmTracking, my trouble is I can not seem to get the information that I input in my FrmTracking to store in the correct table. I can get the information to store in TblEmployee, however the information that I want to be stored in TblEquipment will not store in there.
I have a master table with all of my Site Names in it. I have a collection table that when a barcode is scanned it records the site name as being received. I built a query that counts the number of times the site name has been received.
What I want to do is list all of my sites from the master then display the count next to the corresponding site name. This will identify those sites that did not send in an item. Is there criteria in the query I could use so that 1) I could list all of my sites from the master table and then the count can be displayed from the collection table next to the correct Site Name?
what is the correct syntax to write a command line able to UPDATE more than one field in the table records having multiple WHERE criteria.
Here is my challenge:My TableI has the columns A, B, C and D which are populated, for example, as follows:
TableI
A B C D
1 2
2 6 4 3
1 7 5 9
1 2
2 5 8 5
etc.
I also have a FormII which updates TableII. Among the existing fields of TableII there are the fields C and D (same as above). When saving data entry thru the save button of the FormII, fields C and D will be naturally saved on the TableII. Well, I also want C and D info updated into Table I as well, but only when field A=1 and B=2.So what I need (for the click event of the button save in the FormII) is to open TableI and either insert or update it with the values of the fields C and D in every record WHERE A=1 AND B=2.For instance, assuming C=& and D=%, the desired result should be as follows:
TableI
A B C D
1 2 & %
2 6 4 3
1 7 5 9
1 2 & %
2 5 8 5
I did not find any examples in the net including multiple criteria..Here is what I wrote unsuccesfully:
Private Sub BtSalvarFrmII_Click()
CurrentDb.execute "UPDATE TableI"
Set FieldC = Forms!FrmII!FieldC.value AND Set FieldD = Forms!FrmII!FieldD.value WHERE FieldA = 1 AND FieldB = 2
Docmd.save
Docmd.close
End Sub
What would be the correct syntax?
heya all- ive got sql max working on my table beacause i changed a field type to number and then sql selects the maxium value- but i now need to select a specific number along with its record from that same table.
This specific number will depend on whats been selected in a drop down box- but when im running the sql its comming up with this error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
Could this be to do with the field- beacuse i needed to change it to numeric type as i wanted to use sql max, and what way could i get round it???
Heres my sql command that im running to get a value:
Code:sub sqlCmd( )'==SQLSTATMENT=================================set objrs = server.createobject ("ADODB.recordset")dim sqlStatmentsqlStatment = sqlStatment & "SELECT * FROM [Areas] WHERE [Area_Code] = '"& Code &"' "objRs.OPEN sqlStatment,objConn,1,3'==============================================end sub
any suggestions??
I have a database spread accross a number of tables, I'll simplify it a bit here - there's questions, answers, respondents each as seperate tables.
Respondents includes
RID
Name
PostCode
Questions
QID
Question
Answers
AID
QID
RID
Answer
The answer table contains answers to all the different questions.
How can I query this to compare questions ? grouping answers to spot trends in the data
So I'd want to pull out the answers for questions 1 and 2 showing someone that answered question 1 with Yes and question 2 with No.
Any help would be much appreciated, or even a point in the right direction.
I've played with Crosstabs, multiple queries and pivot tables - none seem to be able to do this.
Thanks :)
Hi
I want select 2 table at same time
this is my code and my first table
Code:
strSQL = "SELECT TOP "&Cint(intTopCount)& " * "
strSQL = strSQL & "FROM T_WEBLOG "
strSQL = strSQL & "WHERE b_published = true "
strSQL = strSQL & "ORDER BY b_date DESC, b_time DESC;"
the above code work very good
but I want select second table
I added this code to above code
Code:
strSQL = strSQL & "SELECT T_IMAGES.* "
strSQL = strSQL & "FROM T_IMAGES "
so this is my code
Code:
strSQL = "SELECT TOP "&Cint(intTopCount)&" * "
strSQL = strSQL & "FROM T_WEBLOG "
strSQL = strSQL & "WHERE b_published = true "
strSQL = strSQL & "ORDER BY b_date DESC, b_time DESC;"
strSQL = strSQL & "SELECT T_IMAGES.* "
strSQL = strSQL & "FROM T_IMAGES "
but after I added second code to it
I get this error
============
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Characters found after end of SQL statement.
/functions.asp, line 71
===========
whats the problem
Please help me I need it
Thanks
This must be the easiest of things, but I haven't used access in a while in now in my new job I need to run the following query.
I have to select a number of postcodes from a single table to see how many are in there. For example select postcodes beginning with B, CV, DY, HR, ST, TF, WR, WS, WW. Then I need to write down how many records found with the above postcodes.
Also I may have to omit some too like omit CV12
Thank you in advance
Hope this works, The table is as follows, ish
Name1 y/n1 y/n2 y/n3 y/n4 y/n5
a...........y......y....n.......n.....n
b...........n......n....y.......n.....y
c...........y......n....n.......n.....n
d...........n......n....n.......n.....y
e...........y......y....y.......n.....y
f............n......y....n.......y.....y
g...........y......y.....y......y.....y
what i need to be able to do is take a form/query answer some questions, y/n1= y/n2= y/n3= ......
Then off the back of this be able to pull out the correct item from column name1.
But i don't need an exact match on all columns. If i am only interested in y/n2=y i need to display all matches
My head says the following If y/n2=y and y/n3=y Then Name1 = e and g
or another example if y/n2=y and y/n4=y and yn5=y Then name1 = f and g
if a y appears in more columns it doesn't matter but it has to be in all those columns i am looking at
can anyone suggest the simplest way forward on this.
Hi,
I have 13 tables in all. 2 are Area and Scope which have unique entries of areas and scope and the remaining 11 will have a primary key, Tag_No.
I want to select data from these 11 tables such that ALL the data will be pulled out but say table 1 has a column MOC and remaining 10 tables dont, then it will be a blank or null in those columns for the 10 tables. How can this be done? (I hope I am not confusing!!!)
Adwait
Hi All,
Any ideas on how I do the following.
I have a table with 1500 records in it.
I want to select at random 4 of these records and append them into another table along with some other data that the query will not pick up, in this case Pilot and Month.
I guess I am having to go the VBA route but dont have a clue on how to do it.
Cheers in anticipation.
Andy.
INSERT INTO Assignments ( flightcode, aircraft, depart, destin, pilotcode, [Month] )
SELECT Schedule.Flightcode, Schedule.Aircraft, Schedule.Departure, Schedule.Destination, [pilot] AS Expr1, [MONTH] AS Expr2
FROM Schedule
WHERE (((Schedule.Departure) Like "man*"));
Here's my problem:
I got a Table which contains software:
TblSoft:
ID |Software
-------------
1 |Office
2 |Winzip
3 |Etc.
I made a form with a dropdown-menu in which I can select the software and it stores it in another Table named TblPC.
It stores something allright. but not the text. It stores the ID Nr.
How do I get this to work that it puts the text inside the table instead off te ID nr?
I allready tried changing the properties off the listbox but it wouldn't help.
in tblquery i have a field date.
it its formated to =Date()
i also have two more fields
1- month
2- year.
what i want is to extract the month and year from the date field.
but the thing is, i was it to be visiable when you are in the table "view".
but i dont know how to format this.
Hi,
I have a form that runs a parameter query to search for university name and then displays 2 fields, university name and course name.
I am having difficulty with a search button that i have on the form called search_command; it is supposed to run the exact query as when you enter the form, it does this but displays the result in a dataheet, i want it to repopulate my 2 text label fields as mentioned above.
In addition i want then to be able to go to a specific record, select it and then press a button to append it to another table. i ahve not started this part yet
Can anyone please help ?
I have to make a multi-user manage database.All the usernames are sorted in one table and exported to listbox in Form.For each user there is a separate table with 3 columns where i must fill information from listboxes.The listbox (there are 4 listboxes, one for users select and 3 for different parameters) are located in MainForm where you select the information.My question is how can I select a specific username(table) from the listbox and fill the information from the other listboxes to the selected one?
The usernames table is named - Clients
each table for separate user is named client1, client2, client3 etc.
the form where you choose what to select is named Fill form.
The listbox for users is named - List71 And the listboxes for parameters are named - List75, List77, List79 Also there are 3 textboxes with calculated fields that i also need to insert in the specific user table.
I am looking for a way to generate a list of all fields within a table, have the end-user select which fields he/she wants to include, and then run the query. I am trying to create this within a form for a nice, easy to use GUI.
I am using Access 2010 on Windows. The fields I need them to select from are in one table, however there are many lookup (tblkp) tables related.
Folks,
can someone help me on this? I am not sure why the select query inside this procedure is not working. Here is the that routine:
Public Sub temp()
Dim strDocName As String
Dim strTableName As String
Dim strTbl As String
Dim aot As Access.AccessObject
Dim strSQL As String
Dim rpt As Report
Set rpt = CreateReport
strTbl = Forms!frmSearchBoilerGuar!cboTypeOfGuar
For Each aot In CurrentData.AllTables
If aot.Name = "strTbl" Then
strTableName = strTbl
End If
Next aot
strSQL = "SELECT tblProjts1.chrProjectName, tblProjts1.chrBlrPropNum, " & _
"strTablename.memGuranItem , strTableName.memLDs FROM tblProjts1 " & _
"FROM tblProjts1 LEFT JOIN strTableName ON" & _
"tblProjts1.intProjectId = strTableName.intProjectId"
rpt.RecordSource = strSQL
strDocName = "rpt"
DoCmd.OpenReport strDocName, acPreview
End Sub
Basically, I am trying to select a table name from the combobox and then use that table name for my query. Then I want to use that query as a recordsource for my report.
Any help is greatly apprecited.
Shan.
I am trying to use a combo box to control which table a data entry form will write data to. I want to create a form that has a combo box to select from a top level table that I will call "Stores". Once a "Store" is selected from the drop down, the next field on the form will be a data entry field. The data entered in that field will be written to the table selected by the preceding combo box.
So, basically I would have say Wal-Mart, Macy's, Sears, K-Mart and etc, listed in my "Stores" table. Once I select one of the stores from the drop down, I would then enter a "department" name in the data entry field and based on which store I selected from the previous combo box, the data would be written to that stores department table (which each store will have its own department table), e.g., WalMartDepts, MacyDepts, SearsDepts, etc...
I have several lookup tables in an Access database. These tables are used to populate fields in the main table and act as filters for viewing record subsets.I want to create a form that does the following:
1.) List the lookup table via a combobox.I was able to accomplish this with the following code:
Code:
SELECT MSysObjects.Name AS [Table Name]
FROM MSysObjects
WHERE (((MSysObjects.Name) Like "tblJob*") AND ((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Name;
2.) When a table is selected from the combobox, display the table in a subform for updating.
I have a module which reads a CSV transaction file line by line and adds the correct transactions to an access table and places the wrong ones in a logfile.Now some transactions are rejected twice there is even one rejected six times. Whereas one wrong transaction is processed only once. I am certainly overlooking something obvious in the logic but what. Here is the relevant code.
Code:
Function ImportCSVForConfederation(inputCSV, ORG)
Dim TNO As Integer, TACT As Integer, TABLE As String, TLINE As String, I As Integer, J As Integer, K As Integer
Dim FLD1 As String, FLD2 As String, FLD3 As String, FLD4 As String, LogFile As String, LogPath As String
Dim Lim As String, ITNO As Integer
[code]....