I have a link table that has a field that represents dates - but they are actually just text. It's a long story but the source is not going to change - so I have to try and deal with it. I need it to behave as a date - and am hoping to do this in a query. The data looks like this:
7/24/12 10/08/13
I have tried various things but it does not seem to totally do the trick - if I sort on it it still does not sort as one would expect from an actual date field. How do I do this?
This may be a simplistic question but I'm having some diffuculty figuring it out. I have multiple rows of data where I need to use the "Unique Values" property in my query to limit only what I need. However, I have a "time" field and their are similar times on different records. So say I have a 100 records but 8 of them have the same "time" value, how can I use someting like the "Unique Values" property to get my rows down from several hundred to 100, but not lose the 8 similar rows and end up with 92 rows, when I have to turn around and sum these times and need all the times to be there?
Maybe I'm approaching it the wrong way and should be doing a SUM of "times" to begin with for that field, but I'm not being able to get that to work either. Any suggestions? /Thanks
I am trying to join two tables in a query. One of the tables has only the fullname while another has only the surname (this table is imported from an external database). I need to be able to match the two tables up. Can anyone please suggest how to do this or even if this is possible.
I am having an issue with duplicates in my table. I have a table, called "Part Mods", that has about 12 fields in it. the first 2 of them are "Mod" and "Part Number". I have a form where someone can insert data into this table and what i want is for them to NOT be able to insert a record with the same Mod/Part Number combination of an already existing record. I have "indexed" turned on but I am pretty sure that is for every field.
In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 08/07/2006, 22/07/2006, 05/08/2006 etc
I have a column in an Access table listing various dates. I want the next column to be populated with the next pay period end date after that date.
So if DATE is 05/07/2006 I want PAY PERIOD END to be 08/07/2006 and if DATE is 09/07/2006 I want PAY PERIOD END to be 22/07/2006 etc
I have this table ("people") and an example of possible rows:
id(key) COL 1 COL2 department country name 1 xx yy KPP USA John 2 zz kk KPP USA John 3 ss ff TLL USA John 4 ww qq PPO Italy Marco 5 jj uu PPO Italy Marco
I have to count the number of distinct DEPARTMENT for each NAME; so, for John should be 2 (KPP and TLL) and for Marco 1 (PPO).
I have tryed in this way:
SELECT COUNT(DISTINCT department) AS NumberOfDifferentDepartments FROM people GROUP BY name;
I have 3 tables, with the same field in each of the three tables. I need to find numbers (within those fields) that are similar in all 3 tables. If a number appears in all 3 tables, then all the data for that number need to be pulled from each table and placed in one row, all corresponding to that one number.
I can get this to work for 2 tables, using a query, but not three.
I work for a voluntary organisation in a hospital environment. Older patients coming into A&E will get sent a letter from us offering help.
We send out about 3,000 letters a year and have just over this number of records on our simple Access 97 database.
The problem I have is when we enter data using a Form in the field Address Line 1 if we have sent a letter to this address before I want somehow for the program to inform me of this. However, I do not want it to stop me from entering the same address again so I can't use the No Duplicates option. The reason being, we may have sent the patient a letter six months ago in which case we would want him to receive another but if we only sent him a letter say 3 weeks ago then we wouldn't send him another. Alternatively, his wife may now be a patient and will get sent a letter.
At present we look at the admissions list, sort our table into Surname order and check if they have been sent one previously, however this is very time consuming and would value anyone's thoughts.
Unfortuantely, I can't program so tend to use the design features and wizards of Access 97
Does this makes sense?
I appreciate the time any of you may take in answering this Thread. As you can see I am a newbie to this. Thank you in advance.
In my Access Database, for each row, there are two queries I want to pull data from to give me the status of the item in the related columns. In Excel, I use one file with multiple tabs to vlookup the data. How would I accomplish this in Access?
For Example, Jacksonville has a value of Submitted in the Completed Checklist Column and Approved in the Parts List Column. These values currently come from two separate tables. How do I get my database table to update when the status changes for each of the columns?
I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
I need to input a string into a column named "EventType". The code should first check if the column "Agent Name" contains any strings. If there is none, it will input "IBM Director" into the EventType column.
Once it has looped through the agent names, the code will then loop through the Details column and input into EventTypes based on what is displayed within the string.
These are the codes that I am using to achieve this, however nothing is being input into the EventType column.
Code: Private Sub Command11_Click() Dim dbs As DAO.Database Dim rst As DAO.Recordset
Set dbs = CurrentDb Set rst = dbs.OpenRecordset("Final")
[Code] ....
I think the problem lies with the code that checks the agent name. When I removed it, it managed to populate the EventType column based on the details. But I still need to find out how to check the agent name too.
Hi. I have a question I'm hoping someone can help me with. I would like to take data from multiple columns and put the data into one column. Additionally, I do not want to exclude any data (union all) and I would like to group the resulting union by another field. For example:
So far I'm using the following SQL. What do I need to add or change to get my desired result of grouping the unioned depths by the 'sample event' field?
I appreciate any help anyone may have to offer. Thank you.
SELECT Depth1 AS Depths FROM Depth_Velocity_Substrate_Correct Union all SELECT Depth2 FROM Depth_Velocity_Substrate_Correct Union all SELECT Depth3 FROM Depth_Velocity_Substrate_Correct Union all SELECT Depth4 FROM Depth_Velocity_Substrate_Correct Union all SELECT Depth5 FROM Depth_Velocity_Substrate_Correct
I have a Access 2003 file and I want to filter anywhere where there last name is "expired" and change the column first name to say "no". How do I do that?
I need a query that could split data into 2 different field. The table pic is attached for your ready reference. I tird with Left & Right funtions with no results.
Any one can help me please.
Table name is Agcad and field on which I need to split is Board_Size. The problem is Board_Size data is sometime in 3 digit (first half part) and sometime second half part is in 4 digit. It is a text data type field.
I have being playing with ms access but I really don't know much about it or databases in general.I have created a very simple database to gather twitter following/followers data for research purposes.One table (table01) has a field for the "boss" user (=the user who I gather data for), another field for "client" (=bosses followers or friends).Both fields are numeric and contain the users id's.In order to distinguish if the link is follower or friend there is a third field, called type which can be either 1 (=follower) or 2 (=friend).So the data would look like this:
boss - client - type 12345, 67890, 1 12345, 54321, 2
If user with user id 12345 had a follower (type 1) with user id 67890 and a friend with user id 54321...In order to avoid getting duplicate rows I also added a unique identifier which is of the form boss_id-user_id-type.So the above row looks like this:
That works just fine.For several reasons I also needed data of the form source - target.So I also made another table (table02) of this form.
67890, 12345 12345, 54321 ...
In table 2 you don't need the "type" field since the position of the user id shows the type of relationship.Still, you need a unique identifier in order to avoid duplicates, so I added on with the form: source_id-client_id..So table02 lookes like this
Both tables also have a date/time stamp for each line.As you can see, table01, having also a type field is bigger than table02.The problem is when I try to append data, exactly the same data in both tables.Appending data to table01 is ok, while appending data to table02 (which is smaller, having one less field) takes a really long time, maybe 10 times as long as appending data to table01.To make sure that no query's are causing the problem I have tried first creating temporary tables with the data to be appended, no duplicates, nothing that would cause the database to make extra calculations and used a simple update query with no filters to append data.Still I get the same result, table02 takes a very long time to finish while table01 finishes in no time.
I've been having trouble trying to figure out how i can add the values within one column. I have a field named Cost, under that field i have serveral rows of data. I would like access to add all the numbers under that "cost" field. Im not sure how this is done. Can anyone please help ???
I have a table with numberous records with Values in a number of fields. In a report I am showing all these records but at the end I want to tally the values.
Whats the best way to do this?
I thought of placing a text box in the form that will run some code to calculated the total....
I am having some trouble with a program that contains a query that is suppose to sum a bunch of market values that have the same ID and is from the same portfolio.
The problem I had at first was that some of these IDs and portfolios that should have been summed together had one called counterparty that had different values.
So what I did was that I deleted that column, but I am guessing that the table somehow might save that they have this difference. How I can make them appear in the same sum?
I have two separate columns in my query but they contain the same kind of data. These two columns data should match but some of the data doesn't. Can I write a criteria that will show me only the rows that do not match.
DUTYPOSC 11B111B1 11B111B1 11C111B1 11B111B1 92Y111B1 11B111B1 13F121B1 For example, I want the query to show me only the rows in which the data in the Duty column doesn't match the POSC column.
