Happy new year all. I have 2 tables
I have a query that if any tbl2.num=tbl1.num AND tbl2.action = 'confirmed' this row should be selected for deletion from tbl1. I've tried linking the tables and using an iif statement, but no good and I'm not sure what else to do- any ideas?
Forgot to add- tbl1.num might or might not exist on tbl2
So I have two tables:
users
primary key = user_id (AutoNumber)
surname (Text)
forename (Text)
...
user_change
primary key = user_change_id (AutoNumber)
user_id (Number) which relates to the user_id from "users" table
change_type (Text)
action_date (Date/Time)
...
In user_change I record any changes made to the users table.
What I am wanting to do, within a query, is pull basic details from the "users" table (forename, surname, etc.) which is working fine, but also add in SPECIFIC data from the user_change table if it exists.
I want to pull ALL rows from the users table, not just specific rows, and not just rows where my criteria for the user_change table match.
This is the data I want to pull from user_change...
The MOST RECENT action_date WHERE change_type is LIKE "*issued*".
However there won't be a change_type LIKE "*issued*" for everyone - I want it to be included only if it exists.
At the moment my query is ignoring any users who don't have a user_change record with "*issued*" in the change_type value. I'm also getting duplicate user rows where people have more than one value for "*issued*" - I only want the most recent one...
I am new to Access in a way, and developed a small tool as a warehouse management system. the system is working great but I need to add a rule or condition to this tool.
I have Tables having warehouses locations (LOCATION1,LOCATION2, LOCATION3, etc...)
Some items (products) will be entered into a transaction table where with queries and reports, can see each item in which location it is or in a particular location what are the products stored in.
however, if there is no limitation or condition in a way or another into the access application to limit users from entering an item OUT in the transaction from a location which doesn't have this product, I need access to tell me a message or stop me from entering that transaction.
To explain more, if I have ProductA in LOCATION1, and one of the data entry guys entered a transaction in LOCATION2 for the same ProductA, access will not allow him since there is no quantity of that particular product in that LOCATION2.
How to do it? I checked in Data Validation and Validation rules, but its either hard for me to understand or am not getting how to do it?
I have two tables "Funds" and "Classes" with one-to-many relationship, resp. (through the "Fund_name" fields). There is the field "Active" in the "Funds" table and the field "Open/Closed" in the "Classes" table. What I would like to do is to put "No" value into the "Active" field record if the corresponding (with respect to the relation of course) "Open/Closed" field records have all values "closed". Otherwise put "yes". Is there any way, how I could make this?
View 14 Replies View RelatedI have specific query related to Access, Currently I'm trying to create all possible combination of lot data. I need to know if this would be possible in MS Access.Below is simple example of what is required
Table1 Table2 Table3
Value1 TokenW GateA
Value2 TokenX GateB
Value3 TokenY GateC
Value4 TokenZ GateD
I have 3 sets of data for which I've to create all the possible combinations but with restrictions.Below are few of my restrictions
1. "Value2" always comes with "GateC" & Vice versa but, both don't exist independently.
2. "Value4" always takes "TokenW", But "TokenW" exists independtly as well doesn't need Value4 always.
Hi - I wonder if anyone could possibly help, I have a list box that is populated by a query, which currently populates the list according to which site is selected within a combobox - what i would like to do in addition is to have a check box which controls how much data is shown, ie, if the checkbox is ticked show all data from April 1st onwards, if not ticked to only show the last 12 weeks (84 days)
I have tried to acheive this using the code below:
[Forms]![frmPccAnalysis]![cboSelect] --is used to select the site--
IIf([Forms]![frmPccAnalysis]![chkFilterLimit]=-1,(Between #01/04/2006# And Now()),(Between (Now()-84) And Now())) --is what i am trying to use to either restrict or show all data--
when i run the code i just get empty fields, if i manually enter the "Between #01/04/06# and Now()" statement in the criteria it works fine, just not with the conditial checkbox. Can anyone please point me in the right direction?
Many Thanks
Ian
My Access is very rusty and I really can't get my head round how to do this!
I have a simple caller i.d. system which detects incoming telephone calls. I have been running an append query where the incoming call number is a query parameter against a table of customer numbers and names. The append query basically adds call numbers, names and times to a call log table.
This all works fine! ...however I realise that if the incoming call number is not in the table of customers numbers and names, no log entry is created. What I wish to do is, if the number is not found to append it anyway together with the time of call. Basically if the query fails to find a result, append details anyway albeit without a customer name.
I'm sure this must be simple but I'm going round in circles. Any thoughts anyone?
Thanks
i have a query from 2 table. let's say table a & b. i have one form to show the query. the query show data based on what enter on the form.
when someone enter a value, on form, the query use it as filter for displaying data. my problem is one field of my query show data from the second table.
if input is AA, the data will be shorted by AA plus showing first colum of table 2.
if input is AB, the data will be shorted by AB plus showing second colum of table 2. etc.
so far, i make several query for each kode to display the corect column.
can i use 1 query to do that? if not, what should i use? macro/module? please the example aslo.
thanks
Hi,
I have a query that combines fields with an IIF statement adding a comma as the seperator "," is it possible to put a condition on it so that if a field had soley an integer or even a string with a value of 4 or less characters that the comma will be excluded, so for example:
A1 A2
1 Parlmont avenue
changes to:
1, Parlmont avenue
want it to change to:
1 Parlmont avenue
this is how the IIF statement looks like:
IIF(IsNull([I1].[A1]),[I1].[A2],[I1].[A1] & IIf(IsNull([A2]),"",", " & [A2])) AS Expr2
where I1 is table and A1,A2 are first and 2nd fields respectively, any suggesetions very much appreciated.
M-.
I have a query that brings up a job#, its start date (mm/dd/yyyy), and its
priority status (Low, Medium, or High).
Is there SQL I can use to make another query that will look at the start date,
and if it is a week old, change the priority status to Medium, and if it's a
month old, change the priority to High?
Thanks!!:)
HI
I am working on a database that holds communications from treatment episodes.
the sub form holds the communications related to that client and that treatment episode.
when you enter a date into the communication record a mesage box asks if this is a new treatment episode and if yes opens a new treatment record. If it is the same treatment episode it just remind you that you are in the same treatment episode.
what i would like to do as an additional cue to the user is that once a client has been discharged from the episode that all communication records change color.
I can get the field that says dc to change color but I want all the fields in all the records from that episode to change color.
any ideas?
thanks
Hi guys,
I have a form with an option group. It has 2 options. The first one has a value of 1, the second has a value of 2. This option group is named "category".
Separate from this I have a query with several fields. In one of these fields, I need to enter a criteria that enters "LOE*" if the option group value is 1, or if the value is 2, I need it to enter "IDC*" in the criteria. My form that the option group is on is named "SelectWell". This is what I have tried so far:
Like IIf([Forms]![SelectWell]![category]=1,"LOE *","IDC*")
This works correctly if the option group value is 2, but doesn't work for the 1 value (LOE*).
Thanks,
Chris
Hi, I have the following situation:
I have two tables: userdata and superusers and a function username() which can be used for acquiring the username of the active user.
Table userdate contains a column 'username'. With the following SQL query the active user can obtain the records from the userdata table belonging to himself with a query like:
SELECT * FROM userdata
WHERE userdata.username = username()
Now, what I want to do is that IF the username (obtained using the function call (username()) exists in table superusers ALL records have to be shown, just with:
SELECT * FROM userdata
I want to do everyting with only one query, so that if the query is executed either all records from userdata are shown (when username is in superusers table) or only the ones that belong to the users.
Is there something like a conditional statement to create a conditional WHERE statement, or maybe another solution to solve this problem?
Thanks in advance.
I have a query set up to prompt users when their license will expire (30 days out). Problem is that there are 2 types of licenses and some have different dates. I would like to have a report where I could see all the people that are about to expire either it be one license or the other or both. Thank you please help.
View 1 Replies View RelatedDear all,
I have a query that is based on orders table and contains "Qty", 'UnitCost', and "ctualUnitCost", in addition to yes/no control to indicate if "SalesTax" is applicable.
I want an expression in the query to calculate total cost in one of 2 conditions:
if no sales tax then TotalCost = Qty*UnitCost
if sales tax is applicable then Total cost = Qty*UnitCost*1.1
how can I write this expression
thanks
how do i use conditional statement in sql? im using access 2003 as frontend and backend.
i would like to count the number of records in a table (ex. ENROLL) where there is only 1 instance of any value in a field (ex. STUDENT_NO) plus another condition where a given value (ex. 1) should exist in another field (ex. SECTION).
example
Table name: ENROLL
STUDENT_NO SECTION FEE
16 ----------- 1 ------ 75
16 ----------- 2 ------ 75
17 ----------- 1 ------ 100
18 ----------- 2 ------ 125
19 ----------- 1 ------ 75
19 ----------- 2 ------ 75
19 ----------- 3 ------ 75
20 ----------- 1 ------ 150
Parameters:
COUNT
The number of records (count) in the table where there is only one instance of the STUDENT_NO value and the corresponding SECTION value is 1
VALUE
The sum of the FEE values in the table where there is only one instance of the STUDENT_NO value and the corresponding SECTION value is 1
The answer should come out like this:
count = 2
value = 250
Can anybody please help me to construct the SQL statement?
Thanks in advance!
Hi,
I have problem in creating conditional expression for query table. I want to add IF Else statement in the statement so that if the value is more than 50, a new field called grade will display the grade of "A" in the new field.
Can anyone teach me how to write conditional expression.
Thanks
Dawn
I have an interesting report query I am working on. Is it possible to have a query that shows different fields for individual records depending on the content of the record? Let me see if I can explain. Let's say that I have a Status field that can be "Complete", "Active", or "Planned". And lets say also in that table I have Field1, Field2, and Field3. For each record, I don't want to include all three fields (Field1, Field2, Field3), but only one of these fields depending on what is in the Status field.
So, it would be something like:
Select Status, (if Status = "Complete", select Field1), (if Status = "Planned", select Field2), (if Status = "Active", select field3) FROM table;
So, the query would result in 2 fields for each record: the status field, and one of the three other fields depending on what is in the status field for that record.
Thank you kindly for your help.
i need to clean up a date field (I hate dates in access!). The field in the table (imported from a spreadsheet) has records where users have simply entered a date in the correct format and then others where users feel obliged to add comments after the date rather in the 'Comments' section. Therefore Cdate alone won't work.
So far i have tried:
Code:
IIf(Len([TabDL].[PROMISE]<>10),#1/1/1900#,CDate([TabDL].[PROMISE])) AS PromiseDt1
The intention being to insert a holding date 01/01/1900 if the field contains too many characters to be a date alone. I've tried multiple variations of this code but keep getting errors across the board. Is my logic or Syntax flawed?
how to conditionally format query results? I have read another forum post that says that query conditional formatting is possible.This database keeps track of projects and their associated tasks & statuses. I created different queries depending on task name( i.e. assignment date, approval date, etc.) Now, with some of the tasks, if it is overdue, it needs to be highlighted red. If it's cutting it close, should be yellow. I can't figure out how to set up a conditional formatting rule to address this. So instead I've had to create to separate queries for "red" and "yellow" rules, and display them separately in the Dashboard form.
View 4 Replies View RelatedI'm populating an unbound ComboBox with a Query via the QueryDesigner. The value of the Rowsource depends on the value of another ComboBox at the main Form.
After several hours of trial and error, I came to the solution showed at the attached image ("works"); my concern is.. why does my first approach does not work via the IIF clause? ("not work")...
I use an access db that stores our customer info on a mssql backend. I need to be able to create a mail merge based on a customers renewal date. Now I do know how to set parameters in a query but is there a way I can use a access report to enter a month and day they are set to renew then have it create the mail merge based on their info?
The Layout is fairly simple just a renewal notice with what they have on their plan.
I have a simple crosstab query:
FirstName and Surname as Row Headings.
Date as Column Heading.
OnShift as a Value (Count).
These are from the table DailyActivityLogs. Also in that table is a choice field called Weather that lets you choose the conditions that day (Weather, Work, Part Weather, Subbed).
This is the report the crosstab query generates:
What I would like to do is colour the Count fields depending on what the weather was that day. It seems possible, as Weather sits in the same table, but the field Weather isn't in the crosstab query.
Can I do this?
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I have some experience doing 'Update Query' using two different tables but I'm having a hard time doing an 'Update Query' using 3 tables.
I have my source table TP05XY with the fields 'Mark' 'Date' 'UTM_Edig' and 'UTM_Ndig'. Mark and Date are my primary keys (they together uniquely ID each record). I have my Observations table with the fields 'Mark' 'Date' and 'Obs_ID'. The last table is Locations with 'Obs_ID' 'UTM_E' and 'UTM_N'.
I want to update my fields UTM_E and UTM_N from UTM_Edig and UTM_Ndig. However, to do so, I have to go from my TP05XY table, through Observations table to update Locations table. Table TP05XY is joined to Observations through 'Mark' and 'Date' and Observations table is linked to Locations through 'Obs_ID' field.
I have tried a few options without success ... anyone knows how to do it?
Thanks,
Josée
I am using Access 2010.I have a table that I am using to pull my data from other tables and a query.My table is called tblMyData.One of the field names is level1. This field points to another table, and gives the user the choices for states (examples California, Texas, Maine).
Another field name is level2. This field points to another table and give the user the choice for type of customer (examples Business, Consumer)
The field name level3 points to a query. The query, qryFinalChoice matches up the choices based on level1 and level2.for example, the user can pick California for level1, business in level2 and California Widgets or Los Angeles Clothing store in level3 (plus about 20 other choices).
If the user picks Texas for level1, business in level2, business in level2 and Houston rugs, or Texas style restaurant in level3 (or about 15 other choices).
-I am recording 1 for California on level1, 2 for Texas and 3 for Maine in level1.
-I am recording 1 for business and 2 for consumer on level2.
The query qryFinalChoice has all the combinations for state, business or consumer, and lastly business name or consumer name.
-qryFinalChoice has line1 to match up the choices for level1 in my table.
-qryFinalChoice has line2 to match up the choices for level2 in my table.
-qryFinalChoice has line3 to match up the choices for level3 in my table.
I do not want any of the Texas business names appearing when the user picks California, or vice versa.
My SQL in my tblMyData tab for level3 looks like this:
Select line1 from qryFinalChoice where line1=1;
I am able to get all the line items where California is a selection.How do I change my SQL to pull all the line1 choices where I have selected from level1, and all the line2 choices where I have selected from level2 automatically based on my pulldowns?