Query Or Code?
Jan 8, 2008
I have been asked to help with a database that someone is writing.
He has a Table that has 200+ columns and approximately 70 of those columns are Qualifications. The first few columns are Title, Forename, Surname.
What he is trying to do is query the table to find all people with a specific Qualification and that is a Mr.
If I had written the db from the start then I would not have produced so many columns for qualifications. Instead I would have 1 column for quals and the populate each record in this way.
My question to the forum is the following:
With the current structure of his table (200+ Columns) Is there a way that I can produce a Query that will find the results that he requires, 1 specific Qualification and Title, but without displaying all other details in the table?
I understand that I can choose what field to include in the query but what I really do not with to do, is produce 70 different queries in order to filter each qualification.
Any and all help with this question really is appreciated.
Thank you
Bev
View Replies
ADVERTISEMENT
Apr 5, 2013
First query = Sum Products:
Code:
SELECT Sum(Tab1.Inputs) AS SumOfInputs, Sum(Tab1.ValInp) AS SumOfValInp, Sum(Tab1.Outputs) AS SumOfOutputs, Sum(Tab1.ValOut) AS SumOfValOut, Products.Product, Products.VAT, Products.UM
FROM Tab1 INNER JOIN Produse ON Tab1.ProductID = Products.ProductID
GROUP BY Products.Product, Products.VAT, Product.UM, Tab1.ProductID;
Second query :
Code:
SELECT Nz([SumOfInputs],0)-Nz([SumOfOutputs],0) AS Stoc, Nz([SumOfValInp],0)-Nz([SumOfValOut],0) AS ValStoc, IIf([Stoc]=0,0,([ValStoc]/[Stoc])) AS CMP, [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
FROM [Sum Products]
GROUP BY [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
HAVING (((Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))>0.09 Or (Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))<-0.09));
I need to combine those two query sql code to make only one query.
View 3 Replies
View Related
Sep 30, 2005
I have this code from a Query that works.
SELECT Contacts.*,Contacts.Address1
FROM Contacts
WHERE (((Contacts.Address1) like "*" * (Enter 1st Line of Address here) & "*") AND ((Contacts.Site) = "Miscellaneous"))
ORDER BY Contacts.Site;
I want to replace the data "Miscellaneous" with a Public variable strSite.
If you can help I would be very happy and of course grateful.
Thanks
Ted :confused:
View 7 Replies
View Related
May 31, 2006
Hi
I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.
I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.
Help me in writing this code.
Thanks in Advance Very much
Deepak
View 1 Replies
View Related
May 31, 2006
Hi
I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.
I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.
Help me in writing this code.
Thanks in Advance Very much
Deepak
View 1 Replies
View Related
Jul 24, 2005
I am using the two lines of code below Each in separate columns of a query (or two text Boxes on a form) to calculate the elapsed time between two dates formatted as General Dates. The first line of code (in column A, "txtTime1") calculates to the total minutes between the two dates, the second line of code (in column B, "txtTime2") takes the minutes and converts them to total elapsed hours and minutes. They work great, however, I would like to combine this code and place it in only one query column or form Text Box. My VBA is not up to it, can someone out there show me how to combine the code below to produce the hours and minutes in one line of code? Thanks
=DateDiff("n",[StartDate],[CompleteDate])
=[txtTime1]60 & Format([txtTime1] Mod 60,":00") :confused:
View 2 Replies
View Related
May 31, 2006
Hi
I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.
I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.
Help me in writing this code.
Thanks in Advance Very much
Deepak
View 2 Replies
View Related
Oct 5, 2006
In the code of a button, ive got....
Which should be pulling the value in cb1 in the last cat query, but im getting an error saying it cant find the field "|" refered too...
Basically there is a table called Last Cat, and I want to draw the value of cb1 out of it where the catpathid = 5 so i made the query to do that... but now im abit lost....
Me.combo1.Value = [Last Cat Query1]![cb1]
any help would be awesome.
View 1 Replies
View Related
Nov 23, 2006
Im looking to have a query that selects the "NAME" from a table "tblPeople" where the NAME field begins with "A". Ive tried using WHERE tblPeople.NAME Like "A" with no luck.
Can anyone help me out, im sure im missing something really simple.
View 1 Replies
View Related
Dec 7, 2006
How do you create a query in code. Actually creating a query that appears under the query section of access. Is this possible?
Thank you
View 2 Replies
View Related
Mar 8, 2007
I need to display the results of a query on screen when somebody clicks a button on the form. So I put code in the event of the click that looks like this:
Private Sub Command20_Click()
On Error GoTo Err_Command20_Click
Dim stDocName As String
stDocName = "HHC Report"
DoCmd.OpenQuery stDocName, acPreview
Exit_Command20_Click:
Exit Sub
Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click
End Sub
So far, so good, but my problem is that this query is one row with a lot of columns - that creates a lot of pages to look through. Is there a command that would loop it around on the same page, or rotate it clockwise so it's going down the page instead of across?
View 10 Replies
View Related
Jun 12, 2007
I am having trouble with some code I am using on a query. Part of the query comes from two fields in a form. The rest comes from a table. The query then populates a report. The code I have written works fine. The trouble starts when I try and enter in some code so that if some of the fields in the form is left blank, it just returns values based on the fileds that has data, so ignores any blank fields.
I have tried to use examples illustrated in this Forum, but it does not seem to like it. I keep on getting the request to fill in the parameter value. I am sure it is something simple!
This is the code I have entered for the Invoice Date Field:
Between [Forms]![Invoices]![Invoice date 1] And [Forms]![Invoices]![Invoice date 2]
(this is so the user can enter a date range with the two fields)
View 14 Replies
View Related
Jan 14, 2008
OK I have worked in Access for awhile now but never worked with SQL statements or anything like that. This is what I am trying to do I want a criteria in a query come from a public string that I have. When some one logs in to the database it stores what property they work for I only want records to show up for that property. I don’t want them to have to type in there property code again every time the query is run. What do I need to do to get this to work? Any help would be greatly appreciated. Thanks
View 1 Replies
View Related
May 31, 2006
Hi
I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.
I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.
Help me in writing this code.
Thanks in Advance Very much
Deepak
View 1 Replies
View Related
Aug 18, 2004
I have an access database that links to a SQL server.
My problem is when I made a copy of the Access database,
made changes to one sub report and mapped to test tables and now I have VB code that
runs the overall report where the SQL gets jammed on the join, (which I noticed through debugging)
but when I take the same SQL and run it in the Query view - the SQL works.
has anyone seen anything like this before?
View 6 Replies
View Related
Jun 5, 2006
Basically what I am looking for is a way to set the criteria of a specific field in a query from VBA.
The reason why:
I have one very large query that includes lots of fields from many tables. I want to make a "search form" that sets one or multiple fields to have criteria to narrow it down.
You could select the field, say, serial number, from a drop down on the form, then enter a serial number and click a button that would refresh the form, showing the new query output.
Then you change from "serial number" to "location" in the dropdown, and type in a location, and it would remove the criteria from the serial number field and add one to the location field. When you click the button it will refresh and show the new query output (all the items in the specified location).
Basically I am trying to create one form to search the output of a query in multiple ways. I would like to keep it to one form and not have to make multiple versions of the query with different criteria.
View 2 Replies
View Related
Jul 29, 2005
i have a sample function
Function GetPercent(n As Integer) As Integer
Select Case n
Case 1: GetPercent = 100
Case 2: GetPercent = 50
Case 3: GetPercent = 0
End Select
End Function
I have a table like this
a | b | c | d | e | f | percent
----------------------------------------
1 | 2 | 1 | 3 | 3 | 1
3 | 1 | 3 | 2 | 1 | 3
1 | 2 | 1 | 1 | 2 | 1
2 | 2 | 3 | 3 | 1 | 2
1 | 1 | 1 | 2 | 2 | 3
1 = 100
2 = 50
3 = 0
for example the first row
1 | 2 | 1 | 3 | 3 | 1
(100+50+100+0+0+100) / 6
I think if I can call function from query than I write like it:
strSQL = "update table set percent = " & GetPercent(a) + GetPercent(b)
CurrentDb.Execute (strSQL)
but sure that I got an error because its not find the a and b
how can I do it?
good day!
View 1 Replies
View Related
Jun 6, 2007
I have a form that shows employee license information. I also have a query & form based on that query to show licenses currently suspended. I would like to on my employees form to flash "license suspended" label if their name/record id is on the license suspension query. For all employees whose license isn't suspensed I would don't want anything to show. I can get the label to flash and to appear. I just need help evaluating the query and comparing it to the current record. Example I
if me.id = queryname.id then....execute label flashing, etc.
Can I write code to do this? Thank you
View 4 Replies
View Related
Mar 16, 2008
Hello Friends,
I have Query1 that should get one parameter , how to set this parameter in a code.
Below the code.
Private Sub Command0_Click()
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("Query1")
MyRS.MoveFirst
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
'
Do While Not MyRS.EOF
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailName]
With objOutlookMsg
.To = TheAddress
.Display
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub
View 2 Replies
View Related
Dec 13, 2007
I have a query that has 4 fields that are text fields in an external database. I have text boxes set up on a form which contain either null or >0 (depending on an option button selected). I want to pass that >0 to one field in the query and null to the other three. I have the following as criteria in my query:
[Forms]![frmSelectPayment]![txt2ndPayment] (same one for each of the 4 fields except the form field name changes)
Below is the code that sets the text boxes that are sent to the query:
Select Case [FraSelectPayment] 'depending on which pmt being pulled
Case 1 '6 payment plan only
stDocName = "qryGetPlusARImport"
Select Case [FraChoose]
Case 1
Me.txt2ndPayment = ">0"
Me.txt3rdPayment = Null
Me.txt5thPayment = Null
Me.txt6thPayment = Null
Case 2
Me.txt2ndPayment = Null
Me.txt3rdPayment = ">0"
Me.txt5thPayment = Null
Me.txt6thPayment = Null
Case 3
Me.txt2ndPayment = Null
Me.txt3rdPayment = Null
Me.txt5thPayment = ">0"
Me.txt6thPayment = Null
Case 4
Me.txt2ndPayment = Null
Me.txt3rdPayment = Null
Me.txt5thPayment = Null
Me.txt6thPayment = ">0"
Case Else
MsgBox "Please select a payment"
FraChoose.SetFocus
Exit Sub
End Select
DoCmd.OpenQuery stDocName, acNormal, acEdit
If I run the query alone I leave all fields blank and put >0 on the one I want to search by it works fine (695 records returned) but when I run it through code, nothing is returned. I really hope this makes sense to someone! Can anyone see what I am doing wrong?
Thanks
View 3 Replies
View Related
Jun 12, 2005
I'm trying to set up a looping code to go thru each record in a query. I know in excel I would set the cell value to a range and offset to get to the next value. How is this done in Access?
View 1 Replies
View Related
Jul 28, 2015
I'm struggling to get my database to auto split up a Query by a certain Column and automatically get it to export into separate Excel files.
I have got the export sorted, currently set to export the whole query, but i'm struggling to do the splitting up.
View 3 Replies
View Related
Feb 16, 2006
Works great, but when I hit the number "3", (3 times in row) it will let me into the form. I want it to not let me in IF I don't know the password.
Where did I go wrong?
Private Sub Form_Load()
Dim pw As Variant
If InputBox("What is the password?", "Password") = "1" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
If InputBox("What is the password?", "Password") = "2" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
End If
End If
End Sub
View 14 Replies
View Related
Jan 14, 2007
I protect my code from people being able to read it by setting a password on the code from Tools > Properties, selecting the Protection tab and entering a password, and clicking "Lock Project"
Is there a way to write code that will remove that Lock Project check and check it back on?
I've looked through the Application.SetOption command and it doesn't seem to be one of the choices. It would be very helpful if someone knew how to do this.
Thanks
SHADOW
View 6 Replies
View Related
Nov 30, 2004
Hi,
Self learning trying to modify a query fieldname and criteria thru code.
Have a small form with a button making a copy of a query/s (eventually making about 50 copies).
Once these have been made, would like to open the query up, which I can do, then modify both
the fieldname and the field criteria to suit my needs from parameters set in the form.
How do I do this if it can be done ?
Thanks in advance
Ian T
View 3 Replies
View Related
Jun 5, 2014
I have a update statement as follows
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [PP TBL] SET [PP TBL].[GTIN] = '" & [UPC QRY]![PALLET GTIN] & "' " & _
"WHERE ((([PP TBL].[PP ID])='" & [Forms]![PP Edit FRM]![ID] & "'));"
DoCmd.SetWarnings True
Access is telling me it can't find the record and from what I have tested it seems to be the Update line, not the where line. I am basing the set portion as equals a query - could this be causing the problem? Or can code be based on a query?
View 4 Replies
View Related
