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 trying to reformat a string of text so that it is easier to read on a report.Based on the text, it seems like the best thing to do is to insert a carriage return before a date/time stamp.Here is the data (it's a conversation log)...it is three unique conversation log entries:
04/13/2014 8:21PM - all work and no play is no fun - Bob Smith04/13/2014 8:21PM - this is a test of the emergency broadcast system - Bob Smith04/13/2014 8:20PM - log testing - Bob Smith
If the carriage return is before the date/time string, then all entries would look the same. If the carriage return is after the date/time string.A user had suggested a child table to break out the data into separate records, but I'm sad to say that I never worked with child tables. Any way for replacing 'Date/time stamp' with 'carriage return' & 'Date/time stamp"
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 at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.
|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|
If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.
Table TBL_NEWDATA is used to append new data to table TBL_PERSON_ALLOCATIONS.
TBL_NEWDATA { Person_ID, Department_ID }
TBL_PERSON_ALLOCATIONS { Person_ID, Department_ID, ... }
I need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there. i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.
INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID)
SELECT Person_ID, Department_ID
FROM TBL_NEWDATA
WHERE TBL_NEWDATA.Department_ID='Form...'
[code]...
This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.
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 have a normalized DB with one to many relationships, using Primary and Foreign Keys.
I need to do inserts and maintain the PK/FK relationship, which means when I add a new PK I need to insert that PK as FK in other tables in the same transaction.
How do I do this in Access? SQL Server I use transactions, but I can't lock up the tables in Access like that.
I did a bunch of searching and found nothing, which leads me to believe I am way off in my thinking. Below are the dirty details
I have 2 tables, tblName and tblPhone. 1 name can have many phones.
tblName has PKName. tblPhone has PKPhone, FKName.
I have a form where user enters a new Name and PhoneNumber.
Name gets inserted to tblName, assigned with PKName = 100
Phone should get inserted into tblPhone with PKName.
IE Insert into tblPhone (FKName, PhoneNum) VALUES (100,"212-555-1212").
The dumb way I am doing it now is I insert to tblName, query tblname for the PK, then write to tblPhone. This can't be right.
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 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 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
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.
Hello,
I am using DMax() to return the highest date in a table, but really need it to return the second-highest...so, if I had:
14th Feb
10th Dec
1st Jan
I don't want it to return 10th Dec, I want it to return 14th Feb
Any help much appreciated. I am sure there was something like dmax()-1, but that just returns (in my example above) 9th Dec!
Thanks!
Hi,
I need to create an sql query for an ms access db that will return the data type of a given column.
Can anyone help me? I quite new to sql etc.
thanks in advance!