New person to the forums here. I've had a scroll through the FAQs and can't see an answer to a problem I need to solve. I wonder if there's anyone could offer some help?
My db has three tables: table_a, table_b and table_c. Each table has the following fields:
table_a
a_id (PK)
a
table_b
b_id (PK)
b
table_c
c_id (PK)
a_id (FK (many to one))
b_id (FK (many to one))
c
I would like field c in table c to be a concatenation [table_a].[a]+"-"+[table_b].[b]. I'd like this field to automatically generate and be constrained as unique. Creation of value c in table_c should be by selection of value a & then value b in a form based on table_c.
Can anyone advise me how I do this?
By the way - editing to add this - I am using MS Access 2000 & can't upgrade to a later version.
I have 8 checkboxes. Each checkbox has several e-mail addresses as string. Therefore, each checkbox has a string variable declared. I was wondering what should I do when selecting multiple check boxes. This is my code:
Problem is that if I only select chkAGDLLA, then strMail will be "email1, email2, email3, email4, , , , , , ,"
I do not want all those commas, but how to make this work. I was thinking maybe a SELECT CASE so that strMail will accumulate data based on what's checked, but then there will be a problem of there being no comma between cases.
Dim i As Integer, c As String With Me.ListBox.Column(0) For i = 0 To Me.ListBox.ListCount - 1 c = c & Me.ListBox.ItemData(i) & ", " Next End With c = Left(c, Len(c) - 2)
This works great!! Now I am trying to concatenate the column 3 of the listbox.
Tried to use the code below (pointing to the second column) but it always returns the first column data.
'Dim i As Integer, d As String With Me.ListBox.Column(2) For i = 0 To Me.ListBox.ListCount - 1 d = d & Me.ListBox.ItemData(i) & ", " Next End With d = Left(d, Len(d) - 2)
ItemData does not allow pointing to my 3 column of the listbox.
Ive got a table called tblClientCar, this is its structure:
ClientCarID ClientID CarID
I want to use an append query to concatenate ClientID and CarID together and putting the concatenated value into ClientCarID. How would i do this?
Ive tried using a form a two combos so when i select the clientID from the 1st combo and then the CarID from the 2nd combo, then run the append query to write the concatenated value into ClientCarID of the table. But it says writing 0 rows to table and the ClientCarID isnt concatenated when i check the table. This is the query sql syntax i was using:
INSERT INTO tblClientCar ( ClientCarID, ClientID, CarID ) SELECT tblClientCar.ClientCarID, tblClientCar.ClientID, tblClientCar.CarID FROM tblClientCar WHERE (((tblClientCar.ClientCarID)=[Forms]![frmClientCar].[Combo6] & "" & [Forms]![frmClientCar].[Combo8]));
I also tried another way. The 2 combos add the selected values into ClientID and CarID in the table tblCar. So i made the append query slightly different to concatenate ClientID and CarID to form ClientCarID. This is my query sql syntax:
INSERT INTO tblClientCar ( ClientCarID, ClientID, CarID ) SELECT tblClientCar.ClientCarID, tblClientCar.ClientID, tblClientCar.CarID FROM tblClientCar WHERE (((tblClientCar.ClientCarID)=[ClientID] & "" & [CarID]));
I get the same result as the 1st query. What am i doing wrong? Is there another way of doing what im trying to do? Whats the correct sql syntax for doing this?
I want to produce some UPCa bar codes which require 11 digits of TEXT for the barcode function to work.
I want the bar code to be made up of several fields Make 3 Chars (Text) (eg 021) Model 3 Chars (Text) (eg 103) Serial_Number 5 Characters (Numeric) (eg 00025) (I need to keep the Serial_Number field numeric so that it increments in modulo 10 format (ie get Numeric only results))
So i need to produce a field that looks like this 02110300025 after the 3 fields are concatenated
However I am getting 02110325
I'm sure that the answer is simple but I seem to be having a mental block. Does anyone have any ideas? Your help will be very appreciated.
I am trying to create a basic query that adds four numbers, but when I try '[Mark1]+[Mark2]+[Mark3]+[Mark4]' it concatenates it instead of adding. e.g. I have '58+96+13+45' and I get '58961345' instead of 212.
I am using this as a criteria for a Query that is pulling the 2 dates from a form - Report Date Range. The reason I am trying to do this is I have a cumulative report that needs to run for the fiscal year (1/1/xx to 12/31/xx). It will always use 1/1/xx as the base date for the range. I am trying to pick up the year because it will change depending on the periods/years picked.
Between #1/1/ & Year ([Forms]![frmReportDateRange]![txtBeginningDate]) & # And [Forms]![frmReportDateRange]![txtEndDate]
When I run the query I get "The expression you entered has an invalid date value." I am trying to concatenate the 1/1/ with the year and obviously that is not working. Any ideas?
I'm trying to concatenate several fields from a set of tables/subtables. But for some reason, it concatenates the first 3 records, then stops. I should clarify that the concatenation "skips" a subtable. Here's the structure of the relationships (not sure if this pic is working so I'm adding the link to the screen shot)
[URL] ....
and the concatenation itself
As you can see, I'm looking to create a concatenation of (Vendor_Code)&(Vendor_Item_Code)&(Item_ID) which runs through the subtable Items_Child, but doesn't use any field in Items_Child
I am trying to concatenate values from seperate columns. Which is fine, however if there are no values in any of these columns I wish to include a dot (.). I also need to add a dot (.) inbetween values.
We use two different systems in our office that have notebook facilities. One of them stores only a limited number of characters in the note field per record and as such, some notes actually span several records. The other system stores the whole note in one record. We want to transfer all the notes from the first system (1 note over several records) to the second system (1 whole note in 1 record). I have a table with the notes from the first sytem which looks something like Note1 - Line1 - Text Note1 - Line2 - Text Note2 - Line1 - Text Note3 - Line1 - Text Note3 - Line2 - Text Note3 - Line3 - Text
What I want to do is, for each note, combine the separate lines into one record. I have been able to do this in Excel with some formulas (see attachment) but am wondering if it is possible to do this in an Access Query (i.e. GroupBy the note number and have a calculated field combining the individual lines of the note, or something similar)?
In a query, I'm using concatenation to join multiple address fields (organisation, building number, street address, etc) into one field. I'm also inserting line breaks to create a formatted address and then creating a form on the query so the formatted address appears in a text box and can be copied and pasted straight into my label-writer software to print address labels.
However, one problem with which I am now presented is where a field is blank. E.g. if one of my contacts is retired, then they don't have an organisation listed in the "Organisation" field. This means that a blank line is left between their name and the rest of the address.
When running a mailmerge there is the option of skipping blank fields so that a blank line is not inserted in such cases.
I have a query that is set up to join two name fields...for example:
[ELIGVENDORS.LSTNAM] & [ELIGVENDORS.FSTNAM]
The issue is that this is perfect for names that are entered in the table like this:
Lstnam: ABC HOSP Fstnam: ITAL
They are entered like that for groups, but when individual doctors are entered, the data entry folks are entering them like this:
Lstnam: SMITH Fstnam: BOB Titlcd: MD
So when I join them the first displays correctly as ABC HOSPITAL, the second displays as SMITHBOBMD and I have to manually go in and add a comma.
Is there a way to do an if/then statement or something to tell the query to display results If the titlecd is NOT NULL, display as [ELIGVENDORS.FSTNAM] & [ELIGVENDORS.LSTNAM]&", "&[ELIGVENDORS.TITLCD], else display as [ELIGVENDORS.LSTNAM] & [ELIGVENDORS.FSTNAM]
I'm sure there is, I am new to IF/THEN statments though and was hoping to get some help setting it up.
Thanks in advance and I hope my description is clear enough.
I would like to make a query that combines the StoreNumber into one field CombineStoreNumbers separated by commas as follows...
LogNumber: 1 CombineStoreNumbers: 2, 3, 4
I tried the following crosstab query, but it's not quite right. TRANSFORM StoreNumber SELECT LogNumber FROM tblWorkOrderStoresFinal WHERE (((LogNumber)=1)) GROUP BY LogNumber PIVOT StoreNumber;
There are so many threads on here for emailing a PDF version of a report. I've put together a nearly complete bit of VBA that will do what I want - except for one thing.
Basically I have a personnel table with an email field. I Also have a field that details if a person is available or not ("Home" means they are available)
I need to create a string that is comprised of all the email addresses of the personnel who are "Home". In another thread I read that this can be done using a SQL statement.
So this is the VBA that I have so far - I commented the SQL for now because I don't quite know how to use it here.
Also, I have created the string, called "emailsList"
Code: Private Sub Email_Button_Click() 'SELECT Personnel_Table.Email FROM Personnel_Table WHERE (((Personnel_Table.Status)="Home")); Dim emailsList As String DoCmd.SendObject acReport, "AWACT_Report", acFormatPDF, emailsList, , , "Training Update", "Attached is the newest Training Report.", True End Sub
I have a query that I want to concatenate some of the results onto a single row, but I'm having trouble.I've followed Allen Browne's example, but when I run the query, I either get the #Type, #Name or a 3201 error (I believe, I can't recreate it now).
I have five reason fields in my table and I would like combine them into one field on my report, with line breaks between so each reason starts on it's on line. Also if there is a blank record, I would like the report not to display a blank line. I have found ways to do this online for 2 records but I need this for 5.
Also when I do manage to get the lines all to show in the report there are 3 paragraph returns between them - making the reason display of the report 15 lines tall, not just 5. The code I have so far, which displays each of the reasons but spaced as described is:
I am trying to concatenate 2 unique values which are parts of 2 identical rows(records) in order to have only 1 row(record) with concatenated field where the values are unique. For illustration here is what I need to achieve :
I am trying to concatenate Address fields into one text box. The Control Source pull-down menu gives me the Field Names listed as QueryName.ColumnName.
I know that if they listed the fields as just ColumnName, you'd simply go: ColumnName & " " & ColumnName2...
But the way these columns are defined, I cant find the correct syntax.
Is there a way to do this without changing all my queries??
I have three tables: Event related on to many with Procedure Procedure relates on a one to one basis with the description of the procedure in a table called ProcedureCodes.
I wish to have a query which outputs three columns:
Event - Prcedure1, Procedure2, etc - Description1, Description2, etc.
I have tried the Allen Browne module [URL] .... This gives me:
Event - Procedure1, Procedure2, etc using the expresion:
Expr1: ConcatRelated("[Procedure Code]","[tbl-procedures]","[tbl-procedures].[Event number]=" & [Event no]) to concatenate the procedures.
But I am struggling to get the final column! I have tried the following:
I have a query that has multiple IDs and different information in numerous fields. For example:
ID Field1 Field2 Field3 1 x 1 m 1 b
I need to find a way to concatenate the data so that it shows the information like this:
ID Field1 Field2 Field3 1 x m b
I'm working in Access 2010.
Yes I know this isn't the best way to set up a database, but I'm trying to make fixes to an already existing database that I can't go back and change the way it is set up. I can only find work arounds to accomplish what I need.
I am trying to create a list of values in a field separated by commas. I have done this in a query as follows:
[Field1]&", "&[Field2]&", "&[Field3] and so on.
However, when Field2 is null, the result is two commas between Field1 and Field2, but I only need one. What function can I use to eliminate the extra commas when fields used in the concatenation are null?
I know that it isnt advised to store the concatenated value but in this case, I have a memo field which is the description of a particular system component.
i have Unbound Combo Boxes for descriptive elements such as COLOUR, SIZE, CONNECTIVITY, MAKE.
ALL of these i would to store in a field in my table and called Description and separate them by colons or the actual words which describe the category.
There are two tabs named Table1 and Table2. In actual there are two tables in Access database named Table1 and Table2. How the data is stored in ACcess tables, I have made two tabs in excel workbook. Now I want Access VBA code that will check if data in Reference field of Table1 matches with any of the data in Reference field of Table2.
If it matches then change the status of the corresponding record of Table2 with either "Withdrawn","Obsolete" or "Updated". SO it depends upon which field out of "WIthdrawn","Obsolete" and "Updated" in Table1 stores "Y". At a time only one of them will have "Y" and rest of two fields will have "N" as shown in the sheets.
As in the example, now Reference "R566" of Table1 matches with Table2 Reference so the status field in Table2 for that record will be "WithDrawn".
