Query Is Re-organizing Data In A Concatenated String
Jan 11, 2012
I'm putting together a shipping database. We scan in a string of barcodes, separate out the Serial Numbers from the Barcode Data, concatenate them together as a string with asterisks as a delimiter and join them together with a date code and manufacturing line identifier. Throughout all of the data manipulations I am able to maintain the Serial Numbers in my table in the order which they were scanned in. The very last function is to concatenate the Serial Numbers and Lot information together into one long string which will then be output to a label creation software. And up to that point, the Serial Numbers are still in the correct order (based on them being linked to an autonumber field in the initial table) Upon the final concatenation, the order of the Serial Numbers is being changed, sometimes 17 out of 18 Serial Numbers will be correctly ordered with just one being randomly placed in the string out of order. Here is the code in SQL that I use for concatenating.
INSERT INTO tempMotorPalletLabel ( PalletLabel )
SELECT DISTINCT tempMotors_to_Warehouse.PalletNumber&"*"&tempMotor s_to_Warehouse.Count&"*"&tempMotors_to_Warehouse.P artNumber + '*' + Replace(Replace(ConcatRelated("SerialNumber","temp Motors_to_Warehouse","PalletNumber='"&PalletNumber &"'"),' ',''),',','*')
FROM tempMotors_to_Warehouse;
And this SQL Query transfers the concatenated string to a table that my Label Software queries for data to create the label with.
INSERT INTO AccessMotorLabelData ( PalletNumber, [Count], PartNumber, PalletLabel )
SELECT DISTINCT tempMotors_to_Warehouse.PalletNumber, tempMotors_to_Warehouse.[Count], tempMotors_to_Warehouse.PartNumber, [tempMotorPalletLabel].PalletLabel
FROM tempMotors_to_Warehouse INNER JOIN tempMotorPalletLabel ON tempMotors_to_Warehouse.PalletLabel=[tempMotorPalletLabel].PalletLabel;
I am receiving this file from a supplier to import as a table. The issue is I cant figure out a good way to display the info for each invoice because there are multiple rows corresponding to each invoice number. I wanted to build some type of form where I could search an invoice number and it would pull up the standard invoice format to print such as inv.#, mailing address, linedesc amount and costs of shipping each item. I cant figure out how to get the invoices that have multiple line items to display in a nice format though.,,
Hi, I need a little help. I will give you the background on what i am doing to hopefully make this easier to resolve. P.S I am open to other approaches.
2) The following Module concatenates the columns (my idea being the user can order all items in bulk.
Code:Public Function Conc(Fieldx, Identity, Value, Source) As Variant Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim SQL As String Dim vFld As Variant Set cnn = CurrentProject.Connection Set rs = New ADODB.Recordset vFld = Null SQL = "SELECT [" & Fieldx & "] as Fld" & _ " FROM [" & Source & "]" & _ " WHERE [" & Identity & "]='" & Value & "'" ' open recordset. rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly ' concatenate the field. Do While Not rs.EOF If Not IsNull(rs!Fld) Then vFld = vFld & ", " & rs!Fld End If rs.MoveNext Loop ' remove leading comma and space. vFld = Mid(vFld, 3) Set cnn = Nothing Set rs = Nothing ' return concatenated string. Conc = vFldEnd Function
3) A query calls on this
Code:SELECT Conc("ID#","PartNumber",[PartNumber],"Qry_70_BOM_Awaiting_Order") AS [BOM ID#], Qry_70_BOM_Awaiting_Order.PartNumber, Sum(Qry_70_BOM_Awaiting_Order.[Qty Req]) AS [Total Qty Required], Conc("Work Order","PartNumber",[PartNumber],"Qry_70_BOM_Awaiting_Order") AS WOrdersFROM Qry_70_BOM_Awaiting_OrderGROUP BY Qry_70_BOM_Awaiting_Order.PartNumber;
I would like my users to purchase againts the new BOM ID# records as seen above. Once a order is placed against these i will, put something against each records to show there order status. (i'll take care of this part.)
Finally, sorry about the lenght of this, I need to be able to use the above BOM ID# to reference back to the the ID# table at the start of the thread. In this example Id's records 2 - 4 and 5 will show ordered.
In other words how do i extract 2, 4, 5 back out of the above and into there original state, but including the order status?
I have built a query, a series of concatentions. What I need to do is add this query as a field on my form. So that the info that has been concatenated is stored in my table. Can this be done? I am able to show this query field on a report, but I need to pull the concatented info into the table. Hopefully I have explained this well enough.
I am trying to use a criteria to filter out nulls in concatenated variable, which slows the query considerably.The part that makes it slow looks like this -
WHERE ... AND (([tbl1].[x] & [tbl1].[y]) <> "") AND ...
If I work them individually, it's not a problem, the query is fast, but I have to combine them first in order to produce the correct output.
I work in a library, and it's my job to keep track of information on thousands of journals.
I have seven sources of information--some of them were already on my computer in word or excel form and I converted them for access, and some of them I downloaded from various places we have subscriptions. My approach so far has been to add the necessary fields to my SuperHoldings table and run updates and appends from the other tables to update and append that one SuperHoldings table. I chose the SuperHoldings table because, true to its name, it was the one with the most records. All of the tables can be related using the Title field.
I'm wondering if perhaps my approach is wrong. Making just one table to use seems like a waste of a database, but I'm not sure how to organize it otherwise. What I want is to be able to go to one central place and see all of this information for each record at once. (That makes 19 total unique fields, plus an optional "Subscription Info" link for EBSCO and Direct titles). (The Notes fields I would want to append into one field together, even though they all have different information--I asked how to do this is a previous post, so I can do it now.)
My problem is that often there are titles in one table that aren't in another table. Let's say that I use SuperHoldings as my primary table, and information about each record is filled in from the other six tables--well, the Project Muse table has some records that aren't in the SuperHoldings table--so if SuperHoldings is the control, then the Project Muse records won't come up unless they're in common with the SuperHoldings records.
I've looked at the way to relate tables, and there isn't an option for Show ALL records of BOTH Tables--combining like records when applicable.
So, that's why I've been adding all of the fields to SuperHoldings, appending whatever titles/records aren't in the Superholdings list, and updating information one table and one field at a time from the other six tables.
Actually, all of that work is pretty much done--but information is going to change on those six tables. I can either update my superholdings table from changes each source sends out and just forget about downloading new versions of the other 6 forever, or I can download new versions periodically and have it updated like that.
If you're confused... well, I'm sorry. I'm confused to.
I'm putting a list of my tables at the end of this. If you have suggestions on how I can organize this, please let me know.
I am on the lookout for workshops or training sessions that I can sign up for to be trained in Microsoft Access, but so far I haven't found any. I think I would prefer an actual classroom setting so I can ask these insane questions and use my hands to make gestures.
Thanks, Siena
My Tables:
SuperHoldings Title (Primary Key) ZP# Former Titles Title Changes Paper Holdings Microform Holdings Closed (Yes/No Box)
Actives TITLE (Primary Key) FUND NOTES
EBSCO Title (Primary Key) ISSN Notes 1 Notes 2 Notes 3 Vendor Subscription Info (Group of Fields Only Applicable to EBSCO Titles and not repeated elsewhere.)
Directs Title (Primary Key) Fund Notes Vendor Subscription Info (Group of Fields Only Applicable to Direct Titles and not repeated elsewhere.)
JSTOR Title (Primary Key) ISSN JSTOR Holdings JSTOR URL Vendor
I have a query that organizes a drop down A-Z. I am looking for a way to organize it A-Z AND Smallest to Largest.
Code: SELECT tblPartNum.PartNumberID, PartNum & ' - ' & PartDesc AS Expr, tblPartNum.Deleted FROM tblPartNum WHERE (((tblPartNum.Deleted)=False)) ORDER BY PartNum & ' - ' & PartDesc;
I need it to sort it out like this
4 digit #'s Ascending 5 digit #'s Ascending 6 digit #'s Ascending So the list would look like this 1111 1112 1113 11111 11112 11113 111112 111113 111114
I would like to Create a database for indexing / organizing PDF documents. My Company currently use a 3rd party accounts program and we raise sales and purchases using numerical numbers ( but we can Jump to a new range of numbers within the program) for example if the purchase order numbers become simliar to the sales order numbers. But this 3rd party software does not let us scan documents into it hence the need for an alternative database.
For the data base I was thinking if only creating a index of the sales order numbers to start with. the first few question I have are.
1.) Should I Use the Autonumber as my first index column or should I replace it with our own sales order numbers in that column.
2.) is there a way to autopopulate the sales order numbers in a table for historical sales numbers.
3.) can you scan directly into Access
How to start planning the design off the database as I know this is almost the most important aspect of a database.
In the header of my form I have two comboboxes that search the main form to bring up a specific record in the detail section. The first, Combo2, uses the primary key of the table on which the chart is based for the search, and works well. The second, Combo6, is based off of a query that displays patient names concatenated as "LastName, FirstName." I can't search by last name alone because some last names are present for multiple people. Is there any way to take a selection in this combobox and use it as the filter for both the first and last name fields on the main form? Here's my current code for the search:
Code:Private Sub cmdSearchPatientInfo_Click() Dim strWhere As String Dim lngLen As Long Const conJetDate = "#mm/dd/yyyy#" If Not IsNull(Me.Combo2) Then strWhere = strWhere & "([Medical Record Number] = """ & Me.Combo2 & """) AND " End If '====This works fine If Not IsNull(Me.Combo6) Then strWhere = strWhere & "([Last Name] Like ""*" & Me.Combo6 & "*"") AND " End If '====This does not lngLen = Len(strWhere) - 5 If lngLen <= 0 Then 'Nah: there was nothing in the string. MsgBox "You have not entered any search criteria.", vbInformation, "Whoops!" DoCmd.Echo False DoCmd.Close acForm, "editPatientInfo" DoCmd.OpenForm "editPatientInfo" DoCmd.Echo True Else strWhere = Left$(strWhere, lngLen) Me.Filter = strWhere Me.FilterOn = True Me.Detail.Visible = True End IfEnd Sub
I have pretty limited familiarity with VBA, and I'm not really sure how to proceed. Any help would be VERY much appreciated. Thanks!
I have a table containing the following fields: Customer ID Customer Name Countries The ID is numeric, the name is a string and the countries are the tricky bit since it contains a string of concatenated 2 digit ISO codes: "USCAGB".
What I would like to do is convert the concatenated representation to a normalized one. Take this example table:
ID Name Countries 2 John SEDK 3 Maria USCAGB
From the above table I would like to create a new table that links Customer IDs to single countries as
ID Name Countries 2 John SE 2 John DK 3 Maria US 3 Maria CA 3 Maria GB
I have imported a lot of data from excel into a table. There is in one of the columns some multi concatenate info I want to separate in it's own individual row.I am not sure if there is a way to do this in a query or if there was an MsAccess2007 example somewhere. I looked on the internet but couldn't find anything.
I concatenated 2 fields CR_Numbers: = CR_Number & [Sub Number] to display 12.01: from 12 and 1. I am now wanting to lookup the CR_Numbers to edit or add information into other fields.
I want to use a pop up form to edit/add data without having the form add an entire row to the table. How do I reference the Table ID so that it will go to the equivalent row to add the data in?
Main Table = Change Request Email Table = AORB_Email Fields in Form!AORB_EMail: Change Request (CR_Numbers), Priority (Pri_Name), Hours (Hour)
I plan on using the Priority and Hours in the email to set some dates and expiration times as well as in the subject of the email. I have figured out the email setup, and if I manually fill in the Priority and Hours fields everything works as it should.
I have a Make Table query that includes a new field concatenated from a numeric field and a memo field, with some assorted text elements - Issue Description:"("&[number]&") "&[Description]
When I run the Make Table, the new field "Issue Description" is created as a text field, NOT as a memo field.
Is there any way to insure that this field is created as a memo field.
I have a field in a table that is to be concatenated from two other fields, PolPrefix and PolNum. On a form I got it to show the full field 'PolicyNumber' by making the control source =[PolPrefix] & [PolNum].
But this doesn't update the field PolicyNumber in the respective table, and only shows it on the form. How do I get a concatenated field defined by the user into a table so that I can call that field other places in the database?
I am attempting to create a printed label with a concatenated field. I want the field to abbreviate several shapes and colors but i will just start with the shape.
=IIf([Shape]="round","RD","TH")
This is what my basic statement says. I am attempting to nest other options inside of this.
I have a class roster report in which the Employees' names are concatenated in a text box. When I select to sort the concatenated names alphabetically, it is sorting by the Unique ID, which is a letter and four numbers. The letter used to match the last name, but newer IDs are random. So most names are still in order, but some are not.
The Employee field Row Source is:
SELECT [Last_Name] & ", " & [First_Name] AS Employee, tbl_AM_Operators.User_ID FROM tbl_AM_Operators ORDER BY [Last_Name] & ", " & [First_Name];
What do I need to change to have the Employees' names alphabetized on the report?
I have a form in which I have a combobox. The code in the row source is:
Code: SELECT [tblBooks].AuthorFirst & " " & [tblBooks].AuthorLast AS Expr1 FROM tblBooks GROUP BY tblBooks.AuthorLast, tblBooks.AuthorFirst HAVING (((tblBooks.AuthorFirst & " " & tblBooks.AuthorLast)<>"") And ((Count(*))>1)) ORDER BY tblBooks.AuthorLast, tblBooks.AuthorFirst;
This is an unbound box.
The rest of the form contains bound textboxes and other bound comboboxes.
This form is processed by the following VBA code:
Code: Private Sub cmdAddBook_Click() 'Place the authors first and last name in an array splitAuthor = Split(cboAuthor.Value, " ") 'Create a new record with the form information DoCmd.GoToRecord , , acNewRec
[Code] ....
What happens is that it places all the records (Except those in the array) in a field and overwrites the values in that field.
I have used the same GoToRecord statement on other databases and it works just fine. I suspect that it is due to the unbound combobox.
What I need to find a way to place all the data in the corresponding fields of a new record including separating the author's first and last name when retrieved from the combobox. I just need to figure out how to get the data in the table somehow.
I need to be able to extract individual fields from a table as strings in order to then send this data to my EPOS printer (which will accept only strings to print) I have created the table with a query so the question could equally be how can i output strings as query results. Im using Access97 HELP!! I have tried to set up a ADODB.Connection but either Access 97 wont accept it or i've got the coding wrong
I need to make a query to compare equality of two pieces of data, one is a 6 character fixed-length text string (where the first character can be ignored) and the other is a 4 or 5 digit number (long integer, if 4 digit number can be assumed to be 5 with a leading 0).
I do not have the ability to change the way I receive the data, they are on linked tables of different customers.
I tried using various SQL functions to convert the data in the query but they don't seem to work in Access, so I am getting "Type incorrect in the expression" errors.
How do I do this in ACCESS? I would prefer a SQL query solution to a VBA solution if possible, the data is updated VERY often and the application is already doing too much processing on the form_timer() event.
I am trying to pick out the ERAP no.s out of a whole string of data. ERAP no.s are like invoice no.s. Do I Append or Make table? How do i go about doing this? I want the ERAP no. in a row next to the the description field all next to its respective string of data too....
for an example..
1)ERAP43463 STAFF TEAM LUNCH MID-YEAR REVIEW
2) 0507 SUBMITTED ITEMIZED ACCR-ERAP43159
See.. the part where the ERAP no. appears is inconsistant.. if not i could simply use excel and use text to columns. We have thousands of lines like this every week... There has to be a way to pick out just this detail and fill in the column next to the respective datastring...
Can someone please advice me on how i can go about doing this?
I have a table that lists county names. On the data entry form only the county names are displayed. However, I have another form that is used to build a custom query based on various criteria, of which county is one.
To make the table compatible with both both forms, the data source for the dropdown list for data entry is: Select * FROM county WHERE county <> "No Selection"
For the SQL form the code is simply: Select * FROM county The default value of the dropdown list is "No Selection". When dropdown list for county is set to "No Selection" the program interprets it as "select all counties". Other dropdown lists provide other parameters such as the project year, nature of the project etc. Each of these other dropdown lists also have a default value of "No Selection". The SQL form thus gives me the ability to mix and match several criteria. For example, it will display all projects in a particular year for a particular county or all projects in all counties that involve the construction of a residence.
My question, instead of physically having the string "No Selection" in the table itself, is there a way to embed the phrase "No Selection" in the SQL expression itself and still have it as an option on the dropdown list?
