Hello Everybody!!!
I need a help.
I have one table that has a field called "Initial ZIP CODE", another field called "Final ZIP CODE", and a field called "AREA".
I have a data base that will give me the specific "ZIP CODE", that will be between the "Initial ZIP CODE" and "Final ZIP CODE", and I want to get the "AREA".
Anybody knows how to to that???
Tks in advance.
Paulo
In my Access Database, for each row, there are two queries I want to pull data from to give me the status of the item in the related columns. In Excel, I use one file with multiple tabs to vlookup the data. How would I accomplish this in Access?
For Example, Jacksonville has a value of Submitted in the Completed Checklist Column and Approved in the Parts List Column. These values currently come from two separate tables. How do I get my database table to update when the status changes for each of the columns?
Thanks in advance for your assistance.
I have an invoice table and a project table. A 1 to Many relationship from Project to Invoice respectively.
Invoice Table:
InvoiceID
ProjectID
InvoiceAmount
Status (Paid, Not Paid)
I am trying to create a report that lists Total Invoiced and Total Paid by Project.
I've been trying to do it in 2 separate queries (Total Sum of invoice and Total Sum of invoice where Status is Paid) and linking them but that causes multiple records.
Is there a way to do it in one query using a constraint on the second "Total Sum of Invoices" (i.e. where status is paid)?
Thanks.
This is a tough one. I am developing golf management software. There is a table of caddies. And there is a table of members. The Caddies carry two bags each one for each member. They are assigned their jobs through an assignment form. Therefore, a single caddie will carry for Member1 and Member 2.
Now I need to produce a report of how many times each member plays. So how can I query the assignment table where Member 1 and Member 2 fields exist so that I can have a single field of allMembers so that I can count their rounds in a report.
Thanks.....Jeff
Hi,
I am trying to query on a table, predominantly made up of check boxes, so that I can get a summary which essentially shows the number of 'checks' in each field within a given date range. I have attached a Word file, which should explain exactly what I would like to achieve.
If anyone can help me I would be extremely grateful.
Many thanks and best wishes
Russell
I am new at Access, but I am trying to run a query on two tables where the linking field is the address field.
One table has addresses storred as "123 Elm Street #123" and the other stores them as "123 Elm St. Apt. 123"
Is there any way to run this query on these two tables with this difference in the address fields?
I have a query that accesses data from 6 tables. The query displays 6 records where as there are 7 records that have to be displayed. The one record it doesnt display is similar to other records. I dont know why its not being displayed. Kindly help.
View 2 Replies View RelatedCan someone please help with the following:
I am trying to join two tables in a query. One of the tables has only the fullname while another has only the surname (this table is imported from an external database). I need to be able to match the two tables up. Can anyone please suggest how to do this or even if this is possible.
Thanks
I'm doing a database for a friend and the challenge I have is there will be a lot of attachments going into this database, which will hit the 2gb file size limit in no time.
My solution is have each year of attachment in a separate databases and link them to the main database. The attachment are tax documents.
They table layout is all the same. It will be like this:
TaxYear, ClientID, 1040, 1040NR, 1040X, etc.....
TaxYear will be whatever year the database is designated and ClientID is the client for that particular year. The same client can shows up in multiple years.
I have thought of inserting each table in a form, but when a new year come, the form will need to be adjusted. And it will has empty field if a client skip a year or two.
I have a database of Assessments, each record having multiple subforms.
I am working on a button, which creates a "similar" (same) assessment, copying over all the subform records/selections.
So, after I actually insert a new assessment, pasting all the values from the original Assessment:
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend
I need to copy over subform contents, for which I use an Append Query (actually, I use its SQL in VBA code to pass it the right parameters).
Now, naturally, I ran into Key Violations, because taking 50 sub-records from the original they would have their own AutoNumber Keys (ID's) and I can't append them into the same table.
My question: how do I copy over the same records (appending FROM and TO the same table) but cause the table to insert AutoNumbers for the records being appended?
Thanks!
I have 3 queries that pull from 3 dfferent tables. All 3 tables contain a field called Acct but the other fields in the 3 tables are different. What I would like is to have a form field that the user can input an account number and depending on which query the account number is in, that query and result will display. I have been trying the dlookup function but can't seem to get the coding down. Also, if the account number is not in any query I would like a message box to display saying it can't be found. Thanks for any help!
View 1 Replies View RelatedI have 3 queries that pull from 3 dfferent tables. All 3 tables contain a field called Acct but the other fields in the 3 tables are different. What I would like is to have a form field that the user can input an account number and depending on which query the account number is in, that query and result will display. I have been trying the dlookup function but can't seem to get the coding down. Also, if the account number is not in any query I would like a message box to display saying it can't be found. Thanks for any help!
View 1 Replies View RelatedI have 3 queries that pull from 3 dfferent tables. All 3 tables contain a field called Acct but the other fields in the 3 tables are different. What I would like is to have a form field that the user can input an account number and depending on which query the account number is in, that query and result will display. I have been trying the dlookup function but can't seem to get the coding down. Also, if the account number is not in any query I would like a message box to display saying it can't be found. Thanks for any help!
View 3 Replies View RelatedI have a database with 3 tables. Each table has an account number field however the tables are completely different as are the account numbers that populate them. To go with the tables are queries that will bring in desired information. What i would like is for the user to input an account number on a form and have some code in the background that will find the account number in the table it is in and open the corresponding query. Is this possible? Thanks for the help. I have been trying to write code with DLookup but can't seem to get it to do what I want.
View 7 Replies View RelatedI have a query that shows banned users (I work in a school). When a student's ban has ended, a tick box is checked in a form linked to tblBannedUsers to show they are no longer banned.
The query itself simply queries all records in tblBannedUsers with a username field (UserID) taking search criteria from a box on a form.
However, I want to filter out the students that are no longer banned (those with a tick in the checkbox). To do this, I thought it would be a simple case of editing the query, and in the Ban Lifted field criteria, use =False to say I only want those records with a tick.
This doesn't work and I still get all records given the combo box filter (which are just filtering for a username...if I leave it blank it gives all records via Is Null).
What should I be putting in the criteria to filter out those records with a ticked checkbox?
Thanks,
Steve Swallow
EDIT: I've just done a test query and <B>No</B> is the criteria to use, but when I use this criteria in my query which also take data from a form's combo box it ignore the <B>No</No> criteria.
I'm trying to limit the records on a subform via an option group selection. The group has 2 options: optionTrue (option value = 1) and optionFalse (optio value = 2). I have the following code in my query criteria of the true/false field.
IIf([Forms]![FrmHome]![frameProcessed]=1,-1,0)
The false part works, but the true part doesn't. I've tried many variations using true/false, using checkboxes, etc. and nothing works.
The database is SQL Server if that matters.
Any suggestions?
Also, is their a way to have an option for True or False or ALL?
Thanks,
Sup
I'm really not sure how to go about this. I'm creating a course booking system and when creating a booking I need to check for current bookings with the same employee and course id's (i.e. the employee is already booked on the course). The query takes the employee and course id's from a form, and is initialised when the 'book' button is pressed. It correctly selects if the person is already booked on the course but I want the query to return a value to the form i.e. if it returns null/false then the booking can be created but if it returns a record/true then the booking already exists and a message box can be displayed.
I'm not sure if I'm going about this the right way, can anyone suggest how this can be done as described above or suggest a better way of doing the task?
I'm trying to have a single or multiple query criteria based on what the user checks on a form.
I can't get the True condition to work at all, I get no records. Here is what I'm using
IIf([Forms]![FrmAttendanceLogsRpt]![BlkFilter]=-1,[TempVars]![EID] Or 86,[TempVars]![EID])
If I just put
[TempVars]![EID] Or 86
in the Criteria it works just fine.
hi !
i'm very "fresh" in access , and i want to learn how to build something like vlookup .
i have 2 tables : car , general
i want to get a query that give me :
id number plate color
1 111111 blue
2 222222 black
3 999999 not found
i want to see all the id's and all th number plate and the color's.
in id=3 there is no color in the table , and i want to see this line in my query with the words "not found" at the color column
I've created a DB to track costs of subcontractors hauling loads for a warehouse that services a number of stores. I've created a stores table and a hauliers table. Hauliers will charge different rates to other hauliers for delivering the same stores. How do I key in a haulier ref and a store number and get the database to return the cost of this trip. It would be easy in Excel with a VLookup, but how do I do it in Access.
Can I create a query with hauliers down the row headings and stores along the column headings from the existing tables?
Help!
I have the SQL statement for the VLookup (Dlookup), but I don't know where to enter the statement. Please tell me exactly where I need to enter the statement. In the SQL Specific menu? Union? Pass Through? Data Definition? Or some place else?
UPDATE Table1 INNER JOIN Table2 ON Table1.material = Table2.Material SET Table2.Serial = [Table1]![serial];
Also, do I need the ";" at the end of the statement?
Thank You,
Ease
Access question
Does anyone know how to do a simple query which will change a number in the data from one measuring scheme to another like centimeter to inches I don't want a math formula but something like a vlookup in excel?
or something like that? in ACCESS?
Edit/Delete Message
I
Dear All,
I have looked at many of the threads on Vlookup on the net but I have not solved my problem:
I have two Tables;
Table 1 Contains a translation/link from a local product categories to international standard categories,
Country - Local Category - International Category
Germany - AA - XX
Germany - AB - XH
UK - FF - XX
UK - HG - XX
USA - FG - XH
Note: local categories are not uniek
Table 2 Contains sales values by Local Categories
Country - Local Category - Sales Value
Germany - AA - 20
Germany - AA - 10
USA - FG - 25
UK - HG - 15
What I now want is to create a query which will provide the following result:
Country - International Category - Sales Value
Germany - XX - 30
USA - XH - 25
UK - XX - 15
Because Local categories are not uniek in the link table a normal query link returns multiple times the sales value and I want the query to return one (linked to the first it sees in the link table, like the vlookup from Excell)
Can any one help??
Thanks!
Hi,
I have two access tables :
Table 1 : Customer number / E-mail
Table 2 : Customer number / Address / Phone
I like to add the data of table 1 to table 2 :
Customer number/ Address /Phone / E-mail
When of course the customer number is matching in both tables .
Can someone help me ?
Thanks
I need to use dlookup in a query to populate a new field "number" with values obtained using a current field "letter". I have a (lookup) table as follows: Col1 = A, B, C, D and Col2 = 1, 2, 3, 4 which of course gives the number code corresponding to each letter.
In Excel, I simply use the formula: vlookup("letter", "lookuptableref", 2, false) and it populates my new field number with the correct values.
Can anyone help me with the equivalent in Access using dlookup?
Thanks!
I have some employee data to import into Access but this data does not have employee ID numbers. I would like to assign employeeID numbers to each person then set the employeeID field as the primary key.
Here is what I had in mind. I would create a table that defines each employee with an EmployeeID number. Then in the table containing the imported data, the first field would be called EmployeeID. After importing the Excel data into this table, initially this EmployeeID column would be blank because the data in the Excel file doesn't have an employeeID. But the employeeID field would have a VLOOKUP formula that would look at each person's name and cross reference that with the employeeID table in order to obtain the employee ID for each person on each row. Then the VLOOKUP would return the employeeID number. Furthermore, I would like to have this employeeID field set as the primary key.
Can you do a VLOOKUP in Access? If so, can the employeeID field still be used as the primary key field, even though its values would be determined by a VLOOKUP?