I'm not sure what the definition of this type of concatenation is, so I'm having trouble searching for it.
I have three tables - one is tblNames (with ID, first_name, last_name) with 25 records, and tblStates (with StateID, state_name, state_abbrev) with 50 records, and the requisite join table between them - tblNamesState (with autonumber and the two FKs), because some of the folks in tblNames can have locations in multiple states. Good enough.
But now it's output-to-spreadsheet time, and I want to run a query that will give me 25 records, and concatenate the location records in a single row. An example would be:
first_name | last_name | state_name
John | Johnson | AL, AK, TX, FL
Eddie | Edwards | MA, TX, FL
Sally | Smith | VT, WA, CA, TX
How do I concatenate records from a single column?
I wonder how can I list all the column names for a given table using a single query? I know how to do it in SQL using system tables, but no idea how to do that in Access.
Is there any way I can use data in the row as a column header ? I only have 1 row of data which i want to use as a header to I can link it to other tables/queries.
I cannot seem to find a way to upload several rows of data to one column in Access!I have about 9 columns already in the table which have 900 rows each. These include names, numbers, address etc.I was asked to add a column on next to the 9 columns with corresponding User ID such as 0093457FX (Which I really do not want to add manually).Every time I try to upload an excel file the data either goes below the current data from the 9 columns on the new column and does not match the current data set, or it does not show up at all.
I have a table that has two columns "Nationality" and "Gender". I wanted to run a query that will Group the nationality and then split the gender column into two columns and after that it will count both gender columns for each nationality. When I posted this question in "Reports" section I got the suggestion to use the SELECT COUNT in SQL. It worked but only for nationality. I couldn't get it work for the Gender column. I searched alot and the only thing I got was the SQL function to split data from one column into two but that also didn't serve the purpose (check the link to see why: [URL]) At last, I went on to create a cross tab query. Selected Nationality as Row header, Gender as Column header and Customer ID as calculation point. And there I got the result I needed. The SQL Code looks like this:
Code: TRANSFORM Count(Register.[Customer ID]) AS [CountOfCustomer ID] SELECT Register.National, Count(Register.[Customer ID]) AS [Total Of Customer ID] FROM Register GROUP BY Register.National PIVOT Register.P_Gender;
I have a data base with names, email IDs, phone numbers of 5 types(example quality, security, IT,finance etc) of person from ten different project teams. I created a basic form for the end user to enter values and created fields for each of the person's name, email ID etc based on the personal type( liek quality, IT, finance etc) on each team. The background table contains records of almost 200 people now.
All of these people will be trained for one task and some will be trained for multiple tasks. I know how to make the combo box allow multiple values/selections but, i cannot list out all 200 personal names in the combobox's rowsource/value list. Is it possible to combine all different name fields and list out all personal(of one team), so that the end user can just select multiple names for that team who have been trained and so on. i need to set the rowsource of the combobox to the values of all personal from one team so the team leader can just go select who has been trained and who has not.
How to get this one to display in a single column.
I know how to do this wiht VBA. But, this output will need to reside on a SQL Server View. So I need a SQL language solution. If it can work in MS Access Query, it won't be too difficult to test then translate to SQL Server.
Customer Table with PK Customer_ID.
There are two tables with FK Customer_ID.
1. Table Lease1 - Has 3 Fields - the form code enforces No Fields -or All Fields. The red * indicate a Required field - These 3 are entered together.
2. Table Lease2 - Has 1 field with 0 to Many records.
Goal:
The Type shows up in a single column.
Each Type shows where the data comes from (Lease Type, Surface Owner, Mineral Owner, or Hz Lease Type)
Challenge:
Lease1 table has 3 fields that need to be transformed into a single column.
Lease2 table has 1 field to be appended to the single table.
Then, there is the column that identifies where the data came from based on the column name.
I have a database with a table with company names, then a relationship to another table that shows that companies' address, but I also want there to be an address 2 and 3 and so on, and some of our companies have multiple sites.So what Im asking is how would you be able to show multiple data, on a sing record.
Hi everyone . Please I need help with this I have a db with 22 records: (A123,B456,C789...Z999) I want to be able to read each record and extract from each one the same data fields and make a big single record out of the reading:A23+B56+C89...Z99 The reason of doing is I want to be able to show on a form the data extracted from the multiple reading. I tried a crosstable, but gave no much room to work with. I think if I use 22 different queries it wont look professional and hard to maintain. I've being considering better a loop with maybe multiple if conditions but I dont know how to apply it. Something like newrecord = "" for x=1 to 22 read record x newrecord = newrecord + record x next
Thanks for taking the time to look at my problem first of all. I have a form that shows records but I want a button next to each record that will append that one single record to another table that is built exactly the same, which is for historic records. Not the brightest on Access and I can only manage to append all records.
As an example, I have an employee record that have a task, when the task is complete I want to append the record to a historic table that I can look up later on. However, just that one employee and not all that are in the current table.
I already have a form where you specific the date and the store I've purchased something at. I want to be able to add several products I've purchased to a table and define their price, using the same form, so I don't have to input the date and store type for each purchased product.
So, when the form updates the table, it will create a new record for each product, using the product description and price - defined for each product - and using the date and store - defined once - as elements of the record. Is there a way of doing this?
I'm quite familiar with creating tables, forms and queries with the design wizard - not so familiar with using scripts, but I've had a small amount of experience with manipulating databases using MySQL, so I'm willing to learn anything that may be necessary to achieve what I need.
I tried the idea sugested in post (http://www.access-programmers.co.uk/forums/showthread.php?p=423080#post423080) but it didn't seem to give the cross referencing that I had hoped for .... example 1 is related to 4,3, and 5 while 6 is related to 1 thus implying an extended relationship to 4,3, and 5.
In a standard one-to-many I'd look at record 1 and see that it is related to 4,3,5 but if I were to look at record 6 I'd only see that it is related to 1. How would I set up the table relationships to drill further to see that 1 is also related to 4,3,5?
How do you do a many-to-many from one table back to that same table?
tblEvent EventID(AutoNumber) EventTitle(Memo) 1 memo content 2 data 3 more memo content 4 some info 5 more stuff 6 other text
Hi I would like to exclude two specific records from my recordset.
For example suppose I have a Flights Database and I want to see all the flight records for Aircrafts 132 and 232 only, between the date jan 1, 2007 to Sep 12, 2007.
The above I know how to do achieve...
([FltNum] = 132 or [FltNum] 232) AND ([FltDate] >= Jan 1, 2007 and [FltDate] <= Sep 12, 2007)
However, among this set of records I want to exclude flight 132 on Aug 1, 2007 and flight 232 on Sept 1, 2007.
([FltNum] = 132 or [FltNum] 232) AND ([FltDate] >= Jan 1, 2007 AND [FltDate] <= Sep 12, 2007) AND NOT ([FltNum]=132 AND [FltDate] = Aug 1, 2007 ) AND NOT ([FltNum]=232 AND [FltDate] = Sep 1, 2007)
I haven't been able to accomplish this using the query grid. Is it possible or do I have to write SQL?
I've recently been told how to enable/disable fields in a form by ticking/unticking a box. Is there a way that, on a tabulated form, i can only disable/enable the field for the same record as the one the check box is in rather than for every record?
I have a form in datasheet view that has a user directory with a list of each person. On each row you can click to open another form that contains the users details using the below VBA:
But once you open to that specific record, no other records are available to navigate to.
I want to be able to stay on that detail form that was opened and go to another record.
How do I continue to pull up that specific user in my detail form, but also load the others so I can navigate to them from the detail form without having to go back out to the datasheet form?
I've been trying to get my head round this one , but i'm just to thick to get it.
I have a continuous form that lists all items at a certain location. The user selects a record by clicking on the record selector and then clicks on a command button with this code:
The user then enters inspection details in to the "DivingInspectionCert" form which i want to store in a table (DivingCert) which will relate to the item. On completion of this form the user then clicks on a command button with this code:
DoCmd.RunCommand acCmdSaveRecord
If Me.RecordsetClone.RecordCount = 0 Then MsgBox "There are no items to Print", vbInformation, "EquiTrac" End Else DoCmd.OpenReport "DivingInspectionRpt", , , ("EquipmentID = " & Me!EquipmentID) DoCmd.Close
When i click on this button, the report doesn't print and i get a message "No current record". The inspection details are not stored in the table. I did have the forms RecordSource based on the table "DivingCert" but that didn't work and i have just tried a query but it is still not working.
Would be much appreciated if you can help me. Thank you
Is there a way to make a form that will display all records based on a single date, at the same time, in the same format each and every time?I have a table which has the following fields:
ID (Autonumber, PK) ServiceDate RunningNumber BonnetNumber Deallocate (yes/no)
Now, I would quite like to keep the form in a style similar to all the others I have, not least as I have to cater for users of all age and abilities, so keeping things as simple. I have attached an image - each row to represent a record basically, I would like the form to open and show the same layout on each day (I would place the textboxes etc in route groups); a null value would not be allowed for at least one field in each record, I could force the records to populate the form in the same way each day?
How to design a form for a table based all records to design in a single form without top to bottom list wise and without scroll bars. Need to form design for all record details are will show on a form as side by side only as horizontal list wise.
For Ex. I have attached the screen shot image as per horizontal wise records continues.
