Please Help With Concatenation Query
Mar 8, 2006
I have a table that I need to query that looks something like this:
ID Type Product ID
123 Health 323
123 Health 424
123 Dental 424
124 Health 323
125 Dental 323
What I need to see is something like this:
ID Type Product ID
123 Health 323 + 424
123 Dental 424
124 Health 323
125 Dental 323
In other words, I need to have the Product ID field concatenate for like ID and Type. I know it would likely involve writing some code but I am a total beginner in that respect and I don't know where to even begin. I could really use some help. Of course I need this as soon as possible! :(
To make matters worse (well, maybe it does), I am using Access 97.
Thanks so much!!!
View Replies
ADVERTISEMENT
Sep 10, 2006
Can anyone please give me an Access 2000 VB module that will concatenate [Portia - 1 query.text1] with [Portia - 1 query.text2] and type the result in textbox [Alltext]? Thanks.
View 1 Replies
View Related
Dec 20, 2005
Hi,
Just wondered if someone could help me with this prob?
The following text appears in a text box on a report
=[HirerCurrHouseNo] & " " & [HirerCurrAddress1] & Chr(13) & Chr(10) & [HirerCurrAddress2] & Chr(13) & Chr(10) & [HirerCurrTown] & Chr(13) & Chr(10) & [HirerCurrPostcode]
My prob is that if say "Address line2" is blank, I get horrible blank line in address (which looks rubbish when pirnting a letter!). What I would really like to do is move the lines up so they show as a proper address..
eg
Mr Joe Bloggs
29 The Street
Anytown
HD11AG
Not
Mr Joe Bloggs
29 The Street
Anytown
HD11AG
Thanks - And Merry Xmas!
View 1 Replies
View Related
Sep 30, 2006
Hello,
I have a query ... i hope that you can help me solve it!
I am creating a database where i was the ID to be concatenated from 3 fields. I did that well i think i did but when i save the concatenation of the ID is not saved in the table .. :confused:
In the ID field in the form i added this in its control source [ =[Field1] & "" & [Field2] & "" & [Field3] ]
.. In the form it concatenated fine but in the table all the fields are saved except for the ID ... :rolleyes:
Help please!
View 2 Replies
View Related
Mar 27, 2005
H ow to concatenate the records of a single field with iteself
forexample
invoice no:
1
2
3
4
5
...
i want it to be written as
1,2,3,4,5,6
can it be done
can i concatenate records with each other of the same field.
View 3 Replies
View Related
Sep 14, 2007
In the table, i already had a few columns:
string1 string2 string3
001 1 1.5
001 2 6.4
I'd like to add a new column and concatenate the strings in previous columns with additional characters:
string1 string2 string3 string4
001 1 1.5 c-001-b01_1_5x
001 2 6.4 c-001-b02_6_4x
002 3 7 c-002-b03_7x
Is there a good way to do this? Thanks in advance.
View 2 Replies
View Related
Mar 7, 2008
Hi all
Is it possible to use a variable to finish the name of a control?
e.g.
Cell = me.A1 (where A1 is a lablel with the name a1)
forms![Myform]! "Cell".forecolor = 65280
What I am basically trying to do is to have a variating control name that i can change the color of.....too long to explain why really.
Can anyone help me please....is this possible?
Thanks
View 8 Replies
View Related
Jul 26, 2007
Hey everyone,
Well i've searched, and have been trying to use 'fConcatenateFldGroupBy' in a query in order to perform the following combining of data with a criteria requirement...
All data is in one table: (frequently re-imported, so no relationships)
Obviously data needs to be stored more efficiently, but I have no control over the imported data structure. Here is what I'm after:
Table structure:
ID - Name - Status - Comments
1120 - One - Pass - uniquecomment1
1120 - One - Fail - uniquecomment2
1120 - One - WIP - uniquecomment3
345 - Two - Pass - uniquecomment5
345 - Two - WIP - uniquecomment6
Need a query to format data as:
ID - Name - Status - Comments
1120 - One - Fail - uniquecomment1, 2, 3...
345 - Two - WIP - uniquecomment5, 6..
As you can see comments need to be concatenated with commas seperating them.
As for the Status determination, it needs to be determined for the ID based on certain conditions. If at least 1 is fail, all fail. Iff all pass, all pass. If no fails and 1 wip, WIP, etc.
I have wasted too many hours trying to figure this out on my own, so I'm seeking your help! Any input would be appreciated. Thank you.
View 7 Replies
View Related
Aug 18, 2006
Please can anyone give me a piece of code to combine the text from memo fields in 25 different queries and put it in one text box for printing?
Thanks.
View 2 Replies
View Related
Aug 22, 2006
I have some data and I want produce a summary report. The trouble is that I need to combine F2 in to one value. AggOfF2 is produced by taking the distinct values for F2 in the group, and concatenating them together with a "/" seperating each element. I don't believe there is a built in function to do what I want, but can I create a function in VB? If so, how?
SOURCE
F1 F2 AMT
1 A 1
1 A 1
1 B 1
2 C 1
3 D 1
SELECT F1, AggFunc(F2) AS AggOfF2, SUM(AMT) as SumOfAmt
FROM SOURCE GROUP BY F1;
SUMMARY
F1 AggOfF2 SumOfAMT
1 A/B 3
2 C 1
3 D 1
Thanks,
David
View 4 Replies
View Related
Dec 13, 2005
This really is more of a VB than an Access question, but I need it answered, and I don't know a good VB forum so....
I have a text box that I'm trying to add text to, I have a loop, and every time through the loop it's supposed to concatenate new text into the text box by appending it to the end. I can't seem to be able to find a way to do this. I can't use the <&> operator, and VB doesn't have a <+=> operator (I wish I could do this in C/C++, but oh well). Any ideas? Or do I have to set the existing text to a string, concatenate the new text onto that, and then set it to the textbox?
View 3 Replies
View Related
Feb 28, 2015
know if there is a way to use a string concatenation to refer to a control name. eg. Referring to Control1, Control2, Control3, Control4, etc using something like Me.["Control" & "1"], Me.["Control" & "2"], etc
View 6 Replies
View Related
Oct 19, 2004
What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?
I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.
So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.
Thanks in advance
Todd
View 1 Replies
View Related
Mar 24, 2013
I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
View 3 Replies
View Related
Mar 4, 2015
In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
View 1 Replies
View Related
Oct 28, 2005
Hello All,
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)
View 9 Replies
View Related
Nov 20, 2013
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
View 5 Replies
View Related
Apr 2, 2013
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
View 3 Replies
View Related
Aug 12, 2015
I have a query where these are the fields:
ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
View 4 Replies
View Related
Jul 15, 2014
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies
View Related
Jul 23, 2015
I have a form which will be used as the basis to print a label.
It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.
What I want to do is to update the form/query without updating the underlying tables to the query.
View 14 Replies
View Related
May 31, 2006
:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;
-----------
qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
Thanks for you time! :)
View 1 Replies
View Related
Mar 8, 2005
I'm building a report for annual software license renewals. The report data source is a query that combines the customer information, their computer information, and the licenses purchased for that computer. I am having no trouble with the form displaying the customer info page, then a page with the computer info at the top and a list of licenses purchased for that computer underneath.
That would be great, if that's what my boss wanted. However, she wants the whole list of available licenses displayed on each page, in the event someone want to purchase additional licenses with this year's renewal.
I'm trying to figure out how I can set up a query/report grouping to do that. I've tried making a new query, relating the qryLicense!licenseID to the qryPurchase!purchLicense and setting the relationship option to show all records from qryLicense and only those related from qryPurchase. I added the qryPurchase!purchCPU field to my query, hidden it, and set the criteria to “=1” (the computer ID of one of my dummy computer records). I also have a Sum of the qryPurchase!purchQty field included in the new query that I want to display the total number of that particular license purchased (and 0 if there are no corresponding records). All fields except for the quantity field are set to Group By.
What I’m getting from this is simply a list of the licenses purchased for that computer, not the complete list of licenses available showing the quantity purchased where applicable.
Can someone see where I’m going wrong?
Slaughter
slaughter at mizzou dot edu
View 9 Replies
View Related
Oct 8, 2013
I am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.
Tables:
Days with 7 records
Hours with 24 records
Workers with as many people that sign up to work the different hours
Schedule signifying the worker, day, and hour which are signed up.
As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.Currently my Schedule table has the following:
WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
16 | 5 | 15
What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:
WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
| | 14
16 | 5 | 15
View 2 Replies
View Related
Oct 1, 2014
I have a query with three sub queries, all returning a number from different tables. But when any one of the sub queries returns a blank, the entire main query is blank.
How do I stop this from happening? I have tried NZ() on the sub queries and on the main query, but the blank still happens.
View 1 Replies
View Related
Aug 12, 2014
I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.
I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .
My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.
View 9 Replies
View Related
