I have a query that is working, but would like to add some extra complexity to it.
The query currently uses a search form for input with multiple fields, and displays all the results perfectly. I have a tick box which I can then filter the results so it only displays results where there is an entry in "PhoneMobile1". This is working, however I want to expand this filter so it can use a combo box if possible.
Currently I have the following code from a tick box:
IIf([forms]![ISISNavigationMain]![navigationSubform].[Form]![txtHasMobile]=-1 And [Candidates].[PhoneMobile1] Is Not Null,True,False)=[forms]![ISISNavigationMain]![navigationSubform].[Form]![txtHasMobile]
The issue is that there are three fields for Phone Numbers (PhoneMobile1, PhoneMobile2 and PhoneMobile3), and I'd like this query to be able to filter on these columns also.
e.g.
Combo box values:
Has Mobile, No Mobile, All
the "Has mobile" choice should display all results if there is an entry in any one or more of the PhoneMobile fields, "No Mobile" selected should display all results where there is no entry in any of the three Mobile fields, and All should display all results.
I've got two tables in my Access 2010 database - both are identical in structure, the difference being one (Dove) contains old data and the other (Dove Data File) contains updated data. The primary key for these tables is "TowerBase".
I want to use an update query to update only the changes from the Dove Data File table into the Dove table - but for certain records only.
I've managed so far to get a query which displays only records that have changed in a particular field (Bells). Up to here I think is all good...
SELECT [Dove Data File].* FROM [Dove Data File] INNER JOIN Dove ON [Dove Data File].TowerBase = Dove.TowerBase WHERE (((Dove.Inactive)<>True) AND ((Exists (SELECT NULL FROM Dove WHERE [Dove Data File].TowerBase = Dove.TowerBase AND [Dove Data File].Bells <> Dove.Bells))=True));
But now I want to add in criteria and this is the bit I'm struggling with. I need this query to now display only records where there is no associated record in my Visit Dates table. In other words, the Visit Dates table has the "TowerBase" ID along with a visit date. These records I do not want to see in the query, as I don't want to update any changes for these from the Dove Data File table to the Dove table.
All using access 2010. I've have a query in a database that is given an error: query too complex or system exceeded resources. I've been running this query once a week and all of a sudden; errors. I have changed one unrelated table. I've gone back to an old database and its giving the same errors. database on the server is giving off this error and it's only when I run this particular query.
I have a shell of my database in the below link for reference. I am trying to create a query for a report that will allow me to track charges and payments per client. Each charge has a ChargeID and each payment has a PaymentID. Multiple payments can be applied to one charge so the same ChargeID can show up multiple times with a different PaymentID. Payments that are catagorized as Third Party Payments will have not only a PaymentID, but a TPPaymentID.
What I need to do in the query and final report is track the total charges (even though the ChargeID may show up multiple times, I only want the charge itself to be calculated once and the charge to only be listed once for each client) and track the total payments as well as sum the total payments for each charge for each client.
Here's the situation: I have a list of Crew Leaders. Each crew leader has the option of being the leader of up to three different types of crews, which is determined by checkboxes. In a separate form, I would like to do the following:
The first combo box contains a list of the three types of crews. Once an option is selected, I would like only the Crew Leaders' names corresponding to that selection to show up in a second combo box. However, they are not ONLY part of this selection, necessarily. They may also be a part of a different selection, as they may be the leaders of one, two, or all three types of crews. The third combo box I think I can figure out (the first box filters different services that the crew can provide, but it is irrespective of the name of the Crew Leader, so the services are crew TYPE specific, not CREW specific).
I've done it where the first box filters the second box, but only when the selection in the first determines an exclusive set in the second. What I'm trying to do is have the first box create a non-exclusive set in the second box.
Ex: Crew Type: A Crew Leader (Selection pool): Joe, Bob, Jim, Jerry, Phil, Mark
Crew Type: B Crew Leader (Selection pool): John, Bob, Jim, Mike, Barry, Dan, Mark
Crew Type: C Crew Leader (Selection pool): Rob, Jack, Jim, Joe, John, Mark
Anyone have any ideas? So far you guys have been amazing helps.
I am trying to build a database to keep records of devices that I have.
The basic ERD is as so: hXXp://img50.imageshack.us/img50/3082/erdkt3.gif I want to create a form where the user selects the building, then is given a list of rooms in that building, the is given a list of Racks in that room.
I have tried added fields Bldg and Room in the device table. The bldg drop down would SELECT Bldg_ID, Bldg_Name FROM bldg;
Then in Room i did a: SELECT DISTINCT [Room].[Room_ID], [Room].[Room_Name] FROM Room, Device WHERE Device.Bldg_ID=Room.Bldg_ID;
Then in Rack I did: SELECT DISTINCT [Rack].[Rack_ID], [Rack].[Rack#] FROM Rack, device WHERE Rack.Room_ID=Device.Room_ID;
This work except for one flaw. there where statement limits what room/rack for all bldg's or rooms in the table not just that current row.
I then started to think I need to do this in a form. But i run into the same problems. I hope this makes sense.
Send me a PM and I can send you a copy of the file.
Alrighty... My DB contains the following tables: VacTab - Keeps track of crew members on vacation and people covering their shifts. CrewRost - List of all the crew members and what crew they belong to. CrewSched - The complete schedule of what crew is working which shift (Currently programmed out until Feb 2008)
Now, I need a query which totals regular crew hours for individuals and the overtime hours they cover for other crew members on vacation. And puts the overtime hours in the correct day/shift.
I have a working query which equates how many hours an individual works with their crew, based on the CrewSched Table. This was rather complex, because we work a combination of 8 hour and 12 hours shifts.
I also have a working query which puts the overtime hours of each crew member into the correct day/shift that they cover for crew members on vacation.
What I need is a way to combine these into a single report, where I can use conditional formatting to highlight the overtime hours. The problem is, if I use a union query, the report will treat these values as such, and not highlight the overtime hours.
If anyone has an idea as to where I can start, it would be greatly appreciated.
I have 4 queries that run and prepare a temporary table for which I then have a sales report generated . THis report has been active for over a year exactly as is but now the past week I am getting an error message that says "The expression is typed incorrectly or is too complex to be evaluated".I used the query designer in access to create them, here they are in SQL VIEW
My Make Table Query:
SELECT [Job Table].[Job Number], [Job Table].[Date Sold], Commission.originalCRate, Customers.[Last Name], [Job Table].Salesperson, [Job Table].Split, [Job Table].[Job Type], Commission.SaleAmount, Commission.JobCost, Commission.SPR INTO [New Sales Report Temp Table] FROM (Customers LEFT JOIN [Job Table] ON Customers.[client id] = [Job Table].[Client id]) LEFT JOIN Commission ON [Job Table].[Job Number] = Commission.[Job Number] WHERE ((([Job Table].[Date Sold])>#12/31/2012#) AND (([Job Table].Split)=No));
Query 2:
INSERT INTO [New Sales Report Temp Table] ( [Job Number], [Date Sold], [Last Name], Split, SaleAmount, Salesperson, [Job Type], OriginalCRate, SPR, JobCost ) SELECT [Job Table].[Job Number], [Job Table].[Date Sold], Customers.[Last Name], [Job Table].Split, [saleAmount]*[SPr] AS TotalSale4, [Job Table].Salesperson, [Job Table].[Job Type], Commission.OriginalCRate, Commission.SPR, [jobCost]*[SPR] AS JCost FROM (Customers LEFT JOIN [Job Table] ON Customers.[client id] = [Job Table].[Client id]) LEFT JOIN Commission ON [Job Table].[Job Number] = Commission.[Job Number] WHERE ((([Job Table].[Date Sold])>#12/31/2012#) AND (([Job Table].Split)=Yes));
[code]...
When I run the queries as a query and view data in datasheet they all work, however when I run the report I get the error message.My Access DB is a front end connected to an SQL server backend. I have never created queries in SQL just in Access.
I am accessing an oracle database that has several thousand records in it. I am quering for specific requirements, but would like to save my query results in a local access database for faster searching capabilities. Is there a way for me to set up a query that will go out to my oracle table files, select the records that pertain to my search criteria, and add records to locally stored tables without duplicating itself each time that I run the query? I would appreciate any assistance in this matter. Thanks for your help!
i just know some query designing and some amount of VBA for making small event procedures.I want to know how we can use VBA when our queries becomes complex to replace the queries and to extend the functionality.how to use recordsets in a VBA function.
Context is I have a TABLE 1 that has a record for every package shipped with various details about it, include how it shipped, and the date. Table 2 is a key to go from the ship method to a better grouping we deem service level. Table 3 is a Fuel Surcharge (FSC) Table. It includes various FSC % that vary by different service levels and different date ranges. In the end I need to know the amount of pkgs shipped (by month and servicelevel) and the relevant FSC.I have several tables in my database but we should be able to focus on 3 to explain my situation and only the relevant fields:
Table 1 is Left join on Table 2 based on ShipVia Table 2 is Left join on Table 3 based on FSCType The issue is Table 3 is dependent on Table1.ShipDate being between the date ranges as well.
There are overlapping date ranges, however they are differentiated by the FSCType field.The only semi-feasible solution I currently have is to expand Table 3 to include every date, and not date ranges. This will create an issue due to size of date ranges etc.
Hello , I had incountered with a huge problem in my project and I need your help guys. I have a huge table contains alot of data about many people- I wanted the data will be checked and sent to a query. Here is an example for a little project beacuse I couldn't have the big one. (This example works fine) My big project is pretty the same but after I try to get the query out I get an error that the query is too complex....(It's really too big when you have 20texts to be checked with 9000 fields) If you check the query fields you may see how the OR is getting separted and because every text has OR statement everything is hanged by everything. Someone told me that I can't use query options and mannge it and I should use VB SELECT option - but I can't make it work too. So I can't show you the real example at all beacsue I can't have it to my computer and It will take alot of time to have 9000 different cells ; SO if someone knows what I am talking about and ever encoutered it , I would be really thankful ! (Also - You may see some problems with the OR ("") but I mannged it to work with the BIG project so it doesn't matter) I don't want you to focus the conditions but just the problem itself ...." the query is too complex"...
This one is a pain in the but. I tried everything, but why would it just won't work. It always says the query is to complex. Have included the database, anyone a solution?
I need to perform a query on a database (not designed by me) that is not normalized. In fact it is only one table with numerous fields (many of them Date/Time). I need to query the table based on the date fields only.
Basically the table is used to track when specific functions are completed to determine the time required to perform the individual tasks (accuracy to one day is acceptable) and find the ones which are falling behind. Since several individuals are required to complete each project each step has an average value (based on historical data.)
The following is a short narrative:
1. Step 1 is scheduled for 1/5/05 and the task is not started untill 1/7/05. This is not acceptable. There is a 1 day window. I need to flag this record (via report) if the start date exceeds the schedule date by more than 1 day. If a start date has been entered this record does not need flagged (regardless of the alloted time).
2. Step 2 is based on the amount of time alloted to complete the task once started. If the task takes more than 2 days the project needs to be included in the query for the report. As above, if the date is entered the record is not needed since there is no need to try to determine the when the project will start.
3. Step 3 measures the number of days from the project completion untill the product is sent to the central office. If the time exceeds 2 days the record needs to be included in the dataset. Again, once a date is entered in the received field there is no need to include the record.
There are a couple more steps but they follow the same criteria as the first three.
I have not been able to figure out how to get these requirements entered into the query design view.
The query I'm trying to get is: Who owes me money for the service and How much.
Each car had several treatments in the past and some have missed a payment or I didn't notice and just issued a new order.
I need a query that runs through the entire database, does a summary of all the amount owed to me per Car, and then deducts the total payments made per Car. If the balance is > than Zero,. Show me that car and the bottom line amount.
Hi guys, long time surfer, first time poster here :)
We use a prehistoric Process Plant design software package, and it stores all its information in Oracle 8i.
A report I need to pull takes data from a stack of tables, each with a unique number.
PDTABLE_113 contains a list of models, each model having a unique PARTITION_NO.
Every PARTITION_NO entry has a matching table called PDTABLE_21_XXXX where XXXX is the PARTITION_NO of the model. Each PDTABLE_21_XXXX has a row for every piece of equipment in the relevant model.
PDTABLE_21, for argument sake, has the columns EQUIPMENT_ID, EQUIPMENT_DESCRIPTION, and EQUIPMENT_STATUS.
Is there some way to query the database so I get something that looks kinda like this:
this shows that I can have multiple entries in a day for a particular serial number.
I need to select ONLY ONE serial number and corresponding data for each day (or week).
I tried to format the date to remove the time and then select the distinct date (so 1 record a day for each serial was displayed), this worked. BUT I could not link it successfully to other tables because I had to format the corresponding match date to avoid a type mismatch thus invalidating what i was doing by selecting the distinct record.
Here is the original query i was using that selected a range of dates (which included multiple dates in a single day with a single serial).
SELECT TBLRemoteUnitInfo.TankSize, TBLRemoteUnitInfo.TankName, * FROM TBLRemoteUnitRequests, TBLRemoteUnitInfo WHERE (TBLRemoteUnitRequests.RemoteFeildUnitID = TBLRemoteUnitInfo.RemoteFeildUnitID) AND (TBLRemoteUnitRequests.SerialNumber=:SerialNumber) AND (TBLRemoteUnitRequests.DateReceived BETWEEN :Datestart AND :Dateend) ORDER BY TBLRemoteUnitRequests.UnitRequestID';
how can i take a sample of the range of dates (ie 1 a day/week or month), i assume i need to create a filtered table via query first then query that table.
Its killing me!
if i am being unclear please let me know and ill try to clarify
I have come to a dead end with my query. Any help would be really appreciated.
I have a query which i have written:
SELECT TblBurnleyWwTw.MetricID FROM TblBurnleyWwTw WHERE (((TblBurnleyWwTw.[Data Source])="OMS")) OR (((TblBurnleyWwTw.TAGFunction)="CHP")) GROUP BY TblBurnleyWwTw.MetricID, TblBurnleyWwTw.[Metric Required], TblBurnleyWwTw.CALCULATIONS, TblBurnleyWwTw.PIPointSource, TblBurnleyWwTw.PILoc1, TblBurnleyWwTw.[High Level KPI] HAVING (((TblBurnleyWwTw.MetricID) Like "130*") AND ((TblBurnleyWwTw.[Metric Required])="-1") AND ((TblBurnleyWwTw.PIPointSource)<>"L"));
This retreives all codes relating to what i want. The problem is, I need this data to try and match within another column of calculations E.g. Data retreived from query I have may be 13001, 13002, 13003. What I would like to do with this data is to lookup in a calculations column if any of the above data matches to bring back the metric ID which is realted to it?
Hope i have stated this clearly enough for anyone to understand???
As i say any help or recommednations for a solution would be really appreciated
SELECT [UK Table].[Business Name], [UK Table].[Business Type], [UK Table].Address, [UK Table].City, [UK Table].Country, [UK Table].[Telephone Number], [UK Table].[Website Address], [UK Table].[Email Address] FROM [UK Table] WHERE ((([UK Table].[Business Name]) Like [Forms]![frmNz]![txtBusinessName] & '*' Or [Forms]![frmNz]![txtBusinessName] Is Null) AND (([UK Table].[Business Type]) Like [Forms]![frmNz]![txtBusinessType] & '*' Or [Forms]![frmNz]![txtBusinessType] Is Null) AND (([UK Table].Address) Like [Forms]![frmNz]![txtAddress] & '*' Or [Forms]![frmNz]![txtAddress] Is Null) AND (([UK Table].City) Like [Forms]![frmNz]![txtCity] & '*' Or [Forms]![frmNz]![txtCity] Is Null) AND (([UK Table].Country) Like [Forms]![frmNz]![txtCountry] & '*' Or [Forms]![frmNz]![txtCountry] Is Null) AND (([UK Table].[Telephone Number]) Like [Forms]![frmNz]![txtTelephoneNumber] & '*' Or [Forms]![frmNz]![txtTelephoneNumber] Is Null) AND (([UK Table].[Website Address]) Like [Forms]![frmNz]![txtWebsiteAddress] & '*' Or [Forms]![frmNz]![txtWebsiteAddress] Is Null) AND (([UK Table].[Email Address]) Like [Forms]![frmNz]![txtEmailAddress] & '*' Or [Forms]![frmNz]![txtEmailAddress] Is Null));
What is wrong? I have attached the database. The form frmNz is what I want to work. I should be able to enter company information into at least one field, allowing me to retrieve the specific company data from the UK Table in the subform.
I have three tables called, Clients basic details, Episode and Modality. There are some 56 fields in these tables. Once a month I run a query on these tables. The data is then uploaded to another site via the internet. I have now been asked to collect data into another table called TOPS which has some 23 new fields in it. The problem I have is, they want none of the TOPS data to be on the same line as the Modality Data. For instance, there are 79 fields in the query which must conform to certain parameters before the data can successfully uploaded, the query must show lines of data for all the 79 fields, but if any line has TOPS information the line cannot have Modality information in it and vice versa. I think some of the data that would be collected will be duplicate, i.e. from the episode and clients basic details tables. The query must show all 79 field headings. Any line of Data in the query result that has data from the Modality table and data from the TOPS table can only show the returned data from one of these tables, the values from the other table must be left blank and vice versa. For Instance say From the clients details table we show the clients name, from the episode table we show the number of children he has, from the Modality table we show he has structured intervention, we then must show blank records from the TOPS table. Then on another line the query must pick up the data from the TOPS table, episode table and clients basic details table and leave the Modality table fields blank. A lot of the info from the Clients and episode table will be duplicated but on the different lines.
I am trying to create ranges in data in order to create a graph. I have the following IIf statement that access says is too complex and also Im getting an error saying that it is too long to edit so does anyone have any ideas how to do this with out these problems. I need to get to $25000 $30000 in increments of $1000.
IIf([Actual$/Mile]<1000,"$0 to $1000",IIf([Actual$/Mile]>=1000 AND [Actual$/Mile]<2000,"$1000 to $2000",IIf([Actual$/Mile]>=2000 AND [Actual$/Mile]<3000,"$2000 to $3000",IIf([Actual$/Mile]>=3000 AND [Actual$/Mile]<4000,"$3000 to $4000",IIf([Actual$/Mile]>=4000 AND [Actual$/Mile]<5000,"$4000 to $5000",IIf([Actual$/Mile]>=5000 AND [Actual$/Mile]<6000,"$5000 to $6000",IIf([Actual$/Mile]>=6000 AND [Actual$/Mile]<7000,"$6000 to $7000",IIf([Actual$/Mile]>=7000 AND [Actual$/Mile]<8000,"$7000 to $8000",IIf([Actual$/Mile]>=8000 AND [Actual$/Mile]<9000,"$8000 to $9000",IIf([Actual$/Mile]>=9000 AND [Actual$/Mile]<10000,"$9000 to $10000",IIf([Actual$/Mile]>=11000 AND [Actual$/Mile]<12000,"$11000 to $12000",IIf([Actual$/Mile]>=12000 AND [Actual$/Mile]<13000,"$12000 to $13000",IIf([Actual$/Mile]>=12000 AND [Actual$/Mile]<13000,"$12000 to $13000",IIf([Actual$/Mile]>=13000 AND [Actual$/Mile]<14000,"$13000 to $14000",0 ))))))))))))))
Hello everyone! i hope someone can help me with either: 1. writing a query to get the results needed (will explain below) 2. write VBA to be used in query to get the results needed 3. or change table(s) to be able to obtain the data in all areas to get the results needed.
The result needed: How can I get the result of ONLY when there's an exact match of the combinations and not all combinations of the matches?
I have a GuitarOptionDetails and it's linked (LEFT OUT JOIN) to a ProgramCodes. so the basic result here is displaying correctly. it's showing everything in my GuitarOptionDetails and only where there's matches from the ProgramCodes. Linked on Guitar and Option as these fields are in both sides. The ProgramCodes has the "Code" for the guitar and guitar / option combinations.
Issue, and why I'm seeking help ... i need to somehow change that so it will only show the set of results where the combinations matches.
example here's the "raw" data from ProgramCodes table
GuitarOptionDetails is showing the Codes for ComboID's 2, 3, and 4 because an invoice for AE185 has BOTH 38 and BB. Again, the basic LEFT OUTER JOIN query is showing all the Codes for AE185 where there's 38 or BB.
But instead, i need the result to show only the ComboID 4 Codes and not to repeat the same Codes for the ComboID 4 Codes.
I truly hope I made sense and there's a solution for this. I have no idea how to look it up to see if there's any previous posting for this or something like this, etc ...
I have a query that when I run it normally (just click on it) then it runs fine. (It is a union query, getting it's data from 8 other queries (who has their dependancies)
But when I want to run a report from it, Access gives me an error saying "query is too complex".
I am flattered, but I would prefer access to work than say I write stuff that is too complex for it. :cool:
Any ideas?
I am confused by the fact that it runs when I double click the query, but the report bugs it out.
I need to sort my query in a rather complicated way, and haven't been able to get the examples I've found on here to work.
I have the following fields [Category] [Name] [Page]
Here is what I need. Where [Category] = "Table of Contents" Sort by [Name], Then by [Page] Descending Else Sort by [Name], Then by [Page] Ascending
All records will be queried at the same time, so "Table of Contents" categories will be included in the same query as other records, but need a different sort order. I know this is stupid, but leave it to government to impose some nonsensical rule on you.
I'm hoping somebody out there might be able to point me in the right direction for solving this problem.
Im developing a hire stock control database and have a table that contains all the order reference numbers and in and out dates.
What i am trying to do is structure a query that searches for all instances of a shipping out/in date that fall within date values already stored in the source table.
e.g. Table values
Out .................In 1/11/05............10/11/05 3/11/05............5/11/05
There is a mapping table called, lets say tblMappingTable which has the following fields:
Contract0001 - Contract0016 1 - 11 2 - 12 3 - 13
Basically I need to write a query which interrogates all the records PA0001 checks the mapping table and gives me all the records in PA0016 which do not have the expected mapping result.
So based on the above data I would expect it to return only the non-matched data, which will be:
I would probably need to do another query which does the exact same thing but the other way around. It would need to interrogate all the records from PA0016 and using the mapping table check the records in PA0001. In my example above I would get the exact same result, but I suspect with large amounts of data, I would get different results, especially if the number of records were not the same in both tables.
I suspect I would need to write some SQL code or VB code to extract the above.
If one of you kind folk could give me some basic pointers that would be great.
