Join Three Fields Together And Create A DATE Field
Feb 20, 2007
I am editing data in a table that was designed by someone else. For some reason, they divided the date into three columns (YR, MON, DAY). I would like to join these three fields together and create a legal DATE field. I have tried the following:
SELECT YR AND MON AND DAY AS TheDate
FROM MyTable;
However, it returns an odd result. I think this might be because ACCESS does not know I want a date format.
My question is: is the above QUERY correct and how do I tell ACCESS to put the concatenated data in a date format.
And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.
Code: SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID) INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID ) ON trains.trainID = bookingLeg.tid) as firstJOIN
Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?
I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"
I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =
Code : DateAdd(d,2,[IncidentDate])
so I removed the parenthesis and nothing happened
Code : DateAdd d,2,[IncidentDate]
I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either
Code : DateAdd d,2,[ContainDueDate]
I'm not sure if I'm even using the right function to get what I want.
I've got a fields called rev code that contain the following values:
field name: 110 131 250 255 258
field value: 7.49 6 11.25 12.11 78
I'm writing a query that pulls from the first two digits of the rev code and need to round off to the nearest dollar so in my query I'll have a column 11 with a value of 7, a column 13 with a value of 6, then I need to take columns 250 255 and 255 add the values together and round off so I get a column 25 with a value of 101.
Hello, I have a big table like this: ID Name Race1 Race2 Race3 -- ---- ----- ----- ----- 1 a Asian russian
How do I create a query to be like this (either in query or report)? I like to combine three fields (race1, race2, race3) in one field (Race), and break any value of races to the 2nd line, 3rd line, if there is a value... ID Name Race -- --- ---- 1 a Asian Russian
In Oracle and SQL Server, we can combine a few rows to be columns-like, but in Access, is there any way to do that? and how?
I am creating a repayment schedule (as a report) and I want to display a series of fields as a column which return (show) a date one month after the date in the field above.
The first repayment date field (Line 1) will show a date one month after the loan was paid out. the Next field below will show the date one month later.
I can't simply insert the "Date + 30" because that would get out of since over the year. If the loan was issued on say the 15th of January, I need the first field to display 15th February and the next would be .... 15th March.... Yes - You've got it!
Now I could do that in Excel, but I don't know how to get Access to do it.
The interface being used is a main form with various tabs and a subform on each of these tabs.
There is one field ('max power density') in my database that is calculated using 'Max Rated Power' and 'Cylinder Capacity' however these are in different tables and subforms. The 'max power density' and 'max rated power' are in table and subform 1 but 'cylinder capacity' is in table and subform 2. Is it possible to keep them in separate tables/subforms and still calculate the field?
Working in MS Access 2010. Data must be entered and updated in Access only. Trying to figure out if it's even possible to auto populate dates in multiple fields in a form that are triggered when I enter ANY date in a primary date field on the form. I have close to half a dozen fields I'd like to do this for but here are a couple of the fields I'm working with:
Re Insp Date 90 Day Notice(Form field to be triggered and show a date 90 days prior to the trigger date) Re Insp Date 60 Day Notice (Form field to be triggered and show a date 60 days prior to the trigger date) Re Insp Date (Trigger date)
What I'd like to do is enter values in the "Re Insp Date" field (in the form) and have it trigger/auto-fill/auto-populate the "Re Insp Date 90 Day Notice" so that it shows the date 90 days prior to the "Re Insp Date" and the "Re Insp Date 60 Day Notice" so that it shows the date 60 days prior to the "Re Insp Date"........all this, and have it auto-update the queries and tables its linked to.
I've tried looking online and in instructional material to see if there is a formula, equation, macro, an expression or VBA coding that I can use to execute this but am having no luck.
The MAIN OBJECTIVE is to have the the linked query and table update automatically. Possible??
I have tried doing =([Re Insp Date])-90 but no dice...
I am currently working on ODBC linked tables to our webend system. I need to create a join to a lookup table but I cant seem to get it to work as it only seems to show me results from one of the tables not both? Ive tried LEFT and RIGHT joins plus INNER JOIN.
I think my question is fairly simple - fingers crossed.
I want to create a field that, whenever a record is added, it auto-adds the date & time the record was created. I'd call it Book_Date_Added, or something like that. I tried snooping around the Default Value options but couldn't figure it out.
The table exists and it already has some records, so I'd need to know how to add this field rather than how to create it when I create a table (although that would be helpful too, if it's somehow different).
I think I know the answer but want to check. I've been asked to create a query, without querying a query first, but it's the only way I know.
I have two tables
Table1 will have data in a column that is 9 characters long ULCABC123 ULCABC124 ULCABC125 PLTABC123 PLTABC124
Table2 will have data in a column that is 6 characters long ULCABC PLTABC
Question: Can I create a Join from Table2 Field with the Left(Field,6) from Table1
I was thinking something like this. (but then I can't enter design mode) Query1 - Test
Code: SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION FROM Table2 INNER JOIN Table1 ON Table2.ORDDETTYPE = Left(Table1.ORDERDET,6) GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;
I presume the only way to do this is first query Table1 (and call Query2) and return the first 6 characters and the create another query (Query3 in this case) using Query2 field joined with Table2 field.
Query2
Code: SELECT Left([ORDERDET],6) AS NEWORDDET FROM Table1;
Query3
Code: SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION FROM Query2 INNER JOIN Table2 ON Query2.[NEWORDDET] = Table2.ORDDETTYPE GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;
Looking to create two command buttons or two keystrokes sets in an Access 2007 form that will allow me to place a current date and time in any allowable field where the Cursor is presently placed. Similar to what was in Access 2000, ctl: (for the current date) and ctl shift : (for the current time).
I have 4 queries in which data needs to be connected from the date and shown as a single date showing each sections entry in a row and a cumulative total is maintained as the balance .
and what i need to is something that will link all sku 1 category names in a new field with a | divider and then all the sku 2 category names together with | as a divider and so on. the amount of categories is different for every sku.
And if this is easy enough, its not that important though but to delete duplicate category names when it transfers them across to a new field joined together.
I currently have two tables each containing information that I need to complete a query, however the results of suming this information is incorrect.
Below is the structure of the table, one contains more fileds than the other however the joins are on the Date and the Name fields. I have added a few dummy results to show the format
Table 1 "Date" "Name" "Times Logged In" 10/01/2013 Chris 1 11/01/2013 James 3
Table 2 "Date" "Region" "Manager Name" "Name" "Times Logged Out" 10/01/2013 Scotland Cindy Chris 1 11/01/2013 Wales Robert James 2
Query Fields
"Data" "Region" "Name" "SUM Times Logged In" "SUM Times Logged Out"
I can see what the issue is, if someone hasnt logged out there would be no entry in the table so the result query would show "Times Logged In" as X and a blank value at "Times Logged Out." However if there is a value at "Times Logged Out", "Times Logged In" is displayed again and causes the SUM calculations to be incorrect.
how do I overcome this issue though? I can do two spereate queries on the tables to produce results for Times Logged In and Times Logged Out but cant seem to create one query to display both these results in one table.
What I want is IF the date in Table 2 = Date in Table 1 AND IF Name in Table 2 = Name in Table 1 THEN take the value from Logged In and place that in a column and the value from Logged Out and place that in a column.
I'm having problems doing a left join with 3 fields. I know I don't need the 3 fields for this example to align the data but, I deal with much more than this and need this functionality.
The query object I'm looking into is named 'left join' and the sql is as follows:
SELECT Act_Base.CustID, Act_Base.Chipset_ID, Act_Base.Product, Act_Base.Qtr, Act_Base.Qty AS Shipments, SE_Base.Qty AS SE FROM Act_Base LEFT JOIN SE_Base ON (Act_Base.Product = SE_Base.Product) AND (Act_Base.Qtr = SE_Base.Qtr) AND (Act_Base.CustID = SE_Base.CustID);
I'm expecting to see 150 units in the SE.Qty field.
I've got an issue pertaining to inner joins within an update query. This query will not run now (giving the expected "must use an updateable query" error) due to two fields involved in one of the inner joins are now both memo fields.
Is there any way (and I assume there must be in vba) to recreate this query so it will run with inner joins involving memo fields?If not, is there a way in vba to do a find and replace within a table? For example, replace the "A" in "A-001" with "B"?
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
I have 2 tables (tblItemsQB and tblItemInfo) that have a 1 to 1 relationship linked by the "PartNumber" field. The tblItemsQB table contains a description field and 2 price fields (this table is downloaded from Quickbooks). The tblItemInfo table contains additional fields with information on the part that isn't contained in Quickbooks, like OrderType, Phase, SubSystem.
I import the items from QB into tblItemsQB and then do an outer join with tblItemInfo to link all the information for the part. If I have created a new part in Quickbooks, when I do the outer join (I include all records from tblItemsQB) I can see the new part but since the part number doesn't exist yet in tblItemInfo, I can't add the information to the fields in that table.
What is the easiest way to create the new PartNumbers (those that exist in tblItemsQb but not in tblItemInfo) in the tblItemInfo table so I can add the other information to it?
Hello, exactly how Access 2002 and SQL operate is still a bit fuzzy to me.
In brief: tableAccounts has fields for Signatory1ID and Signatory2ID, and other stuff tableSignatories has ID for a primary key and has SignatoryTypeID as a field tableSignatoryTypes has ID for a primary key and SignatoryType as a field
For each account, on a Form I want to show the Signatory and SignatoryType info for both signatory1 and signatory2. I have tried creating various queries and INNER JOINing the tables and/or queries together, but whenever I manage to get all the information displayed, none of it is editable. Access seems to lock the various text boxes and combo boxes.
I am undertaking an analysis of corporate mobile phone data based on data from the phone provider. The base data consists of a number of monthly text files at transaction level for voice calls, data usage, billing/tariff information, international calls/transfers etc. I have set up VBA code to import the text file data into a number of Access tables.I would like to have a way of joining these tables but the only common field is the phone number (a text field with the groups of numbers separated by hyphens).
I confirmed that joining the tables on the phone number text field does not work.My idea was to create an additional table with just one row for each phone number and link that to the other tables by the ID in the new table. I was able to create this table [PhoneNumbers] (by creating a totals query of the phone numbers from the main call transactions table, I then dumped it into Excel and then imported it into a new Access table with an auto-generated ID column).My problem / challenge is how to get the ID column from my [PhoneNumbers] table to appear in each of the other tables so that I can join them effectively. In the Excel-world, I would have used a vlookup function.I even thought of performing this as an interim step in Excel but there are too many records / rows in some of my tables. It seems that the dlookup function is not what Im looking for and even if the IIF function is suitable, I cannot get the syntax to work for me.
Here is my current table structure (I have omitted some fields from this example and have given some sample data in italics to make the table structure more clear.
tblEmployees
ID (autonumber) 3 EmployeeName John EmployeePhone 555999555 EmployeeLocation New York
tblClients
ClientID (autonumber) 1 , 2 , 3 ClientName ABC Company , XYZ Company, PQR Company fkeyLocationID
tblLocations LocationID 1 , 2 Location New York , Chicago
The junction table tblEmployeeClients only stores ID of the Employee and in the second column (which is a multi-valued field), the ID of each of the clients the employee Supports.
I am trying to generate a report that lists say, EmployeeName alongside the clients supported by the Employee (listing the client location is not required, however, it would be good to know how to do that as well).
The report (for the example above), should look like this:
Name Clients Supported John ABC Company, PQR Company
Currently, I am able to get :
John 1, 2 i.e the client ID for the clients that the employee supports instead of the corresponding company names.
