Easy Question - Compare 2 Fields
Mar 12, 2006
I have a field called Badge_Number in Table A.
I have a field called Badge_Number in Table B.
In Table A Badge_Number has entries 101, 102, 103, 104, 105
In Table B Badge_Number has entries 102, 103
How can I do a query to get a list of entries that are in A but that are not in B. (Query result would be 101, 104, 105)
This is the only thing I have to do in Access, so after trying to learn this for a while, I have decided it would be much easier to get help at this time.
View Replies
ADVERTISEMENT
Feb 5, 2008
Can someone tell me if this can be done somehow.
I have a date field, ID field and a memo field (I have to have this memo field).
I want to compare the memo fields by two certain dates and find out what doesn't match and if the ID field is blank. I have been trying with quaries but no luck.
I am not sure what forum this would fall under so I put it in general. feel free to move.
View 7 Replies
View Related
Apr 13, 2006
Hello,
I have 2 tables:
tblProviders
ProviderName
ProviderAddress
ProviderType
LocationCode
FacilityType
Etc...
tblFacilityType
ProviderType
Location
FacilityType
In tblFacilityType, the FacilityType field is a combonation of ProviderType and Location. Each of those fields are alphanumeric. What I want to do is have the FacilityType field in the the tblProviders table to look at the Location and the ProviderType fields in the tblFacilityType table to determine what the FacilityType field in the tblProviders table should be.
Hope that is understandable.
Thanks in advance for any assistance.
View 5 Replies
View Related
Jun 13, 2014
Need to compare 2 fields in a query, and return the larger of the 2 values in a 3rd field.
As in:
Field A=10 Field B=6 I need Field C to = 10.
Field A=5 Field B=6 I need Field C to = 6.
View 3 Replies
View Related
Aug 30, 2006
Ok, I'm absolutely positive I'm missing something totally simple here... Someone give me a clue...
I have a database whereby I track academic performance of students in our program. I am trying to use last year's table to calculate how many hours each student earned during all three semesters.
In many cases, students did not enroll in all three semesters, so I am dealing with Null values in many of the fields. The easiest way would be to do something with the Nz function such as follows:
HrsEarnedLastAY: Sum(Nz([HrsEarnedFall],0)+Nz([HrsEarnedSpring],0)+Nz([HrsEarnedSummer],0))
But what I am getting is the numbers in a string! Say, a student earned 12 hours in the fall, and 10 hours in the spring, and didn't attend at all in the summer. The answer I'm getting in the calculated field is 1210, rather than 22. I've tried about 50 permutations of the above expression and I truly can't see why it isn't adding the fields together... I have also tried this with parenthesis around each Nz: (Nz([HrsEarnedFall],0)) , same result.
The Sum function does not recognize Null fields, but I thought I could use the Nz function to account for that. If I can't use Nz with Sum, how the heck do I add these numbers?
What I need to do is create a table with these hours totaled, and then update my main table from it. I could conceivably add the fields together in a report, but then I don't have any way to update my main table other than simple data entry.
I really do not want to have to go back into almost 700 records and add in zeros in multiple fields just because I'm missing something stupid.
You'd think an application as powerful as Access would be able to add 2+2 and get 4...
Any help would be most appreciated...
View 6 Replies
View Related
Mar 6, 2006
Hi there
This is probably quite easy but I cannot make it work so would appreciate some help. I have a table containing vehicle information. I have 2 fields, Current Mileage and Max Mileage. I want to do a comparison of the 2 fields and should the Current Mileage be greater than the Max Mileage, the query returns the Vehicle Registration. Test data has been entered in the table I am querying and I was expecting 1 value to be returned. However the query as I wrote returned either all the vehicle Registrations or none of them. Can someone help with this please.
Thanks in advance for the assistance
Craig:)
View 2 Replies
View Related
Aug 8, 2007
Have devloped a database to log all support issues for an application I work with.
I periodically update the database by importing from another database
for example my main table tbl_issues has fields IR_NO, DESCRIPTION and STATUS
I then import data from tbl_import which also has these fields. The following query works fine:
UPDATE tbl_support
SET tbl_support.STATUS = tbl_import.STATUS
WHERE tbl_support.STATUS <> tbl_import.STATUS
AND tbl_support.IR_NO = tbl_import.IR_NO
However I also want to do this at the same time:
UPDATE tbl_support
SET tbl_support.DESCRIPTION = tbl_import.DESCRIPTION
WHERE tbl_support.DESCRIPTION <> tbl_import.DESCRIPTION
AND tbl_support.IR_NO = tbl_import.IR_NO
How do I update both fields at the same time?
View 2 Replies
View Related
Aug 9, 2005
Hello..
In one of my tables I have a field with the following characteristics:
Listbox
row source: SELECT Category.CategoryID, Category.Category FROM Category ORDER BY Category.CategoryID;
row source type: table/query
bound column:2
column count: 2
This gives the user the opportunity to see the category as well as the categoryID when choosing, however once selected only the CategoryID (which is a number) is visible.
I would like to put the same option on one of my forms, but Im not succeeding at this. How can I do this? Is this possible?
This leads me to my next question: If I want to load the information from the form and compare it in VBA with the one from the table, is it seen as a number or a string?
I hope it is clear..
Any thoughts on how to deal with this??
Thank u..
Stacey
View 8 Replies
View Related
Aug 27, 2013
Text fields name:
Password
confirmpassword
Trying to achieve:
on "After update" event for Confirm password a code needs to find if both Passoword and confirmpassword are having the exact character (Case sensitive).
Below code doesn't recognize the Case. It allows as long as both words are same:
Dim StrString1, strString2 As String
StrString1 = Password
srtString2 = ConfirmPassword
[Code].....
View 8 Replies
View Related
Jun 18, 2014
I would like to change the colour of field on a form if the date in that field is later than the date in another field.
View 9 Replies
View Related
Jul 18, 2013
I have a table called tblAuthorizations. It appears as follows
AuthInstanceID....ConsumerID....AuthNumberID....Au thStart....AuthEnd....PSRUnits....BSTUnits
1374006036.........356679..........20255102....... ...4/22/13......7/21/13.....0..............416
-432536491.........356679...........20255102....... ...1/21/13.......4/21/13....104..........416
-124970517.........356679...........20147863....... ...10/23/12.....1/20/13....208..........520
504564357...........469432..........20254788...... ....4/22/13......7/21/13....0..............520
282523535...........469432..........20254788...... ....1/21/13.......4/21/13....0.............520
I work in mental health, and when we put in a request for a consumer to receive PSR & BST services, we put the above information into our database. Each record identifies which consumer the authorization is for, a start date and end date for them to receive services, and the amount of two types of services (PSR and BST). When one period ends, we request another period, such as in the first three records above, and then the final 2 for another consumer.
What I need to be able to tell is how their service authorizations change. For instance, if I were to compare the first and second records, it would tell me that PSRunits decreased 4/22/13-7/21/13 from 1/21/13-4/21/13 levels.
I have a query that displays the records for each consumer for the current period, but I can't figure out how to identify the previous period's number of PSRUnits or BSTUnits.I've tried adding the following fields, but this isn't working.
PrevEnd: (Select Max(AuthEnd) from tblAuthorizations Where AuthEnd < Auths.[AuthStart])
PrevBST: DLookUp("[BSTUnits]","tblAuthorizations","AuthEnd=" & [PrevEnd] And "ConsumerID=" & [ConsumerID])
View 2 Replies
View Related
Jul 26, 2006
I promise I have searched, but I hav spent 10 minutes reading through posts that are unrelated...
What is the code to have multiple fields updaterd based on what is input into a field?
---
Example:
A ZIP Code Field, which updates City & State on the form when entered. (I have a table that has over 39,000 ZIPs w/ City & State already there)
---
I have several applications for this, but if someone could explain this use to me, I will be able to figure it out.
Thanks a million!!!
View 1 Replies
View Related
Dec 4, 2013
I'm trying to create a query that will compare the data in 3 fields in a record, choose the largest (I also have a criteria to order by if more than 1 field has the same entry and it's the largest of the 3), and then group by that.The fields I will need are as follows:
PRODUCT table:
ProductName
Chemical
ChemicalAbstract
PhysicalState
NFPAHealth
NFPAFlammability
NFPAReactivity
qryQuantityOnHand query (which doesn't link directly to the PRODUCT table, it links through associations with other tables):QOH...I will eventually need information from another table for the final reports, but I don't think it has to be included in this query.
The fields NFPAHealth, NFPAFlammability, and NFPAReactivity each may be 0, 1, 2, 3, or 4...I need to ignore blanks; if 1 of the above fields is blank, they will all be blank.For any record, I need to compare the number in those 3 fields to each other, and choose the largest number and group by that rating.
In other words, if the largest of the 3 numbers is a 3 in the NFPAFlammability field, all those products need to be grouped together.If the same number appears in at least 2 of the fields, the order that determines the grouping is: Flammability, then Health, then Reactivity..Ultimately the report will be grouped as follows:
Flammability
Rating 4
Product 1
Product 2
Product 3
Rating 3
Product 1
Product 2
Product 3
Rating 2
Product 1
Product 2
Product 3
Health
Rating 4
Product 1
Product 2
Product 3
[code]....
and each of the groups will be sub-totalled.I'm stumped at trying to create the query in the first place.The added aggravation here is that we are dealing with 23 stores, each with their own mix of products. I have another table that contains the information about which products are in which store.
View 4 Replies
View Related
Jun 1, 2005
I want to have code that disables fields based on the value of the contents of one of the fields. I wrote this code which goes on and on but it doesn't work. Can someone help?
Private Sub Form_Open(Cancel As Integer)
Dim Item_Number As String
If Me.Item_Number = "300123C" Then
Me.Batch_Lot_Number.Enabled = False
Me.Issue_Date.Enabled = False
Me.Production_to_BPT.Enabled = True
Elseif blah blah blah
else blah
Endif
End sub
View 3 Replies
View Related
Mar 22, 2005
I don't normally work in access so I am sure I am asking a really easy question.
I have a table that is connected to a form where users enter survey data. Currently they have a drop down form to mark the responses to the survey as "Fully Completed", "Partly Completed", etc.
On the table I want to add the text "Awaiting Completion" to all those without any entry so that we can find out how many are left to do.
I can filter the table to give me the correct records, and assumed I could paste the text directly into the column (as with Excel).
Can anyone please let me know how to update the column easily.
Thanks for your advice.
Chris
View 2 Replies
View Related
Mar 30, 2006
Warning - Complete newbie post!
Hi all,
I have 2 Access databases setup, each containing 1 table. Let's call them A and B. These two tables contain some of the same fields.
Table A is used for collecting new client registration info through our website and gets updated by SQL.
Table B is on a different server and is used for company newsletter mailings.
Table A is the one which is automatically being updated so I don't need to do anything with that table. Also, some fields in Table A are auto-incrementing numbers so we can differentiate a clients interest and provide a more informative newsletter for them specifically on their selected interest.
Whenever an entry in certain fields of Table A is made, I would like the same fields of Table B to be updated with that same information automatically. For example...
Client subscribes to our newsletter on our site and selects their interests, the email field now contains the subscribers email address and the various 'interest' fields contain a '1' telling us they are interested in that particular area of our business. As this takes place on Table A, it needs to be also replicated to Table B.
Surely this is possible, but how?
View 1 Replies
View Related
Apr 18, 2006
As I am an SQL novice I need some help
I have 3 tables
Table - Fields
SalesLedger - TransactionID, ProductID
Vouchers - TransactionID, Voucher_Ref
Products - ProductID, Product_Description
The Salesledger has fields TransactionID and ProductID
The Salesledger is linked to the Products table by the ProductID field
The SalesLedger is linked to the Voucher Table by the TransactionID field
How do I produce a query to list all the items in the Salesledger but if there is a Product in the Products Table matching the ProductID the Product details, and if there is a TransactronID in the Voucher Table list the VoucherRef.
At the moment, it only shows transactions if there is a corrosponding TransactionID in the Vouchers Table
Probably not explaned this at all well. I will try to elborate if required.
View 1 Replies
View Related
Jun 24, 2005
Hi all,
I am making a query which sorts birthdays into chronological order.
I have used the DatePart function to determine the month:
DatePart("m",[DATE OF BIRTH])
But I want to know how to display the month in CAPITAL LETTERS, i.e. instead of reading 1 it reads JANUARY
Thanks in advance
View 7 Replies
View Related
Feb 16, 2006
I have a list in Xcel of approximately 1100 client #'s that I have to match to our master list of 10000's.
I can create a query to pull these Clients, but I'd like it to only pull the 1100 requested. Is there a way to import an excel file to bump up against a query so that it only pulls the requested clients?
IE do I have to create a table and bring that data in then use them both in the query?
Sorry if this is a simple question, I'm just being thrown into the fire and have very little access background.
View 1 Replies
View Related
Mar 2, 2006
To start: Running Office 20003. I have created a survey with Frontpages wizard. Basically It has a topic and the end user rates that topic on a scale of one to five like so:
1. Work Environment
a. I enjoy my work place. 12345
b. I think my office has a good reputation. 12345
1-5 are radial buttons. It takes the users input and stores it in an access database as im sure you all are aware. Looks kinda like this:
1a 1b 2a etc.
3 5 1
What i would like to do is this: When the users are done submitting i would like to go back and take results. I.e. we had this many users select option 3 for question 1a and we had _ number of users select option 2 for 1a, etc.
The thing is i was wondering if there was a way to create a query for it. I know i could just put =1 or =2 but that would require running 5 queries on each question.... not fun. Anyway, any help would be appreciated.
Thanks,
Viko
View 1 Replies
View Related
Mar 22, 2006
Hi - I think this can be quickly sorted.
I use the following code in my query to merge together my customers details to 1 field:
LeadName: [Title] & Space(1) & [FirstName] & Space(1) & [LastName]
I want to do it for their address, what do i use instead if SPACE so that i get a NEW LINE each time rather than1 space to the right?
Thankyou
View 1 Replies
View Related
Apr 18, 2006
As I am an SQL novice I need some help
I have 3 tables
Table - Fields
SalesLedger - TransactionID, ProductID
Vouchers - TransactionID, Voucher_Ref
Products - ProductID, Product_Description
The Salesledger has fields TransactionID and ProductID
The Salesledger is linked to the Products table by the ProductID field
The SalesLedger is linked to the Voucher Table by the TransactionID field
How do I produce a query to list all the items in the Salesledger but if there is a Product in the Products Table matching the ProductID the Product details, and if there is a TransactronID in the Voucher Table list the VoucherRef.
At the moment, it only shows transactions if there is a corrosponding TransactionID in the Vouchers Table
Probably not explaned this at all well. I will try to elborate if required.
Edit/Delete Message
View 3 Replies
View Related
Dec 1, 2006
Hi all. I need to know if there is a way to show the last entry for a record. In my database there is a set of numbers i.e. 3000, 3001, 3002 etc which have multiple valuation figures with them i.e. 3000 could have valuation 1 £1000, valutaion 2 £1245. So as mention what i need is my query to show the last valuation entry for the selected reference number i.e. 3000 and valuation2 only.
Any ideas?
View 4 Replies
View Related
Dec 28, 2007
Hello,
I dont know if my brain is not working today....but I have a query that is prompting for user input....if the user clicks cancel it shows "Action Failed" "Halt". How can I get this to just close when cancel is clicked?
Thanks
View 2 Replies
View Related
Feb 21, 2008
Hello,
I working with different business groups who have vendors assigned to them. I have table of vendor names for each business group. I'm getting data from different sources with vendor names that are unlike table. example: ABC company, ABC. Even through these are different they are they same vendor. Is there a formula, criteria, etc. to pick these up? I will be updating the vendor table occastionally.
Thank you
View 4 Replies
View Related
Feb 18, 2005
i need to show a group of donators details in a report. i want to have one donator per report page, but at the moment its sticking three to a page.
any ideas???
View 1 Replies
View Related
