Multiple Criteria Query Help
Jun 29, 2007
Hi,
I have 3 fields that I need to run a query on. Date_Image, IMAGE_SYSTEM and DATE_TO_BR. If there is no entry in either fields, it should be part of the query. If entry is in both Date_Image and Image_system, I do not need those results in the query. If Date_to_br field is empty, I must have an entry in the other 2 fields before this record is not displayed in the query. I hope I was able to explain this clear enough. I was trying to do this with the IS null and Is Not Null expressions, but I am not getting the results I want.
View Replies
ADVERTISEMENT
May 14, 2005
Hello all:
I need your urgent help with the following:
I am setting up a query in access. I have about 4 criteria for one of the fields called Center, I want the query to do the following:
Return all rows if:
Center is Like 6101*
Center is Like 61HKS56800
Ignore row if Center is Like 6101D*
Ignore row if Center is like 6101SALM01
I put in the following and I am not getting the correct results:
Like "6101*" Or Like "61HKS56800*" And Not Like "6101D*" And Not Like "6101SALM01"
Thanks,
Odun
View 1 Replies
View Related
Aug 24, 2006
Hi all,
I'm working on a purchasing app in access. At this point i'm working on the reporting module. I want a user to be able to fill out a start date (text box), end date (text box), and select a code from a list, hit Run Query, and have it pull up a report listing the date that the selected code was used, between the start and end date, and display other info as well.
The problem i'm having is that i can get the date ranges to work, or the code to work, but not both of them. Here's what i have in my query:
http://content.imagesocket.com/images/gl_codes2ff.JPG
I'm really lost here. Any advice would be appreciated.
View 5 Replies
View Related
Nov 7, 2005
Hi,
This is probably an easy one but for some reason I'm not finding it in any of my reference sources.
I'm trying to set up a query that calculates tax my company owes the gov't, and the tax varies based on year to date totals. The 1st $30,000 is taxed at 4% and everything over $30,000 is taxed at 5%.
tblOrders has fields for date, CustomerID, OrderID and OrderTotal. Can the query have an expression field ("UpTo30") for ([OrderTotal] <=$30000) and also a 2nd expression ("Over30") for ([OrderTotal]>$30000)? And in the same query is it possible to also include the calculated fields for ([Upto30]*0.04) and ([Over30]*0.05)?
My main question is where to put the criteria - all I'm seeing talks about multiple criteria being State=NH AND/OR Name=R*, not multiple criteria on the same field. When I try WHERE statements I just get error msg no matter what the syntax is.
Thanks!
K
View 5 Replies
View Related
Jun 29, 2006
Hi all
I have a query linked to a report that prints a worksheet specific to a individual work item. This report/query picks up the Work_ID value on a form. I have 2 other forms displaying the same work with different amounts of detail. Rather than create a new report/query to run from each form, I am trying to use the one query/report from each form.
The problem is that I cannot get Access to recognise the Work_ID value from the other forms. I have tried the following:
In the Work_ID criteria field building an SQL statement as below
[Forms]![frmVCRUpdate]![Work_ID] Or [Forms]![frmVCRShort]![Work_ID] Or [Forms]![frmVCRLong]![Work_ID] - This does not work, it keeps asking for the frmVCRUpdate Work_ID value when I try to run the query from the other forms
Adding 2 extra Work_ID Values to the query and on the 2nd and 3rd criteria lines specifying that it look for the Work_ID value from the other forms but I get the error above.
Any suggestions on how I can make this work would be appreciated, I'm not sure what else to do.
Craig
View 2 Replies
View Related
Mar 9, 2007
Hi all,I posted something similar to this beforehttp://www.access-programmers.co.uk/forums/showthread.php?t=124289But i didnt get it figured out.Is it possible to use the same field for multiple criteria in a query?the one i would like to base it on is taskID.i just want the total time to be called admin time if taskid=2 and investigative time if taskid<>2.Ive tried it with single and multiple queries in one and am recieving errors with both. help is always appreciated!Woohoo for 100 posts!
View 3 Replies
View Related
Apr 22, 2008
Hi,
I'm trying to build a select query where it prompts the user for a few parameters. I've been having issues where people would misspell the vendor name and nothing pops up. I changed the vendor to Like [Vendor Name] & "*" but now I can't use the parameter of PO# without everything popping up. I have attached the SQL view. Thank you for any suggestions.
SELECT [Main Payment].[Batch#], [Main Payment].VendorName, [Main Payment].VoucherPrefix, [FY08 PAYMENT detail].VoucherNumber, [Main Payment].VoucherSuffix, [FY08 PAYMENT detail].Vchline1, [FY08 PAYMENT detail].PONo, [FY08 PAYMENT detail].InvoiceDate, [FY08 PAYMENT detail].InvoiceID, [FY08 PAYMENT detail].Amount
FROM [Main Payment] INNER JOIN [FY08 PAYMENT detail] ON [Main Payment].VoucherNumber = [FY08 PAYMENT detail].VoucherNumber
WHERE ((([FY08 PAYMENT detail].PONo)=[Enter PO#])) OR ((([Main Payment].[Batch#])=[Enter Batch #])) OR ((([FY08 PAYMENT detail].VoucherNumber)=[Enter Voucher #])) OR ((([Main Payment].VendorName) Like [Enter Vendor Name] & "*"));
View 3 Replies
View Related
Feb 8, 2006
Hello all,
I have a form that feeds a query with information, in my query I have this:
[Company] Like [Forms]![QBF_Form]![Sales] & "*" Or [Forms]![QBF_Form]![Sales] Is Null
When I fill in a value in the field e.g. "data*"the query brings up all information with the word "data" in it.
However, when I fill in something like "data* or vent*" I don't get any query results.
How do I change the query expression above so I can search for multiple criteria in one field using "or"?
Thanks a lot for your help!
View 2 Replies
View Related
Sep 16, 2013
I want to create a query with multiple conditions. Basically if the person Passes any of this trainings they need to show up in my query..how do you do it?
SELECT tblMasterUsers.userid, tblMasterUsers.Licenses, tblMasterUsers.firstname, tblMasterUsers.lastname, tblMasterUsers.email, tblMasterUsers.npn, tblMasterUsers.Region, tblMasterUsers.ABSID, CMPreport2014.[Ahip status], CMPreport2014.[LP Status] AS [AZ Product Training], CMPreport2014.[LP Status1] AS [CA Product Training], CMPreport2014.[LP Status2] AS [OR WA Product Training], CMPreport2014.[LP Status3] AS [Fraud Waster Abuse],
[code]....
View 1 Replies
View Related
Jul 20, 2006
Hi everyone,
My query is coming along nicely, but as always once one problem is solved you find another :rolleyes: !
My problem is that I have thus far specified criteria for the field OrdDeliveryCountry, but this field is not filled in unless the delivery address is different from the default address for the customer, therefore it is frequently blank and so the query wasn't finding all records, only those where the Delivery Address was specific to the order.
I want to use the IIf function to make an expression to say (in linguistic terms):
If OrdDeliveryCountry is blank, then use the country in the Customers table.
Sounds simple enough, but the criteria currently is:
WHERE (((ORDERS.ORDDELIVERYCOUNTRY) = "Austria"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "France"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden")
AND ((PRODUCTS.PRODUCTNAME) NOT LIKE "*Upgrade"
AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Repair"
AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Rpr"
AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Commission")
AND ((ORDERS.[DEMO/SALEID]) = 2))
So how do I combine the IIf(expr,truepart,falsepart) with "Is Not x Or x Or x"?I.E. I need to get it to exclude records where OrdDeliveryCountry does not equal one in the list, and if that is blank then the Country field in the Customers table does not equal one in the list?
My attempt is this, but I think I'm way off the mark
SELECT ORDERS.SHIPDATE,
PRODUCTS.[STANDARD TARRIFF NUMBER],
[ORDER DETAILS].[QUANTITY] * [ORDER DETAILS].[UNITPRICE] * (1 - [DISCOUNT]) * (1 - [SPECIAL DISCOUNT]) AS LINETOTAL,
[ORDER DETAILS].QUANTITY,
ORDERS.ORDDELIVERYCOUNTRY,
ORDERS.ORDERID,
[ORDER DETAILS].PRODUCTID
FROM CUSTOMERS
RIGHT JOIN (PRODUCTS
RIGHT JOIN (ORDERS
LEFT JOIN [ORDER DETAILS]
ON ORDERS.ORDERID = [ORDER DETAILS].ORDERID)
ON PRODUCTS.PRODUCTID = [ORDER DETAILS].PRODUCTID)
ON CUSTOMERS.CUSTOMERID = ORDERS.CUSTOMERID
WHERE (((ORDERS.ORDDELIVERYCOUNTRY) = IIF(ISNULL([ORDERS]![ORDDELIVERYCOUNTRY]),([CUSTOMERS]![COUNTRY] NOT LIKE "Austria"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "France"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden"),
(([ORDERS]![ORDDELIVERYCOUNTRY]) NOT LIKE "Austria"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "France"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden")))
AND ((PRODUCTS.PRODUCTNAME) NOT LIKE "*Upgrade"
AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Repair"
AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Rpr"
AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Commission")
AND ((ORDERS.[DEMO/SALEID]) = 2))
ORDER BY ORDERS.SHIPDATE DESC;
My thoughts:
Maybe I need to re-structure the WHERE clause?
Would it work if the IIf expresssion was in the SELECT part not the WHERE part?
I would really appreciate some help with this: I'm not sufficiently familiar with structuring statements as complex as this and I don't know all the syntax rules etc.
Thanks in advance!!
View 2 Replies
View Related
Jan 12, 2007
Hello,
Was wondering if there is a way without building individual update queries, to update info in one field that has multiple criteria ?
Basically I need to change/update daily multiple ID numbers to new ID numbers, long story on why this needs to be done but for now I need to do it this way.
Example: 12345 update to ABCDE, 6789 update to FGHI, etc. These ID's are all within the same field in the table.
It works fine running each ID one at a time but was wondering if it is possible to do all these updates within one query or code ?
Thanks for any assistance
View 6 Replies
View Related
May 29, 2007
Embarrassingly, I'm not even to the point where I can ask a specific question about the query(ies) I think I need.
Here's where I'm starting from and where I want to go... maybe it will make enough sense for somebody to point me in the right direction.
I have sales data that contains line items for every item sold over the past X number of years. For each line, there are six key attributes that I'm concerned with.
For simplicity here's a scaled down example of the data for each line.
PART, PART_FAM, TRANS_DATE, FAM_GROUP, TRANS_AMT, OVS_CODE
For each attribute, there are at least five possibilities.
I have been asked to find monthly sales trends on about 20 unique combinations of these various attributes. An example might be, the monthly sales totals for:
TRANS_DATE = 1/1/06 to 1/31/06
PART_FAM = PIN
FAM_GROUP = 01xx
OVS_CODE = 2
Then a SUM for the TRANS_AMT.
I've set up a query that can give me the information I'm looking for one month at a time, but I want to believe there is a way to have Access do some of the grunt work, rather than me having to change the variables one by one and copy/paste each result into my new file.
Is there some reading or previous posts I could review that might get me thinking about this in the right way?
Thanks in advance!
View 2 Replies
View Related
Jan 28, 2008
Hi All,
I am currently having problems with a multiple criteria query with combo boxes (see attached file).
The problem:
- When user defines all 3 criteria (e.g. selects Project: A, Category: Services and Equipment Type: Packages), the query returns a result - no problem here.
- When user defines no fields (i.e. all combo boxes empty), query returns all records - again, not a probelm.
- However, say a user wants to display all records belonging to just Project: A, for example, the query returns no records. Obviously, there a a number of combinations of this (another example would be a user wanting to show records from project: B, with Category: Equipment Type).
I have included the Is Null statement in the query but to no avail. Could anyone advise on a solution based on the attached database. Your help would be greatly appreciated!
Kind regards,
Stephen
View 2 Replies
View Related
Oct 14, 2005
I was searching through here looking for a solution to the following problem:
I am building a Capacity database for work. The requirements require that users be able to sort by numerous criteria (Forecast Date, Portfolio, Market, Bucket, Month), all from a simple form that will spit out the results. Rather than creating unique queries for each combination of criteria(way too many!) or creating the SQL text in VBA, I played around until I came up with the following(which may have been done already, but I can't find it on here, so I'm not claiming to be brilliant...LOL). I have dropdowns on my form for each sort criteria-(Forecast Date, Portfolio, Market, Bucket, Month)
Then I created a query and for each criteria, I put this in:
Like (IIf(IsNull([Forms]![frmWAOFAdjustments]![Bucket]),"*",[Forms]![frmWAOFAdjustments]![Bucket]))
So if the user leaves a dropdown blank, the query simply brings back all the results from that field (Like *).
The text in bold is simply replaced with the dropdown name for each segmentation criteria. Next to each dropdown is a button that clears the dropdown box and requeries the listbox with the results(in case the user doesn't want to remove a criteria. This makes running searches for a specific number of accounts easy for the user and easy on me..lol.
Hopefully this can help someone out with a similar problem. I have learned so much from this forum, I just wanted to give something back. If anyone has any questions on this, just let me know.
View 2 Replies
View Related
Sep 23, 2004
Alright I've been picking my brain for the past hour trying to figure this one out...
I have 3 tables...
1) Retailers-RetailerID, RetailerName, address etc. (500 entries)
2) Distributors- DistributorID, DistName (10 entries)
3) Uses- RetailerID, DistributorID
Sample data is as follows for 'Uses' table:
RetailerID DistributorID
1 1
1 3
2 1
2 6
3 4
3 5
4 8
I am trying to create a search form based on a query which will return retailer information for those retailer who use multiple distributors.
For Example...There will be 2 text boxes and a user can enter a distributorID in these text boxes. If only 1 distributorID is entered, it will display records for that one.
I have tried the following but I have been unsuccessful:
SELECT Uses.RetailerID, Retailers.Address, Count(Uses.RetailerID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE Uses.DistributorID = 1
GROUP BY Uses.RetailerID, Retailers.Address
HAVING Count(Uses.RetailerID) > 1
Is this possible to accomplish with the way I designed this database? If so, can I get a little advice? I know this is a long question that can't be answered in full but I would just need to basics in order to be on my way.
Thanks in advance!
View 1 Replies
View Related
Nov 18, 2004
I have a table(Product Change) with these fields:
Tracking Number
Approved (a check box)
Engineering (text box that represents department)
Purchasing (text box that represents department)
Quality (text box that represents department)
Production (text box that represents department)
Customer Service (text box that represents department)
I want my query results to show all records that have the "Approved" check box...checked, and then only the records that have one of the Department fields with a null value.
So I'm looking to see only records that are "Approved" and out of those....only the records with at least one department field empty(Null).
Any help doing this is SQL view would be great...or even design view.
View 2 Replies
View Related
Sep 27, 2006
I have a report that is based on a query. In the query, I have it prompt for a criteria for a field. It prompts for the name of an event, so only the participants of that event are displayed.
Also on that report, I have a subreport, which is based on that same query, but returns different data.
My problem is that since both the main report and the subreport are based on the query that prompts for input, it's prompting me multiple times for the same input when I view the report.
Is there a way to have Access use the same prompted input for both reports, so I don't have to enter it more than once?
View 1 Replies
View Related
Apr 23, 2013
I've been asked to get involved in some access development but don't seem to be get my dlookup syntax correct.I essentially want to lookup what salary band different employees are in.I have two tables:
Employee Table) has the fields: Name, Type, Salary
Salary Band Table) has the fields Employee Type, Salary Band, Lower range, Upper range..my query syntax is:
Code:
Band: DLookUp("[Salary Band]","Salary Band Table", [Salary] & " BETWEEN [Lower range] and [Upper range]" & "AND " & [Type] & " = [Employee Type]")
I can get the first criteria to work but can't get the second part to work - currently it produces an error.
View 1 Replies
View Related
Mar 29, 2014
I am working with a database which contains dates and a measurement value with data within the following fields:-
Record
SurveyID
DateTimez
mX
mY
mZ
Here is an snapshot of my main table "SurveyData"
Record|SurveyID|DateTimez|mX|mY|mZ
7194|A2|16-Mar-14|12797.4815|25781.8672|81.1848
7195|A2|17-Mar-14|12797.4863|25781.8672|81.1867
7196|A2|18-Mar-14|12797.4907|25781.8698|81.1875
7197|A2|19-Mar-14|12797.4844|25781.8642|81.1846
7198|A2|18-Mar-14|12797.4883|25781.8646|81.186
7199|G23|21-Mar-14|12419.8779|24822.6836|132.6177
7200|G23|12-Mar-14|12419.8928|24822.6812|132.6075
7201|G23|13-Mar-14|12419.9211|24822.6829|132.5915
7202|G23|14-Mar-14|12419.9392|24822.6837|132.5795
7203|G23|15-Mar-14|12419.9581|24822.684|132.5662
Record is the primary key which is a auto increment integer. The Survey ID is a text field that contains the name of the survey sensor, DateTimez is, we the Date and the time of survey and mX is the X coordinate. There is also mY and mZ, but once I can do the mE then its just a matter of duplication.
Now, I am trying to develop a query that will add some additional calculated fields to calculate the difference between the mX coordinates from the previous pickup, which is called 'dX'. The code will be duplicated to calculate the dY and dZ. I have managed to solve this using a Dlookup statement as follows:-
Code:
dX: [mX]-(DLookUp("[mX]","SurveyData","[Record]=" & [Record]-1))
This works fine, however I am struggling to make it work so it will only take the difference for each SurveyID. For example, the calculation does not calculate the difference between the X-coordinates between the Sensors A2 and G23,as they are not spatialy related. The code I am trying to use is shown below:-
Code:
dX: [mX]-(DLookUp("[mX]","SurveyData","[Record]=" & [Record]-1 & "AND [SurveyID]=" & (DLookUp("[SurveyID]","SurveyData","[Record]=" & [Record]-1))"))
I think I have got myself totally confused, but what I am trying to achieve is calculating the difference between the x coordinates from a previous record but only if the SurveyID matches on the previous record. I am using the record field to find the previous record.
View 5 Replies
View Related
Jul 29, 2015
I am trying to set up a query by form with multiple criteria.For the majority of the criteria I'm using the format: Like [Form]![formName].[txtInputboxName] &"*" .
I copied the Like (...) &"*" from someone else in order to allow for multiple, optional criteria, which does do the trick, however I don't understand why.
I now want to set up a criteria on my query to search for values between two input values to gives the records in the range e.g between 50 and 100 Following the above format, I want to put something that achieves this:
Between Like [Form]![formName].[txtInputboxName1] &"*" AND Like [Form]![formName].[txtInputboxName2] &"*"
with Inputbox1 being 50 and Inputbox2 being 100, however that code doesn't work and I don't know how to get around it as it keeps giving the 'incorrect syntax' error.
View 5 Replies
View Related
May 2, 2013
I am designing a database which keeps track of door access levels at a college, using Access 2010. A door access level is programmed to a key card, which grants access to a number of different doors throughout the college.
So, what I have is an "LevelID" and a "DoorID", where each LevelID has zero to many DoorIDs associated with it, as well as each DoorID has zero to many LevelIDs associated with it.
example table:
LevelID DoorID
1 1
1 2
1 5
1 6
etc..
All the data has been inputted into Access 2010 successfully, however I am having difficulty in developing one of the main functions of the database..
What I want to do is have a checklist of each DoorID displayed on a form, and when any combination of DoorIDs are checked, Access will search to see if a LevelID is associated with that combination. This information can tell me whether a new LevelID is needed to be created.
View 14 Replies
View Related
Jul 3, 2013
I have a query thats outputs my costs on a project divided into 50 categories. Additionally, each project can be divided into 3 stages, each with the same 50 cost categories. I have been using a query that prompts the user which stage they would like to look at, and it works great.
However, it would be useful to be able to see all stages at once, next to one another.
So, essentially, i want to be able to input the stage parameter in the criteria box for three fields in the query, so instead of showing the costs for one stage at a time, it will show all three(and a summation across all stages), without a prompt.
It works when i set the one field manually to the first stage, but when i add additional fields, it returns a blank query.
View 1 Replies
View Related
Dec 4, 2007
Hi All,
I have fought hard to try and figure this out and I have gotten 50% there.
I have a query that generates the following calculated fields through numerous calculations etc....:
[De-Merit Marks]
[No Del with Major Faults]
Now the way it should work is that a supplier gets a rating (A-D) depending on both their De-Merit Marks score and the amount of deliveries with major faults. I used the following function in the query and it does the de-merit mark grading.
Rating: IIf([qry_vend_rating2]![De-Merit Marks]<=10,"A",IIf([qry_vend_rating2]![De-Merit Marks]<=30,"B",IIf([qry_vend_rating2]![De-Merit Marks]<=50,"C",IIf([qry_vend_rating2]![De-Merit Marks]>=51,"D",""))))
However I have no idea how to integrate the Major Faults part.
Basically:
0-10 = A
11-30 = B
31-50 = C
51-100+=D
If the supplier has one major fault, the rating should drop one from that reported using just the de-merit score. Two means it drops two ratings (so if an "A" on just the demerit, they would drop to a "C" and so on. Obviously a supplier cannot drop below a "D".
Can anyone suggest how these maybe acheived? I fear it requring VB code, but I am not that good with VB hence why I have relied on queries for this.
TIA
View 2 Replies
View Related
Apr 21, 2013
I need to update the periodtype field in my table depending on different values in the field Formtype- I am looking to do this without having to use VBA. I have the following fields in a table - I want to update the value of the field Period_type as follows -
When Formtype is 10-Q, update Periodtype to "Quarterly"
When Formtype is 10-K, update Periodtype to "Annual"
The current value of Periodtype for both formtypes is "Semiannual"
Can I do this using only update query?
View 2 Replies
View Related
May 29, 2014
I have a form where varying layers of information can be entered. In some cases, a user may know all information or only part. If i tie the query to the form, each field has to be populated for it to work. how do i set the query up to effectively use the information available and not require all information?
I have tried setting VBA code as follows:
If Me.Combo1 = Null Then Me.Combo1 = "*"
However, when i do this, it updates the form field with an asterick/wild card but does not include in query. yet, when i update the form fields with data (not asterisk/wild card) the query runs.
View 13 Replies
View Related
Feb 10, 2014
I need to design a query that contains about a dozen fields for stationery items along with another dozen fields (checkboxes) to show whether these items were delivered (Yes, I know, not great db design).
I ran out of space with the the query criteria window.
View 6 Replies
View Related
