Query Is Too Complex... On Report, But Query Runs Fine
Oct 18, 2005
Hi...
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.
-Reenen
View Replies
ADVERTISEMENT
Jul 14, 2005
Complex Report Query (CrossTab)
Hey everyone!
I need some help here, I am in the final design stages of my database… I am working on creating some reports and I need some help getting the info I need in the right place. Let me summarize the problem, then I will give you a rundown of my table structure and what I need to do, finally explain how far I am at this point.
I need to create, a complex query for use of a report I am creating. Here is a summary of what info the final query needs to include:
CurrentRank / LastYrRank / BusType / BusName / BusPhone / etc. / Cat1 / Cat2 / Cat3 / etc.
*******************
Here is my table Structure
*******************
|tblBusiness
|--------------
| &BusinessID
| BusinessTypeID-----1---1--1
| BusinessName | | |
| ….(etc.) | | |
| | |
|tblBusinessType | | |
|-------------------- | | |
| &BusinessTypeID--%/ | |
| BusinessType | |
| ….(etc.) | |
| |
|tblCategory | |
|------------- | |
| &CategoryID---------1 | |
| CategoryName | | |
| | |
|tblBusinessCategory | | |
|------------------- | | |
| &BusinessID----------- |--%/ |
| &CategoryID-------- %/ |
| &CopyYear |
| Value |
|
|tblRank |
|--------- |
| &RankID |
| &BusinessID------------------%/
| BusinessTypeID
| &Year
| Rank
1= one
%=many
&=PrimaryKey
Now, each BusinessType has several associated BusinessCategories (3-8)… all of the businesses use that set of categories to create a unique set of categories along with a value and a year. So, all businesses with a common businessType will have a common set of categories and values, and will have a set for each year
Additionally each business within a businessType is ranked by those categories for each year. (I am currently setting the business rank by year manually, though I will eventually create a module to do it for me.)
With me so far?
*************************
Here is The Report I need to make
*************************
BusinessType
|----------------------------------------------------------------------|
|Rank | |Telephone| | | | |
| 2006| Business | Fax | $ sold lt. yr| Type Meal | Owner | |
| 2005| Address |e-mail/web|(Category*)| (Category) | Category | etc.|
|-----|----------|-----------|------------|-----------|----------|-----|
| | Bus. 1 | phone | 23,405 | dinner/ | John Doe | |
| 1@ | Street | fax | | dessert | | |
| 1^ | State/Zip | email/web | | | | |
|-----|----------|-----------|------------|-----------|----------|-----|
| | Bus. 2 | phone | 20,185 | breakfast |Jane Smith| |
| 2 | Street | fax | | lunch | | |
| 4 | State/Zip | email/web | | | | |
|-----|----------|-----------|------------|-----------|----------|-----|
| | Bus. 3 | phone | 18,958 | lunch | Ron Man | |
| 3 | Street | fax | | | | |
| 2 | State/Zip | email/web | | | | |
|-----|----------|-----------|------------|-----------|----------|-----|
| | Bus. 4 | phone | 17,432 | lunch / | Bob Neff | |
| 4 | Street | fax | | dessert | | |
| 3 | State/Zip | email/web | | | | |
|-----|----------|-----------|------------|-----------|----------|-----|
| Footer |
|----------------------------------------------------------------------|
*=Ranking Category
@=currentYearRank
^=PreviousYearRank
Hope this is helpful… almost done…
*****************
Here is where I am at…
*****************
I have a query named QallBusinesses which contains all tblBusiness linked to tblBusinessCategory.
Then I have a Cross Tab Query named CTQGolfCourses (for instance) that limits the year and business type which gives me the below results
BusType / BusName / BusPhone / etc. / Cat1 / Cat2 / Cat3 / etc
This is great… it is almost finished… Now, I need the list to include the current year and previous year rank (lets say 2006, 2005 respectively) and sort the list of businesses according to the current year rank… (in case of rank ties sorting by business name) to end of with this:
CurrentRank / LastYrRank / BusType / BusName / BusPhone / etc. / Cat1 / Cat2 / Cat3 / etc.
That is where I get stuck! Can I create a nested Crosstab?
I have tried to create a query using CTOGolfCourses and tblRank linking BusinessID then crosstab that query to get the results I want, however it just ends up creating a records for each business type to have every available rank with every available year, so when I crosstab it, it ranks all the businesses as #1 for both years.
If I can lay this out manually, I know that I can code this using VBA to generate the exact reports I need, using the varying criteria, however I cannot quite get the results I need manually.
Wow, this is a long post! Thanks for hanging in there! I really appreciate any help you can give on this complex problem!
View 3 Replies
View Related
Sep 25, 2006
I have a parameter query that was discussed a while back Here (http://www.access-programmers.co.uk/forums/showthread.php?t=89095&highlight=option+group) where I needed help with a nested IIF statement returning multiple values. This has been working great until I was asked to add a couple more processes to the list. Now I get an error saying expression too complex. Ok, well I agree. So I decided to look into calling a select case function from the query to perform this task. In the original discussion about this query, it was discussed, but I was already satisfied with the IIF statement and moved on. I'm a complete newbie when it comes to passing a parameter to a function in order to return the records I would like in my query. Please help me get my head around this.
I'm ready to rethink this whole parameter query and would appreciate some input on using a case statement. Here is the sql for this query:
SELECT tblInspections.InspectedQty, tblInspections.RejectedQty, tblInspections.AcceptedQty, tblInspections.InspectDate, (DatePart("ww",[InspectDate],2)-1) AS Week, tblCustImport.CustomerID, tblInspections.InspectID
FROM tblWorkCenters INNER JOIN (tblProcess INNER JOIN ((tblCustImport INNER JOIN tblMaxImport ON tblCustImport.CustomerID = tblMaxImport.[Customer Number]) INNER JOIN (tblInspections INNER JOIN tblInspectDetail ON tblInspections.InspectID = tblInspectDetail.InspectID) ON tblMaxImport.[WO#] = tblInspections.[WO#]) ON tblProcess.ProcessID = tblInspections.ProcessID) ON tblWorkCenters.WorkCenterID = tblInspectDetail.WorkCenterID
WHERE (((tblInspections.InspectDate) Between [Forms]![frmRejectopt]![txtStartDate] And [Forms]![frmRejectopt]![txtEndDate]) AND IIf([Forms]![frmRejectopt]![optWC2]=1,[tblWorkCenters]![WorkCenterID] = ("S"),
IIf([Forms]![frmRejectopt]![optWC2]=2,[tblWorkCenters]![WorkCenterID] = ("T"),
IIf([Forms]![frmRejectopt]![optWC2]=3,[tblWorkCenters]![WorkCenterID] = ("M"),
[tblWorkCenters]![WorkCenterID])))
AND
IIf([Forms]![frmRejectopt]![optgrpWC]=2,[tblProcess]![ProcessID] In (1),
IIf([Forms]![frmRejectopt]![optgrpWC]=3,[tblProcess]![ProcessID] In (2),
IIf([Forms]![frmRejectopt]![optgrpWC]=4,[tblProcess]![ProcessID] In (5),
IIf([Forms]![frmRejectopt]![optgrpWC]=5,[tblProcess]![ProcessID] In (9),
IIf([Forms]![frmRejectopt]![optgrpWC]=6,[tblProcess]![ProcessID] In (10),
IIf([Forms]![frmRejectopt]![optgrpWC]=7,[tblProcess]![ProcessID] In (11),
IIf([Forms]![frmRejectopt]![optgrpWC]=12,[tblProcess]![ProcessID] In (4),
IIf([Forms]![frmRejectopt]![optgrpWC]=8,[tblProcess]![ProcessID] In (3),
IIf([Forms]![frmRejectopt]![optgrpWC]=9,[tblProcess]![ProcessID] In (6),
IIf([Forms]![frmRejectopt]![optgrpWC]=11,[tblProcess]![ProcessID] In (7),
IIf([Forms]![frmRejectopt]![optgrpWC]=10,[tblProcess]![ProcessID] In (8),
IIf([Forms]![frmRejectopt]![optgrpWC]=13,[tblProcess]![ProcessID] In (1,9,10,11),
[tblProcess]![ProcessID])))))))))))))
GROUP BY tblInspections.InspectedQty, tblInspections.RejectedQty, tblInspections.AcceptedQty, tblInspections.InspectDate, (DatePart("ww",[InspectDate],2)-1), tblCustImport.CustomerID, tblInspections.InspectID
ORDER BY tblInspections.InspectDate, (DatePart("ww",[InspectDate],2)-1);
TIA!!
View 2 Replies
View Related
Feb 28, 2007
Dear pro,
I am having difficulties running a simple query that it runs all day and I have to kill it at the end of my date because it does not finish. Here is the statement that I would like some suggestions on how to improve it for faster results:
SELECT code, acct nbr, date, user, sum(amt)
FROM Tbl a
WHERE code IN
('AB',
'BC',
'CD',
'AA',
'BB',
'CC',
'ZZ'
)
AND date BETWEEN TO_DATE ('20070101', 'YYYYMMDD')
AND TO_DATE ('20070131', 'YYYYMMDD')
group by code,acc nbr,date,user
thank you...in advance for all your help..
Wilman.
View 1 Replies
View Related
Aug 8, 2006
I have a table with a rotating work order number (from 1 to 9999 then starts over) and because there may be more than one record with the same work order number I need to find the most recent one. Here's the query I'm using the find the most recent record for each work order number:
SELECT * FROM [Work Orders] AS wk1
WHERE NOT EXISTS(
SELECT * FROM [Work Orders] AS wk2
WHERE wk1.[Work Order #] = wk2.[Work Order #] AND
wk1.[Call In Date] < wk2.[Call In Date]
);
Right now the table has a litttle over 10000 records and it takes anywhere from 10 seconds to 60 seconds to return the results. Is there a way I could possibly speed up this query?
View 2 Replies
View Related
Feb 26, 2008
I apologize up front for how long this is, but the queries involved are a bit complex.
I developed a database in Access 2003 running on XP sp2 to score events for our dog club. Everything works. I recently had to migrate to Vista. I installed Access without a problem. No changes to the database were made. Almost everything works except for a few queries. I continually get the error "Object invalid or no longer set." This is not running on a network - it is local on the hard drive. Setting XP compatibility mode and runing as administrator do not help.
The query that is actually causing the problem is the third in a chain, meaning it queries a dataset produced by another query, which in turn queries a dataset produced by a third query. This all works under XP.
I have narrowed the problem down to the ORDER BY statement in the query (I prefer to work in SQL). If I remove one of the sort criteria, everything works, except the results are displayed in the wrong order. It does not matter if I click the "run query" button on my form, or simply execute the query from the list of queries, the same error occurs. This is where I'm stuck.
Here is the chain. This query pulls data out of multiple tables and performs some point total calculations. This one is rather complex, but works fine. It produces a blob dataset of all entries for all events and sets points for each entry.
SELECT tblResults.Event_ID, tblEvents.Event_Name, tblClass.Class, tblDogs.Dog_Name, tblDogs.ID, tblDogs.Owner, tblDogs.Sex, tblResults.Dog_Wt, tblResults.Cert, tblResults.Ex, tblResults.Cplt_Wt, tblResults.Time, tblResults.Final_Wt, tblResults.Dist, tblResults.Place, IIf(tblResults!Cplt_Wt<400,0,5) AS Bonus, IIf([Bonus]=0,0,IIf(tblClass!Class="D",(tblEvents!D_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="C",(tblEvents!C_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="B",(tblEvents!B_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="A",(tblEvents!A_Count-(tblResults!Place-1)+[Bonus]),(tblEvents!O_Count-(tblResults!Place-1)+[Bonus])))))) AS Points, Round(tblResults!Cplt_Wt/tblResults!Dog_Wt,2) AS xbdywt
FROM tblClass INNER JOIN (tblEvents INNER JOIN (tblDogs INNER JOIN tblResults ON tblDogs.ID=tblResults.Dog_ID) ON tblEvents.Event_ID=tblResults.Event_ID) ON tblClass.Weight=tblResults.Dog_Wt;
That resulting dataset feeds the next query, which also works fine. This one sums the points for each entrant by class for the season, only selecting entrants that have been in 3 or more events.
SELECT DISTINCTROW qryAll_Results.Class, qryAll_Results.Dog_Name, qryAll_Results.Owner, Sum(qryAll_Results.Points) AS Points
FROM qryAll_Results INNER JOIN qryPulls_Entered ON qryAll_Results.ID=qryPulls_Entered.ID
WHERE qryPulls_Entered.CountOfID>3
GROUP BY qryAll_Results.Class, qryAll_Results.Dog_Name, qryAll_Results.Owner, qryAll_Results.ID;
This last query is the one causing the headache. This one pulls the 10 entrants with the most points for a class, sorts them by points, then assigns 1st-10th place.
SELECT TOP 10 qryOverall_Totals_All_Classes.Dog_Name, qryOverall_Totals_All_Classes.Owner, qryOverall_Totals_All_Classes.Points, ((SELECT COUNT(*) FROM qryOverall_Totals_All_Classes AS T WHERE T.Class = "A" AND T.Points > qryOverall_Totals_All_Classes.Points)+1) AS Place
FROM qryOverall_Totals_All_Classes
WHERE (((qryOverall_Totals_All_Classes.Class)="A"))
ORDER BY qryOverall_Totals_All_Classes.Class, qryOverall_Totals_All_Classes.Points DESC;
If you remove 'qryOverall_Totals_All_Classes.Points DESC' from the ORDER BY statement, the query runs. If you leave it in, it generates the error "Object invalid or no longer set". I've tried using the alias, but that didn't work either.
Any thoughts? I can post the database somewhere if someone needs to see the whole thing.
View 5 Replies
View Related
Feb 22, 2008
Hi,
I have been using the following query, literally for years, without any changes. I run it from code using db.execute, and I do use the dbfailonerror option.
UPDATE TST3 SET [date] = Mid([timedate],8,2) & '/' & Mid([timedate],10,2) & '/' & Right([timedate],4), [time] = Left([timedate],2) & ':' & Mid([timedate],3,2) & ':' & Mid([timedate],5,2), Serial = [serial] & '3';
One of the things it does is to add a '3' to the end of the [serial]. [serial] is the primary key in the TST3 table. You might think that there would be a problem if, say, I have a list of serials containing
35
56
1
13
and I'm trying to update them to
353
563
13
133
But this has worked OK in the past. NOW I'm getting a KV Error when it tries to update the 1 to 13, because there's already a 13 in the table.
Even stranger, when the query fails, all the rows BEFORE the offending record DO get updated. So the query fails, and I end up with:
353
563
1
13
(and yes, I DO have dbfailonerror set)
So, it looks to me as if update queries are no longer running as transactions.
I am pretty certain that action queries have always been run transaction-wise in the past... if the query fails, the whole thing should fail. WHY is the transaction processing no longer working for this update query? Has anyone else noticed this?
I recently ran microsoft update and am now running
Access 2002 (10.6771.6830) SP3.
View 7 Replies
View Related
Mar 14, 2008
I have a database with 96 tables.
8 tables per month
one query pulls the desired information.
My question is this, currently the query looks to all the January Tables.
Can I configure one query to request user input(month) and then the query looks to the tables based on the users input(month).
View 4 Replies
View Related
Aug 8, 2005
I have an append query that everytime it runs it will simply duplicate the records. ex.. sample table has 11 records if you run it again the tableB will now have 22 records. I need it to only update the records, so if someone makes in change / new record in tableA it will only update the records in TableB.
I have the Primary Key set to Yes no duplicates.
TableA Primary Key is ID
TableB Primary Key is CustomerID
Thank you,
Chuck
View 3 Replies
View Related
Jan 26, 2007
I have a form set up and would like to have field update to a table when a policy number is input into the form. The fields are extracted from a linked table and are not viewed on the form but need to be written to a table to create a report. I'm confused on the sets to take to handle this. I think i need to use the "onchange" property and set up a macro that runs a query but how does the query write to the table?
View 1 Replies
View Related
Jan 31, 2008
I have a report with several subreports which run agains a very large table. If I decide to print the report after examining it, all of the underlying queries must run again. Is there any way around this to avoid this delay, other than exporting as rtf or snp?
Thanks,
Randy
View 1 Replies
View Related
Oct 26, 2005
I need to execute a complex query. I can get the query to work if I break it into two queries, queryA and queryB, and then use queryB inside queryA. Note that queryB (combined query) produces the final results I want.
Here is the code for queryA
---------------------------------
SELECT
Table_Subgrantees.ID,
Table_Subgrantees.SDPISubNumber,
Table_Subgrantees.SDPIName1 AS SDPINameSubgrantee,
Table_Subgrantees.SDPIName2 AS SDPIName2Subgrantee,
Table_FormLogInfoSubgrantees2004.FormRequired AS FormRequiredSubgrantee, Table_FormLogInfoSubgrantees2004.FormRequiredComme nts AS RequiredCommentsSubgrantee
FROM Table_Subgrantees INNER JOIN Table_FormLogInfoSubgrantees2004 ON Table_Subgrantees.SDPISubID = Table_FormLogInfoSubgrantees2004.SDPISubID;
Here is the code for queryB
--------------------------------
SELECT
Table_AreaAbbrs.AreaAbbr,
Table_AreaAbbrs.AreaID,
Table_Grantees.SDPINShort,
Table_Grantees.SDPINumber,
Table_FormLogInfoGrantees2004.FormRequired,
Table_FormLogInfoGrantees2004.FormRequiredComments ,
Table_Grantees.SDPIName1,
Table_Grantees.SDPIName2,
QueryA.SDPISubNumber,
QueryA.SDPINameSubgrantee,
QueryA.SDPIName2Subgrantee,
QueryA.FormRequiredSubgrantee
FROM (Table_AreaAbbrs INNER JOIN (Table_Grantees LEFT JOIN QueryA ON Table_Grantees.ID = QueryA.ID) ON Table_AreaAbbrs.AreaID = Table_Grantees.AreaID) INNER JOIN Table_FormLogInfoGrantees2004 ON Table_Grantees.ID = Table_FormLogInfoGrantees2004.ID;
------------------------------------------------------------------------------------------------
I need to execute this query from ADO inside my code so I would like to come up with a single query that will return the results I want. I have played around with it for quite a while and everything I come up with generates messages like: "Join expression not supported".
Can anyone help me write this query as a single query?
Thank You,
Becky
View 1 Replies
View Related
Aug 4, 2007
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"...
View 6 Replies
View Related
May 25, 2005
Hi,
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?
Thanks
View 2 Replies
View Related
Sep 16, 2005
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.
View 3 Replies
View Related
Sep 19, 2005
This is the table structure that I use:
CAR TABLE
==========
Car_Id
Car_Tag_Number
CLIENT TABLE
============
Client_Id
Client_Name
CLIENT-CAR TABLE
=================
Car_Client_Id
Car_Id
Client _Id
ORDER TABLE
============
Order_Id
Car_Client_Id
Order_Date
Payment_Date
Payment_Amount
ORDER-DETAILS TABLE
=====================
Order_Detail_Line_Number
Order_Id
Item_Id
Order_Detail_Price
Order_Detail_Qty
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.
Please let me know if you can help with this.
-Alon
alon@wsco.com
View 1 Replies
View Related
Dec 14, 2005
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:
MODEL_NO=====PARTITION_NO=====EQUIPMENT_ID===etc..
A1A1M01======1516=============XYZ-110-A=====etc..
A1A1M01======1516=============XYZ-111-A=====etc..
A1A2M01======1517=============ABC-122-A=====etc..
A1A2M01======1517=============ABC-123-A=====etc..
View 3 Replies
View Related
Aug 31, 2006
Hello, this is kinda complicated to explain so Ill try to break it down.
I have a table with the following sample data
SerialNumber DateReceived Measured Level
0000-0024 25/08/2006 11:31 84
0000-0024 25/08/2006 12:59 84
0000-0024 25/08/2006 15:05 84
0000-0021 25/08/2006 10:08 32
0000-0021 25/08/2006 17:19 32
0000-0024 24/08/2006 09:45 88
0000-0024 24/08/2006 16:06 88
0000-0021 24/08/2006 13:09 36
etc
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
Dan
View 3 Replies
View Related
Feb 6, 2007
Hi
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
Andrew
View 6 Replies
View Related
Mar 29, 2007
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.
Gurdip.
View 12 Replies
View Related
Jun 28, 2007
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.
Any Help would be greatly appreciated
Barry
View 2 Replies
View Related
Jul 24, 2007
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
))))))))))))))
View 14 Replies
View Related
Oct 10, 2007
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
GuitarOptionCodeComboID
AE185185RR1
AE185186RHT1
AE185187RT1
AE18538185RR2
AE18538186RHT2
AE18538187RT382
AE185BB185RR3
AE185BB186RHT3
AE185BB188RT-B3
AE18538185RR4
AE18538186RHT4
AE18538188RT38B4
AE185BB185RR4
AE185BB186RHT4
AE185BB188RT38B4
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 ...
Thank you in advance!
View 14 Replies
View Related
Jul 19, 2005
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.
Is there a way to do this?
Thanks,
Doc
View 4 Replies
View Related
Nov 10, 2005
Hi All
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
Query Values
Out..................In
25/10/05............2/11/05
In this instance only the first record in the table should be found.
Many thanks for help in advance
Simon
View 3 Replies
View Related
Feb 15, 2006
I need to create a query based on the following 3 tables.
table PA0001 has the following entries (first line is the header data)
EmployeeNumber - ContractType0001
1234 - 1
1235 - 2
1236 - 1
1237 - 1
1238 - 2
1239 - 3
1240 - 2
PA0016 has the following entries:
EmployeeNumber - ContractType0016
1234 - 11
1235 - 12
1236 - 13
1237 - 11
1238 - 11
1239 - 13
1240 - 12
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:
EmployeeNumber - ContractType0001 - ContractType0016
1236 - 1 - 13
1238 - 2 - 11
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.
Thanks,
Evan
View 2 Replies
View Related
