I have a table named "Patients" with a field named "Code" and another field named "Patient" which contains the full name (LastName space FirstName) "Chaviatsos Nick" for example.
I want to make an other table from the table "Patients" with three fields:
1. A field named "Code"
2. A field named "LastName" which will contain the Last Name of the field "Patient" of the table 'Patients" and
3. A field named "FrstName" which will contain the Frst Name of the field "Patient" of the table 'Patients"
How Can I manage this?
I'm trying to trouble-shoot an undocumented datbase built by someone else and running into a recurring problem. She has it set up where a macro runs several queries. When these macros are run, the queries cause "Division by Zero" errors which stops the macro in its tracks. The following is a query that causes this error, but by looking at this query, I see no place where a division takes place. I'm assumming that another porblem is occuring and Access just states that it's division by zero error when it's actually something else.
1) Has anyone seen a "Division by Zero" error when running a query when it's actually something else causing the problem? 2) Can anyone see from the query what could possibly cause this and give me a hint as to a good way to trouble-shoot these queries to possibly handle whatever problem is occurring.
Thanks,
The query is as follows: SELECT [Buyer Category Fiscal Period for Report].FiscalMonth, [Buyer Category Fiscal Period for Report].FiscalYear, BuyerCategory.Buyer, BuyerCategory.Description, BuyerCategory.CategoryID, [008c GMROI by Category].[Category GMROI], [016c MTD GM by Category].[MTD GM], [017c YTD GM by Category].[YTD GM], [010c Turns by Category].[Category Turns], [005c MTD Cogs by Category qry].SumOfNetCogs, [004c YTD Cogs by Category qry].SumOfNetCogs, [006c MonthEnd Inv by Category qry].MonthEndInv, [014c GMROI Buyer Category].BuyerCategoryGMROI, [018c MTD GPM by Category].[Buyer Cat MTD GM], [019c YTD GPM by Category].[Buyer Cat YTD GM], [015c Turns Buyer Category].[BuyerCategory Turns], [005c MTD Cogs by Category qry].SumOfNetSales AS [MTD NDS], [004c YTD Cogs by Category qry].SumOfNetSales AS [YTD NSD], [Buyer Category Fiscal Period for Report].EndDate FROM ((((((((((((BuyerCategory LEFT JOIN dbo_Category ON BuyerCategory.CategoryID = dbo_Category.CategoryID) LEFT JOIN [008c GMROI by Category] ON BuyerCategory.CategoryID = [008c GMROI by Category].CategoryID) LEFT JOIN [016c MTD GM by Category] ON BuyerCategory.CategoryID = [016c MTD GM by Category].CategoryID) LEFT JOIN [017c YTD GM by Category] ON BuyerCategory.CategoryID = [017c YTD GM by Category].CategoryID) LEFT JOIN [010c Turns by Category] ON BuyerCategory.CategoryID = [010c Turns by Category].CategoryID) LEFT JOIN [005c MTD Cogs by Category qry] ON BuyerCategory.CategoryID = [005c MTD Cogs by Category qry].CategoryID) LEFT JOIN [004c YTD Cogs by Category qry] ON BuyerCategory.CategoryID = [004c YTD Cogs by Category qry].CategoryID) LEFT JOIN [006c MonthEnd Inv by Category qry] ON BuyerCategory.CategoryID = [006c MonthEnd Inv by Category qry].CategoryID) LEFT JOIN [018c MTD GPM by Category] ON BuyerCategory.CategoryID = [018c MTD GPM by Category].CategoryID) LEFT JOIN [019c YTD GPM by Category] ON BuyerCategory.CategoryID = [019c YTD GPM by Category].CategoryID) LEFT JOIN [015c Turns Buyer Category] ON BuyerCategory.Buyer = [015c Turns Buyer Category].Buyer) LEFT JOIN [014c GMROI Buyer Category] ON BuyerCategory.Buyer = [014c GMROI Buyer Category].Buyer) LEFT JOIN [Buyer Category Fiscal Period for Report] ON (BuyerCategory.Buyer = [Buyer Category Fiscal Period for Report].Buyer) AND (BuyerCategory.CategoryID = [Buyer Category Fiscal Period for Report].CategoryID) GROUP BY [Buyer Category Fiscal Period for Report].FiscalMonth, [Buyer Category Fiscal Period for Report].FiscalYear, BuyerCategory.Buyer, BuyerCategory.Description, BuyerCategory.CategoryID, [008c GMROI by Category].[Category GMROI], [016c MTD GM by Category].[MTD GM], [017c YTD GM by Category].[YTD GM], [010c Turns by Category].[Category Turns], [005c MTD Cogs by Category qry].SumOfNetCogs, [004c YTD Cogs by Category qry].SumOfNetCogs, [006c MonthEnd Inv by Category qry].MonthEndInv, [014c GMROI Buyer Category].BuyerCategoryGMROI, [018c MTD GPM by Category].[Buyer Cat MTD GM], [019c YTD GPM by Category].[Buyer Cat YTD GM], [015c Turns Buyer Category].[BuyerCategory Turns], [005c MTD Cogs by Category qry].SumOfNetSales, [004c YTD Cogs by Category qry].SumOfNetSales, [Buyer Category Fiscal Period for Report].EndDate ORDER BY BuyerCategory.Buyer;
I have a field in the a query with the following: % OVER NB: 1-([Monthly MTA Table]!NB/[main inventory table]!PRICE) I then format the column in properties to percent When I try to put anything in the criteria I get an error "Division by zero"
I want to only the records that are greater then 80%
I have a query with the following calc. but it has an error of 'division by o' when I run it. Some of the figures may be null or 0 within the fields and I still want it to return a % figure result
I know its probably the iif function but am not sure how to use it in this instance.
Process %: Sum([Process volume]*[se]/[minutes worked])
Please help me with the round function. I want .5 to round to 1. Here is an example of my data: (18+18+18+20)/4 = 18.5 rounds to 18. I want it to round to 19.
I used the following expression: RoundACT Composite Score: Round((([Column1]+[Column2]+[Column3]+[Column4])/4),0)
I've been using the following code to show Random records from an Access Database, the only problem with it is that every now and again it throws a "division by 0 error" any ideas how i can fix?
Code:strsql_videos = "select top 5 * from items ORDER BY RND(id)*(id*1000) MOD datePart('s', NOW()), id;"set rs_videos = server.createobject("ADODB.recordset")rs_videos.open strsql_videos, conn_videos, 3, 1, adCmdText
Iam hoping its possible with a few lines of code rather than some elaborate fix :P
So, I have some complicated calculations I am aliasing in my SQL query. My end goal would be to sort by my final calculation from all the other calculations I have performed but I am running in to some issues. I know I can't sort by referencing the alias itself without saying order by (column number). My calculations are usually pretty small (around .001 and up). Whenever I order by the column number I would like to use (select... from... where...order by 73) I get a "Division by zero" error in access. I figured this was an issue with how small the calculations were, so I multiplied by 1000000, then ordered by that column number. Still division by zero...I went ahead and changed all 0 values to 1, still division by zero. There are no zero values in the column itself, it's either 1 or something in the thousands. I can order by other columns, just not the one I want to use. I don't want to have to re-do my entire calculations in my order by clause.
I have made a query with to take the percent difference of two mileages and then if they are greater than 2.5% to put a 1 or if not a 0. I try and put a 1 in criteria in order to only see the ones that are changed by greater than 2.5% and I get the divide by zero error. Any ideas how to fix this problem.
I am running a very simple query that divides profit over turnover to show the margin. I have many queries throughout my database that do this, here is my little formula;
Margin: Round(([profit]/[Turnover])*100,2)
Now, this runs just fine but if I put in a criteria of <10 I get the division by zero error. The reason I am confused is that there are no zeros or error values or even negative values in either of the profit or turnover columns?
Also I have an almost identical query in another database that has a <5 criteria in it and it works a peach.
I have searched but all I am getting is the usual definition of the division by zero error.
How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?The main form and sub form are linked using parent/child linking, and the sub form is in a tab.I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.
In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.
So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.
Here is what I am trying to do. I have a query with 2 fields. "Time In" & "Time Out". What I would like to happen is this. Whenever a character, let's say a "t", is entered into that field I would like the current time to populate that field. Right now we are actually typing in the time. I have the fields set up as DateTime fields currently.
I've just returned to work after kids and started managing a large Access database related to health, back-tracking over many years.
Currently in filling a form we physically enter: Apples 2.2 red apple 2.4 red apple cut 2.45 Oranges 5.6 Cucumbers 8.5
Is it possible to get field 2 to automatically fill with a number code due to the text typed in field 1?
FWIW, I'm confident at more basic Access e.g making follow on default value = Dlast("field""table") type stuff but the more complex stuff I haven't touched since Uni over a decade ago and you will need to be gentle while I blow away the cobwebs
Within my table if Field 1 has an answer of Self (from drop down), then, I would like Fields 6-12 to auto populate; however, if Field 1 does not have an answer of Self, then leave Fields 6-12 blank.
I am not quite sure how to lay this out. I am using Access 2010.
I am currently stuck on set focus property. I have a main from with nested subform. I am trying to move the focus from last field of the subform to another field on the main form.
Now I have a field name [Securedesign] in frmOrderdetails and I want the tab order to navigate to field [CustomerID] in frmAddresses which is a subform to frmCustomers.
I have my Assets form and the primary key is the ChargerID, in this form I have an "Add New Job For This Asset" button, which opens up the Jobs form at a new record.
How do I make it so that the ChargerID field is automatically filled with whatever the previous record was instead of being blank.
For example if I have Charger12345 open in the Asset form, I'd like to click the Add New Job button and it automatically have Charger12345 in the ChargerID field of the Jobs form.
I'm pretty good with setting up a very simple database such as inventory, profiles, etc.. However I'm creating a database to keep track of a football (soccer) team's players and match statistics.What I have so farsample attached)
Tables: * Players - PlayerID, Fname, Lname, position, goals, assists, etc (all details regarding a player) * Position - Positons (Table containing positions eg: defender. Data is selected in player's form as a combo-box) * Competition - Competition types (Cup, League, Friendly. Data is selected in Match's form as a combo-box * Venue - similar to Competition table * Opponent - Similar to above two tables * Match - MatchID, Competition, Venue, etc (form corresponding to table attached)
Forms: * Player form * Match form
Now as shown in the sample, I choose players using the combo-box. Then whatever stats they had during the match are entered on the fields provided. How to link the player (selected using combo box) to the stat fields (goals, assist, YC, etc).
I am trying to do some simple table operations. I have a field (Date) containing dates, and an empty field called Day.
I want to extract the day number from the Date field, and write it to the Day field.
I didn't get very far until I ran into trouble when setting my recordset. I get the error "Too few parameters, expected 1". Clicking "Debug", will highlight the code line "Set rs = db.OpenRecordset(sqlString, dbOpenDynaset)".
So far, my code looks as follows:
Code: Private Sub Command16_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim sqlString As String Dim dataDay As Byte 'Open connection to current Access database Set db = CurrentDb()
[Code]...
I am not very familiar with the various types of recordset settings. I just want to be able to read data from the Date field, and write data to the Day field.
Any easiest solution so that After I Update the field called JobStatus in the subform, it changes the field called JobStatus in the main form to the value which was selected from the subform?.
I am making a new Record in the design view of a table (creating a new field in the table), and I am assigning it a date type, is there a hotkey that will select the field properties sheet so that I can select the format of the field without having to move my mouse?
I hope someone can help me, I have a database compiled from different sources which means that information in fields that need to be linked are written differently, meaning that I can't just simply make a relationship between them.
The two tables I would like to link are, Table 1 has the fields OCCUPATION and AMOUNT and contains over 740,000 records. Table 2 has the fields COMPANY_NAME and TICKER and has 500 records.
I need to find a way for all COMPANY_NAME fields in table 2 to be cross-referenced with the OCCUPATION field, so if COMPANY_NAME is part of the string in the OCCUPATION field then the TICKER (of that company) can be attached to the record in table 1 (specifically to AMOUNT).
The problem is that the OCCUPATION field is not written in a standard form and can include either only the occupation, only the company name, or both in either order.
I can make a seperate query for each company by using as criteria "like "*[COMPANY_NAME]*", but then I would have to do this 500 times!!! Is there a way to automate this?
The final purpose is to link the AMOUNT to TICKER so as to find the sum of all the amount associated with a company.
I really hope you can help, I have little programming knowledge and it will save me the time of making 500 seperate queries. The final use is for my thesis studying private contributions in the american elections.
I need to convert my text data to a number but when I convert using the VALUE function or use "format cells" to the numbers category, I loose the leading zeros. I need to keep them for sorting purposes.
I'm creating a database that keeps a track of questions and scores.
The questions in the database need to be dynamic and are changed frequently.
I have a scorecard table which keeps a record of scores and the applicable question at the time the record was saved. I need to do this because in 6 months time we may want to provide feedback. As the question may have changed we need to be able to refer back to what the question was.
I want the question field in my scorecard table to populate with the value in my question table.
I have tried a number of things including setting the question field in the scorecard table to a lookup based on the following query:
SELECT tblQ1.Q1 FROM tblQ1;
This works however only as a list or combo box. I don't want the user to have to select the question. I want it to auto populate, is this possible?
I know there are numerous threads regarding dsum() on the forum, but I wasn't able to find the exact answer to my problem.
The root of my problem is that I'm trying to update a field on a table using dsum, which references another query to update the table. Although I have all of the correct keys from the physical table joined to the query in the dsum function, the code/ms access seems to ignore the joins. As a result, all payees are having their "vol" field set instead of a select subset.
Query (GetTxnVolAmtTR"):
SELECT p.payee_id, sum(txn_volume) AS vol, t.market, t.period_id FROM ft_payees AS p, ft_txn_summary AS t WHERE p.payee_id=t.payee_id And p.market=t.market GROUP BY t.payee_id, t.period_id, t.market, p.payee_id;
Update statement (references the query above):
UPDATE tmp_ft_component AS rc SET rc.volume = Dsum("vol","GetTxnVolAmtTR","GetTxnVolAmtTR.payee_id= " & [rc.payee_id] And "GetTxnVolAmtTR.market= " & [rc.market] And "GetTxnVolAmtTR.period_id= " & [rc.period_id] & "") WHERE rc.component_name='Total Revenue';
as you can see, I have all of the fields I want joined, but the code seems to ignore this. I've tried looking at this site: http://www.mvps.org/access/general/gen0018.htm , but haven't found my answer. Any help would be much appreciated!
hi, i would like to be able to prevent a user from being able to change a field (lock a field) in a main form if a field in a subform is complete. (the field in the subform is named: "new_weekly_base" if this is complete then i would like the field: "weekly base" to be locked on the main form. is this possible?, please help.
the main form is named: "SCREEN-MAIN" the subform is named: "SCREEN-SUBFORM" (the main form has a button on it which loads the subform.)
ive tried the below code but it doesnt work, any help would be excellent.
------------------------------------------------------- Private Sub Form_BeforeUpdate(Cancel As Integer) If [NEW_WEEKLY_BASE] >= 0 Then
With Me.WEEKLY_BASE .Visible = True .Enabled = False End With End If End Sub ------------------------------------------------
