In a query design view, I have two fields, LastName and FirstName. In the Field of a blank column I enter [LastName]&[FirstName]. In the query the last and first names are now connected , like SmithJohn. How do I put a space and or a comma to separate them?
View RepliesI have table1:
Name, Date, Points
Peter, 1.8.2005, 100
________2.8.2005, 200
Paul, 1.8.2005, 100
________4.8.2005, 300
etc. and I need to fill the blanks with Peter, Peter... , Paul, Paul, ... etc. so, that I can later take it as a group ID and work with the data.
Preferably with SELECT query, but insert or update is also possible.
I trying to combine three columns that I have into one column without combining fields.
Currently what I have:
(see image below)
What I want:
ID-----MOC
##----name1
##----name2
##----name3
##----name4
##----name5
etc
The list I have will be much longer and will be changing frequently, which is why I can't just go on excel and manually do this.
Hello,
I did a cursory search of the forum and didn't find anything (probably more my search than the content). I also think I know the answer to this, but I want to clear it up once and for all.
Is it true that I cannot insert an image into an Access Table in a way that it treats the image like data (e.g., it comes up on reports as entered).
If so, and I suspect it is so, what would be the best way to associate a row with a certain image in Access? Here's what I'm hoping to do: I have a list of projects and all of them have a status. Right now the "status" column includes the text "Green," "Yellow," or "Red." I would rather have this text display as green, yellow, or red color rather than text. Is this possible?
Thank you!! :D
Hello,
say for example I have a particular column in Access that I would like to force users to enter as percentage only, is there a way to format the cell as in Excel to create such effect? Thanks a lot!
Regards,
Anyi
Once again, I'm working with a file that I got from somewhere else that isn't in *quite* the right format.
What I have is a table listing all of the titles and their holdings we have in various journal databases. The table looks something like this:
|____TITLE____|___COVERAGE______|____DATABASE___|
|_ABC Journal_|_1998 until 2000_|_EBSCOhost DB__|
|_ABC Journal_|_1953 until 1986_|_Proquest DB___|
|_ABC Journal_|_1980 until 2006_|_Free Journals_|
|_XYZ Journal_|_1899 until 1956_|_CINAHL DB_____|
|_XYZ Journal_|_2000 until 2006_|_EBSCOhost DB__|
|_XYZ Journal_|_1955 until 1999_|_Proquest DB___|
The titles, as you can see, are listed more than once, with one record for each holdings record from each database where that title is found. For my purposes, I need the holdings to show up all together. I can do this in a report, by sorting by the title, but I really need to have it all together in its own cell--one line per title. The above table should look more like this:
|____TITLE____|_______________________COVERAGE____ _________________|
|_ABC Journal_|_1998 until 2000_,_1953 until 1986_,_1980 until 2006_|
|_XYZ Journal_|_1899 until 1956_,_2000 until 2006_,_1955 until 1999_|
I don't really care what happens to the database field, but if it helps, the title and the database fields together would be a unique combination in the table.
Currently, I am working on an exported version of this table in excel, merging the cells 2 by 2 with a function command like this: =A2&" "&B2. Since there are 32366 records, I hope there is a quicker way. At this rate, I'm not sure if I'll be done for 100 years.
I'I need to randomly input values into row cells and when press enter key, it calculates the total value and show in another sheet. Example, in sheet1, I input random values in A1,C1,D1,Z1 and when I press enter key, it calculates the total value and appear in the first row cell, A1, of sheet2. All by VBA coding.
View 1 Replies View Relatedi'm creating a table and would like to have a list of items to chosefrom in a cell to keep from having to type an answer. e.g countries , or Prefix
thank you
I need to update column 'customer' in table 'db' with a name of a customer.
Currently this column is empty from data.
I do following , but it doesn't work.
UPDATE db SET Customer = 'Microsoft'
WHERE Customer='';
Where is the problem.
How do I paste to multiple cells in access 2007? Basically I want to copy one
cell in the dataview and then highlight maybe 10 cells and copy the info from the one cell into the highlighted 10 cells at one time. I had no problem doing this with 2003 but in 2007 this seems to be an issue. Any help is appreciated!
I want to import only the information from an excel sheet like the one below where the answers for Q1 and Q2 are No and Yes respectively.
http://www.dfwls1.com/hosting/albums/userpics/11225/excel.jpg
I'm using this code below that is working but importing all cells:
Private Sub Command31_Click()
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "CustomerRecords", "C:Documents and SettingsmstaffordDesktopCustomerRecords.xls", True, "C1:I20"
End Sub
What do I need to add?
Right, So i've got a table which has records for the Top 40 Music Charts.
I want to be able to find out the averages for each song taken from it's positions in the chart over the weeks:
http://www.playfm.orcon.net.nz/Chart4.GIF
So essentially I get something that tells me
[Lenny Kravits] [American Woman] [24.5]
[Silverchair] [Ana's Song (Open Fire)] [41.1]
and so on...
Any ideas?
Cheers guys,
Alex.
Hi,
I currently have a table of contacts. For the most part, each record has a 'company name' field, an 'address' field, etc. Unfortunately, because I imported this data from a text file (I ran a VBA code which extracted each record from the text file and put them into the table) and because not all the contacts have the same number of fields, the fields don't all line up.
How can I traverse through the entire table and shift the cells so that everything lines up properly (i.e. - all the addresses are under one column and all the fax numbers are under another)? If I have to write some sort of procedure to do this, what approach should I take?
Thanks!
Hello,
how do you connect fields from one table to another? what im trying to do is to connect two fields from product! product id and order details! product id. the one in the order details table should equal whatever i input in the product table.
I need to count the number of cells that match a certain number like >0 and <60. Thanks!,
Mike :confused:
hi all
i have created a database but didnt insert any default value at the begining and now i have blank cells in the table rather than £0.00
how would i go about querying the blank cells and then populating them with 0.00
thanks in advance
chris
This is a form used to enter in the hours worked for each employee for any particular job. What I'm trying to do, is show a total for all the hours worked for the job in a text box under the list. The cells that I want to total up are named "StartTime" and "EndTime," and they come from "tblRelEventEmployee." The form shown here is "frmEvent."
Each job can have anywhere from 1 - 30 employees, and I need to be able to basically have a calculation that does EndTime-StartTime for each employee, and then add up the total for each to give me a grand total of hours worked for the job.
I have a crosstab query which shows me something like:
Activity Quarter1 Quarter2 Total
Call 2 1 3
Visit 3 1 4
Home 4 2 6
and I want to add another row in the query itself or a new query to SUM the Visit and Home.. Like for example:
Visit+Home 7 3 10
Is it possible to do that?
Hi All,
Yes ok DDE may well be dead but it works for me so why change it? Basically I wondered what the commands where to format Excell cells. For example if I stamp a cell with todays date:
DDEPoke intChan1, "R3C2", Left(Now(), 10)
How would I say shade that cell black, change the lettering to white or underline or bold that cell?
Any help most appreciated.
Regards,
Dalien51
Can anyone help with this?
I will routinely be sent a Excel 2000 spreadsheet with multiple worksheets and each worksheets data is to be imported into a related named table in a MS Access 2000 database. No of course I have been using Transfer Spreadsheet to perform the imports but to make sure I get all of the data I use the range A1:AZ65536 (65536 of course being the maximum number of rows available in a Excel 2000 spreadsheet).
What I would like to do however, is just import the necessary number of rows not all 65536!!! Is there a way in code of working out how many rows in the A-AZ column range contain data?
It occured to me that if I linked a table to each worksheet then this would only display the necessary rows and I could count them however, once you have specified the spreadsheet location the code doesnt let me repoint the individual worksheet unless someone knows how to do this???
Any help most appreciated.
DALIEN51
Is there any way I can automatically add zeros to cells with no value?
View 5 Replies View RelatedI have a Query pulling data from several tables. If there are empty cell with no data in them, they cause the Query to completely skip that record. It does not show up in the Query. Not practical since most of the data int the table will not be entered until some time later.
What gives? Is there a way around this?
Thanks.
I think I read somewhere that DCount will not count blank cells, but when I try to do it - it counts everything.
Here is my formula:
=DCount("[E-Mail]","Detail","Not IsNull([E-Mail])" & " AND [Primary] = [Products]![Product Code]")
Basically, I want to count the number of email addresses in the E-Mail column of my Detail table IF the Primary column (also in the Detail table) equals the product code on my form AND if there is something in the E-Mail column for that line. When I did the above formula - and I've done a bunch of different variations - it keeps counting all lines that match the product code.
I have a query that has 11 dates on it, I am trying with iif statements to get it down to one column reflecting the most recent date out of the 11, for example 5/2/14 and 4/9/14, I would want to have 5/2/14. The problem with this is that most of the cells are empty and and I can't calculate empty date cells. my thought was to create a statement that goes something like this iif([Date1]="", "1", [date1]), obviously this does not work but I would like for it to show something other then nothing so that I can calculate on it.
View 6 Replies View RelatedI'm trying to execute a query which is looking at data from 2 different tables and most of the data fields in the tables are the same. For instance table 1 has Dept, Res, Period_date, & Available Hrs and table 2 has Dept, Res, Period_date, Project & Forecast Hrs. The results I'm looking for are as follows:
Dept Res Period_date Project Forecast Hrs Available Hrs
11 A 11-02-07 123 XXXXX
11 A 11-02-07 456 XXXXX
11 A 11-02-07 789 XXXXX
11 A 11-02-07 012 XXXXX
11 A 11-02-07 XXXXX
But the "Available Hours" are duplicated in the preceeding 4 rows.
Thanks for any advice on this.
I am using Access 2010 . I need to format cells inside the Excel spreadsheet
Here is my code:
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim intColumn As Long
Set ApXL = CreateObject("Excel.Application")
[Code] .....
The code "With xlWSh.Selection" returns a run-time error 438 - Object does not support this property or method
When coding, when entering a "bang" ("."), Access normally returns the next piece of code. The code above does not! How to I correct this?
I do have a reference to Microsoft Excel 14.0 Object library....