I hope this is a simple query becuase my brain has drawn a blank on it.
I have a query with a parameter which a form textbox. If the text box has X value I want to use one list of parameters and if has Y I want to list all rows in the query.
Any help appreciated!
Dave
I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.
So if my input table looks like this
[strField]
"kick the ball"
"return the pass"
my query result should looks like this
[Orig] [new]
"kick the ball" "kick"
"kick the ball" "the"
"kick the ball" "ball"
"return the pass" "return"
"return the pass" "the"
"return the pass" "pass"
Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.
With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.
I am looking to return one row from groups of the same EpisodeID whereby the row with the minimum date is selected each time. This includes returning all other fields in the row such as EventID below and ideally others as well if that will be possible.
To illustrate I include the following. What Access 2003 query would I need to return all the rows with the earliest dates? EventID will be unique in the intial table.
Code:
EventsTable
```````````
EpisodeID | EventID | EventDate
-------------+---------------+-------------
1 | 001 | 01/02/2010
1 | 023 | 05/10/2009
1 | 103 | 12/02/2010
2 | 004 | 02/03/2013
2 | 102 | 12/10/2014
3 | 546 | 04/05/2012
3 | 100 | 08/08/2013
3 | 034 | 10/10/2012
3 | 066 | 02/03/2013
4 | 777 | 05/07/2014
4 | 233 | 01/11/2012
5 | 087 | 10/03/2011
Code:
ExpectedOutput
``````````````
EpisodeID | EventID | EventDate
-------------+-------------------+---------------
1 | 023 | 05/10/2009
2 | 004 | 02/03/2013
3 | 546 | 04/05/2012
4 | 233 | 01/11/2012
5 | 087 | 10/03/2011
I have a Query of dates and I need to have those dates return onto a from horizontally?
View 1 Replies View RelatedFor each record in my database, there are observation periods which are recorded in the format dd/mm/yyyy hh:mm:ss, titles as follows
1st Obs Start
1st Obs End
2nd Obs Start
2nd Obs End
3rd Obs Start
3rd Obs End.
I have been asked to create a query that will quickly show how many obervation periods commenced in a particular month. What I am trying to do is create a column that will be named Obs Start, and another, Obs End. For each record ID, this would then show as follows:
ID......Obs Start.............Obs End........
1....[1st Obs Start].....[1st Obs End]....
1....[2nd Obs Start]....[2nd Obs End]....
2....[1st Obs Start].....[1st Obs End]....
2....[2nd Obs Start]....[2nd Obs End]....
2....[3rd Obs Start].....[3rd Obs End]....
3....[1st Obs Start].....[1st Obs End]....
4....[1st Obs Start].....[1st Obs End]....
etc.
I could then quickly count how many obs periods started within the desired month.
In a query I'm trying to return a list of rows sorted by Service Type Ascending and then the last item in the list should be a row called "Add Edit Value".If I 'ORDER BY 2' then the "Add/Edit" row appears at the top which is not what i want.
My SQL:
SELECT '' As ServiceTypeID, 'ADD/EDIT VALUES' As ServiceType FROM ServiceTypes UNION SELECT ServiceTypes.ServiceTypeID, ServiceTypes.ServiceType FROM ServiceTypes
ORDER BY 1 DESC;
Not sure if what I want to do is possible, or at least possible the way things are set up.
I have a massive table - c. 6 million rows. It contains data along these lines:
Plan#, Item, Price, Description, Colour, Value, Location, etc.
The primary key would be Plan# + Item. Each Plan# has approximately 1,000 Items, and there is only 1 Item per Plan#. There are only a limited number of Items (c. 1500) and all or only some Items might be assigned to the Plan#. All items under each unique ID# belong together, sort of in a set. So this huge table has approx. 6,000 unique sets (based on Plan#).
To add to the confusion, Item A under Plan#1 may have different information (Price, Description, Colour, etc.) from Item A under Plan#2. I know this isn't a great way to set up data but this is what I have to work with.
Over the years it's possible that the exact same combination of Items with identical values might have been set up for multiple Plan#s. What I need to do is find any Plan#s which have the exact same combination of Item, Price, Description, etc. So if Plan#R has 200 rows and Plan#S has 201 rows, it automatically doesn't match. If Plan#R has 200 rows and so does Plan#T, all information in each record must match between the two Plan#s (with the exception obviously of Plan#).
I don't think this is possible, and if it is I am sure it's not going to be easy. So far the best I can do is to come up with finding duplicates on Item, Price, Description, etc. but that's only one record at a time and doesn't tell me if the two Plan#s match.
Any help or suggestions would be much appreciated.
Thanks!!!
Hi:
Using MS Access 2000.
Maybe I've been looking at the monitor for too long but... I need to have a query return data based on a date field or return all dates greater than 1/1/2001 if left blank.
criteria: [Enter date - mm/dd] Or >#1/1/2001#
When I leave it blank it gives me all dates as it's supposed to but when I type a date it does not filter by that date, I still get everything.
I also tried [Enter date - mm/dd] Or Like "*" but it behaves the same way. I know it has got to be easy but my skull may be too thick to let me see it.
Any help is appreciated.
:confused:
I am trying to convert a table that looks like the following...
Customer Name SumofSum of Bill Rate Reviewer
000462 John 500 Mike
000224 Mike 900 Jeff
I would like to covert it to....
Customer 000462 000224
Name John Bill
SumOfSum.. 500 900
Reviewer Mike Jeff
I don't think Access has any functions for this but I am not 100% sure. I know this can be done in a Module but I am not sure of how to code it.
Hi,
In a query, I am using the following code to return the average length of each tenancy. This works fine unless the length is 0. If so, it only brings back 'blank'. How can I change this code to return a 0 value?
Tenure Length AVG: IIf([Average Tenure Length]>0,Format([Average Tenure Length],"Fixed",Format(0,"Fixed")))
Thanks in advance
Lee
Hi guys
Im trying to set my label to a string.
Something like
myLabel.caption = StringOne & chr(13) & StringTwo
The problem is I just get funny symbols instead of the carriage return.
Please help
Thank you in advance
Hi There,
Can anyone tell me how to send a Carrage Return in Access? When I try to use the 'SendKeys with{Enter}', Access starts to run in an endless loop that seems to be some Wizard. All I want to do is go to a new line within the same text field.
Can any one help me? I'm an amature at Access and VBA.
Hi all
i would like to know if there is a way to return a field to null
thanks in advance
Hi all
i would like to know if there is a way to return a field to null
thanks in advance
I am creating a database for a hyperthetical car hire company. I have created three tables, customer table, car table and bookng table. The customer table is linked to the booking table using a Customer ID (autonumber). The car table is linked to the booking table using NumberPlate. The primary key for the booking table is Booking ID.
The cars are catagorised in different groups e.g. hatchback, sports car etc. The customer will choose a car type which is entered into the booking table. They will also enter the dates for which they want to hire their car.
I want my database to take these dates and the type of car, search through the car table and return a list of cars of that type which are availiable on these dates.
Can this be done???
When running a query, I want to hide duplicates records for a given field. PO# will have many duplicates, depending on how many part numbers were order on the same PO#. Other unique fields are not displayed. Can an expression help with this? If not, how do I go about hiding them in a query?
Current Query results:
DATE PO#
08/18/2005 ABCD
08/18/2005 ABCD
08/18/2005 ABCD
08/18/2005 ABCD
Desired Query results:
08/18/2005 ABCD
Thanks in advance with any help!
When the SSN field is null I would like this query to return : 000000000, is this possible and if so could you show me how? Thanks
SELECT
[CASEHEADS RECEIVING NON WELFARE MONEY].IVA_MEMBER_ID AS [IVA #],
[CASEHEADS RECEIVING NON WELFARE MONEY].MEM_SSN AS SSN,
Sum([CASEHEADS RECEIVING NON WELFARE MONEY].AMT_DISBURSE) AS AMT,
[CASEHEADS RECEIVING NON WELFARE MONEY].DT_DISBURSE AS [DATE] INTO [CASEHEADS RECEIVING NON WELFARE MONEY TBL2]
FROM [CASEHEADS RECEIVING NON WELFARE MONEY]
GROUP BY [CASEHEADS RECEIVING NON WELFARE MONEY].IVA_MEMBER_ID, [CASEHEADS RECEIVING NON WELFARE MONEY].MEM_SSN, [CASEHEADS RECEIVING NON WELFARE MONEY].DT_DISBURSE
ORDER BY [CASEHEADS RECEIVING NON WELFARE MONEY].IVA_MEMBER_ID;
Is there a way while using the count query to make it return zero when using the criteria (not like "N") instead of a blank datasheet.
View 1 Replies View RelatedI have a table that has multiple Account Numbers and their Balances, I need to only pull the records that have the largest value.
Example: Acct# Balance
2 $1.00
2 $500.00
2 $300.00
3 $25.00
3 $400.00
I need my query to pull only these records Acct # Balance
2 $500.00
3 $400.00
Please help me figure out how to only pull those records
Hi.
I have 2 queries, one that returns all the project numbers in the db (say 50 projects), and one that returns some of the project numbers (say 30 projects)
I can make a query that combines the 2 queries, and it returns project numbers that are in both queries (30 projects), but I would like the query to return the 20 projects that are in first query, but NOT in the second query.......does this make sense?
I am sure this is fairly easy.......it has just got me stumped!!
Many Thanks.
Frank.
hi,
I have a table that contains itemcodes and prices. An example of the records would be:
<Item Code> <Price>
Product001 £34.56
Product001 £49.23
Product001 £23.22
Product001 £98.43
Product002 £12.45
Product002 £54.34
Product002 £25.51
Product002 £76.84
Product002 £14.97
I would like to run a query that returns the lowest value price for each Item code. So in this example it would return:
<Item Code> <Price>
Product001 £23.22
Product002 £12.45
Does anyone know the criteria code in the query design view for this problem?
I know it sounds silly, but there are unique codes in another field, i just need to query against these fields.
Thank you for your help.
Dean
I have three queries that count non-compliant records in each of three catagories per month. One catagory has records for both Jan and Feb and returns the rersults as follows:
Jan 3
Feb 4
The other two vatagories however have no non-compliant records for January so they return as follows:
Feb 2
Feb 1
I wanted to tie the three queries together in the form for reporting:
Cat 1 Cat 2 Cat 3
Jan 3 0 0
Feb 4 2 1
Is there any way for the queries to return a zero for any month with no records rather than not returning the month at all? Thanks for any help.
This is probably the first of many questions. I have an Access2000 DB with two tables. Each has a field called ParID. I want to have a form with Table1 and Table2 listed. I want to query on a parcel number I keyin. If Table1 has the number in it, I want the form to note such. The same goes for Table2.
Example: Keyin: 123
If Table1 has 123 in it and Table2 does not, my form would note:
Table1= YES
Table2= NO
If they both had 123 in it:
Table1= YES
Table2= YES
etc.
Thanks,
SKK
Is there a good way to return an id that is created when I insert values into a database?
So I have this: INSERT INTO assignedReview ( reviewUser, reviewedEmpuser) VALUES (@reviewUser, @reviewedEmpuser);
And from that I want to get the reviewID that is generated (setup as a random autonumber) on the INSERT to pass back the user.
I am looking up a product based on a unique code in tblProducts...I need to return one of two values
If the value of [Product type] = "multi" then return "multi"
else
If the value of [Product type] = "solo" then return the value of the non empty field in a range of fields
for example
tblProducts
PID (key) | PACKAGE TYPE | RED | YELLOW | BLUE
C13T0714010 | single | <empty>| yellow | <empty>
C13T0754010 | multi | red | yellow | blue
query
C13T0714010 returns "yellow'
C13T0754010 returns "multi'
Is it possible to return an age as: 21.45 or 64.93?
I have done some searching and can't find any formatting or code to make it work.
I have changed the field format to Fixed and then it gives: 21.00 or 64.00
I like the 2 decimal places for quick at-a-glance-knowing of the age.
Of course I have a formula that returns an age with decimals in EXCEL, that's why I think it should somehow be possible in Access.