I currently have a query that uses a function to get the current users email address. This is matched with a "Teacher Email" address in a table of students to only show students which match the logged in teacher. I'd like to modify this so the teacher can see any student in their department. Is there a way to do this without creating a separate "Teachers" table that would contain a department and email?
View RepliesI have a db which has a query. This query is rather simple where it filters fields based on criteria. Here is the SQL VIEW:
SELECT DSResult.STATUS, DSResult.TESTCODE, DSResult.BATCHNO, DSResult.TESTNO, DSResult.TESTDATE, DSResult.CODE
FROM DSResult
WHERE (((DSResult.STATUS)="TESTED") AND ((DSResult.TESTCODE)="41015" Or (DSResult.TESTCODE)="41016") AND ((DSResult.BATCHNO)<>"1") AND ((DSResult.TESTDATE)>=#1/18/2010#))
ORDER BY DSResult.TESTCODE, DSResult.TESTDATE, DSResult.CODE;
I need to alter this query so that where records that have the same "DSResult.CODE" and counted are greater than 20 occurrences and then display only these records sorted in DSRESULT.Code order.
Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.
My query shows:
field1 field2
apple a
apple b
banana a
carrot a
carrot b
dog b
elephant b
I would like my query to now display a third field and group field :
field1 field3
apple both
banana a
carrot both
dog b
elephant b
Hi,
I have two tables of data, one is a customer information (membersdata) table and the other is information recived from a bank (bankdata). Each customer has a 'bank description' field in its membersdata table and the bankdata table also has field 'bank description'.
The query I have at the moment gives me back the data that both tables have a matching 'bank description'. The query I want is one that will give me the data from the bankdata table that does not exist in the membersdata table.
So simply put the query I want is the opposite of the one I made with the wizard.
I hope that makes sense?
Any help would be fantastic!
Cheers
Phill
i made a query to display students results after an exam. there are 12 subjects but a student chooses 8 from that. After an exam, the results are keyed in and a result slip is printed later. The problem is that the result slip displays all subjects including those not done by the student. I typed a criteria >0 in one of the fields and no records appeared. Is there a way that i can use to display only the subjects chosen and marks attained by a student and leave out the rest?
View 1 Replies View RelatedMy colleague has a multiple table database and uses lookups for some fields. The simple query was to extract 3 fields and display all records from those fields. The result of the query was that the selected fields were listed first followed by all other fields which are usually not displayed.
View 5 Replies View RelatedI am exporting an Oracle report to excel, and using an Access macro to import into a table.
The records contain some duplicate info that I do want to capture, but I don't want to import the same records. (I import the file on one day, and someone comes in and imports the same file later).
This would work if I could get the table to not duplicate if three of the fields are the same as an existing record. I tried using the index, but it reconizes any/all of the fields that duplicate.
Any help would be appreciated.
I am trying to setup a table that tracks the progress of an item from a cradle to grave process with 26 steps in between. I would like a calculated status column that is calculated by finding the max value (Date) in a row and returning the "header" that is associated with the max value.
View 11 Replies View RelatedHi,
I need to match the data in all the fields between 2 tables.
eg.
Field 1 (Table 1) = Field 2 (Table 2)
Field 2 (Table 1) = Field 2 (Table 2)
so on......
Using the wizard, I'm only able to match 1 field at a time and this is very time consuming as I have 45 fields to match. Any advise? Can this be done via SQL?
Is there a way to have a query return only records where the text in two fields doesn't match?
View 1 Replies View RelatedI have done this in Excel before, but not Access. I do not know VBA. I figure this will have to be done in a query or a macro. I don't even know if all of this is possible in Access. I need to be able to split an Address field into:
Street Number
Street Name
Street Type
Street Direction
And the purpose of this is so that I can pull out the Street Type (Drive, Road, Lane, etc) and update the abbreviations (DR, RD, LN) to the Street Type spelled completely out.
I did this in Excel by creating a Named Range "Types" on a sheet that has the abbreviations in Column A and the spelled out versions in Column B so that I could convert the abbreviations to complete street types. I broke down the entire address into each part on another sheet. Then I did a VLOOKUP to look up the Street Type in Column B in the Types range. Works great! And the only way I knew at the time to do that.
But, by doing this, I have to get the data I need from a download into a spreadsheet, break the address fields down on a second sheet, do all kinds of field update conversions (to get the field names from the download to match the field names in my Access table). This takes extensive Excel programming. I just thought there might be a way to do it by simply importing the data from the download straight into Access. That is easily done, but the Street Types aren't consistent.
There may even be a better way to do this than splitting....something like if a field CONTAINS DR update it to Drive. This would be a long process to set up because there are so many different street types to consider.
This is basically for the purpose of finding duplicates. If there is one entry called 123 Main St and another called 123 Main Street, they are not going to show up as duplicates, rather as two separate records.
Hello,
My database has two tables:
Master and History
Both tables have a SSN field with the following format:
000-00-0000XX (xx stand for two additional characters)
I have a search form with a textbox that I use to type criteria to open a form. The text box has the following SSN input mask: 000-00-0000CC;0;_
This allows me to return data even if I have a part of the SSN
I have now placed a button which verifies if the SSN I will type in the textbox already exists in table History. I am having problems with the SSN format as I have to type the whole SSN while I would like to type part of it (like the textbox input mask).
If Not IsNull(DLookup("[SSN]", "History", "[SSN] = '" & Me![txtbox] & "'")) Then
DoCmd.OpenForm ("popupssnsearch")
Is there a way I can do this?
THanks.
(this might be more appropriate in Forms; apologies if I'm putting it in the wrong place.)
My company runs a home repair program and we keep track of our clients in a database I made a few months ago. We use almost exclusively a form drawing from 3 tables. We just hit 800 records (according to the form) and I was checking some things, and found that there are 807 records in each of our 3 tables.
I did a preliminary check, and the primary keys match as best I can tell, so I'm trying to figure out how 7 records can be missing from our form.
Any ideas?
Thanks in advance.
will
I have stored data in access. The records have name, address and unique Id. I want to be able to extract the unique ID from the Access database for all those records that match the name and address in any new file that I compare my Database with.
View 10 Replies View RelatedI would likw for my first and last name fields width to match the length of the name. So if the last name is Smith then the field width will resize itself to fit perfect fit the name. Then if I have a last name of Connor then it will grow to fit it perfect. I dont need this but it will be nice so everything looks a bit nicer an cleaner. I thought the following would have worked:
Code : Me.Lastname.width = Len(Me.Lastname]
Hi, I have a personell DB an I vant do be able to select only the persons from a specific "city" with a specific " genre" and a specific "skill" and also i want the query to "ignore" one of the criteria if I press Enter or input all.
I tried with "query design " and I don't seem to make it work for more than one condition.
Thanks
I want to place a control in a report footer that will return the highest number of consecutive weeks that a profit was earned. The database has fields named WeekNo and Net. The Net field contains positive values (profit) and negative values (losses). The WeekNo field is an integer from 1 to 52 designating the week number.
For example if the figure in the Net field is a positive number I want the control to count the number of consecutive weeks that a profit was earned. I know how to get the value of total weeks that a profit was earned; this is not what I am looking for. The control must return the highest number of CONSECUTIVE weeks that a profit was earned over the year.
Any help would be appreciated.
I understand that a query can accept wildcards in the search criteria but this is different.
I have a form on which a user selects a [product] part number in an unbound combo e.g. "12345-V111".
I then need a subform on the form to list records where the relevant field matches the left most part of this part number but is not necessarily complete. e.g. records with "12345-V1" would be returned, records with "12345-V2" wouldn't. The number of characters recorded in this field will vary, but if the characters that are entered match, then the record should be listed.
In other words, the record entries themselves need to be treated like wildcard entries.
Very new to access, I need an easy way to compare 2 tables with a common field (the name field) and list just the records that appear in the second table but not the first (primary) table.
Please help, thanks.
I would like to create a query that will delete records that match several fields from another table. This is complicated by the fact that one of the fields will be in one of 3 columns.I have attached a test database (no real details), all Sheet2 entries need to be deleted from Sheet1.
What I need to do is delete records that have the same 'Surname' and 'DPS' value but also the same 'Line5' value from Sheet2 in 'Line3' or 'Line4' or 'Line5' in Sheet1.The 'Surname' and 'DPS' are no problem, it's the variable position of the third field. I think I could do it in three separate queries but it would definitely be better in one.
I have a sales sytem in Access 2010.New customers have to be imported to MYOB daily. I have a query that finds new customers and appends them to a table for importing at a later stage. I export the records in that table if the field "imported" is set to false. The results are exported to Excel so they can be imported into MYOB after some additional data is added. If there are no records to append to the import table I want to stop the export from happening. I am new to VBA and don't know how to express that the select query "000 Append New Customers...." should only run if the append query has records. If there are no records found, I want to put a message saying "no records found". Here is the code I have behind the command button:
Private Sub PrepCustcmd_Click()
'Turn warnings off
DoCmd.SetWarnings False
DoCmd.OpenQuery "000 Append New Customers to MYOB Customers", acViewNormal, acEdit
[code]....
I have fields lastname, firstname, spousefirstname, spouselastname -
I would like to display:
Koenig, Mark & Jane (not a problem)
- but in some cases the spouselastname is different so then I would like to display:
Koenig, Mark & Jane Doeinger
This is how it displays with my code:
Koenig, Mark & Jane & Doeniger
I can't seem to get rid of the & between the spousefirstname and spouselastname.
Code:SELECT Potentials.Agent, [LastName] & ", " & [FirstName] & IIf([SpouseFirstName] Is Null Or [SpouseFirstName]=""," "," & " & [SpouseFirstName]) & IIf([SpouseLastName] Is Null Or [SpouseLastName]=""," "," & " & [SpouseLastName]) AS Expr1, Potentials.Address, Potentials.City, Potentials.State, Potentials.ZipFROM Potentials;
How do I get rid of the second &
In my table, I setup a multivalue field. In the screen, I can check off more than one selection. It stores the selections in the table as value1, value2, value3, etc...which is fine. However, when I go back to review the entry in a report or a screen, it displays it exactly the same as it is stored in the table - value1, value2, value3, etc...Because of size limiations, the field is only so wide, thus it only shows value1, va...and then the rest cut off.
Regardless of how wide I make the field, all of the values will not be displayed, plus, if there is only one value, it will be a waste of space. What is the best way to display one (if there is only one selected) or all of the values selected ? Is there a way to display then vertically, like value1, (next line) value2, (next line) value3 ?
Hi Guys and Girls,
While doing a rabbit DB for the missus I have hit a problem, if one of the does gives birth to 3 babies i wish to have the details entered onto a form (not a problem) but i would like to do this:
if number of babies = 3 then fields to display to enter details is 3 on the form, i have my table setup with up to 10 seperate field for the babies per record but am stuck trying to do this
any help or direction would be great
Hello folks,
There must be an easy answer to this! I have searched the forums but have not found what I need to make this work the way I want it to. I have a three part reference field. The records in the form are supposed to be sorted into ascending order Field1 first, then Field2 and finally Field3. The forms RecordSource is a query that brings through the relevant fields from my table. I have the query sorted according to these three fields so that every time I open the database it sorts the way I want it to. What I want to do is have a button that will sort the records into the correct order when the buttion is clicked. I am sure this can be done, but cannot get it to work on more than one field at a time! Any help/ideas would be greatly appreciatted.
Thanks
I am trying to display two fields when the combo box is unselected.
Basically I have a table source manufacturer. This table has Manufacurer e.g. Nokia. Model No e.g. 3230.
So on the combo box i want it to show Nokia 3230. Saves me linking to a subform to show the information.
Can it be done???:)