Time taken to run queries Results


I have a basic knowledge of Access but am struggling with getting update queries to run. Sometimes they work perfectly and other times I can't seem to the the syntax quite right and don't know enough about SQL to spot the mistakes, can anyone help?

I basically want to update a field on a table, but to ensure the right value is taken from the reference table I need to make sure that two fields on the update table match those on the reference table.

I have tried creating a query in design view but it doesn't seem to work. Therefore I've been playing around with the SQL and have the following, but it only returns values from the update table which I entered manually:

UPDATE tblOperatorsInstallations, Operations
SET Operations.MSReference = [tblOperatorsInstallations]![MSReference]
WHERE (((Operations.Installation)=[tblOperatorsInstallations]![Installation]) AND ((Operations.Operator)=[tblOperatorsInstallations]![Operator]));

Any advice appreciated!


I have been looking around this forum for a long time now and taken a lot of advice regarding building an access database which I really appreciate.

Recently I have been asked to create a new part to my database which is to basically track what a physical site is capeable of, such as wi-fi acccess, seats, tables, a wall for projector etc.

I have no issue storing this data the only issue I have ran in to is attempting to create a form for the query values so that team members can query the database themselves. The form I intend to create will have a subform that updates after each selection.

The way I would like the form to work is, if i select an Opertaions Director Region, this would then show the Regions within the OD Region, this would trickle down to Facility level. So once the region has been selected it would show the facilities within this Region.

I understand that cascading combos would be the best job for that, however the issue i run into is when i would like to also query on the capabilities of the site. So if a user had selected Yes for wifi and Yes for Chairs but left the remaining blank or a * that this would still query without issue.

I hope this makes sense.



Hi All:

This is my first post to this forum because usually I have been one of the many people who search these forums for answers from the outside. Today I have been searching the entire morning and cannot figure out how to normalize a table that I have built to the first form.

I have this database that compiles user submitted financial numbers from 5 different reports for over 400 users. These users often make the same common mistakes when submitting these financial numbers, usually about 40 or so different mistakes. So, my database takes in all the financial numbers from the reports by user(Hire Agency). I then run a query for each of those common errors (Example 1) and if it triggers the error then it puts the error number in an Error Log (Example 2). This functionality is working great.

My problem is that I have essentially made a flat file with the error log table. The error log is what I need to run my reports off of and it is classic example of a table that is not NF1. I need to be able to run reports that will give the user (Hire Agency) with all of their errors listed. I could do that if my error log was normalized and looked something like (Example 3).

The problem is that I cannot figure out how to do this. Everywhere that I have researched on the internet has taken me to Unions, Cross Tab Queries, and example of how to change the information in Excel. The problem is that I will need to run this "Normalization Query" all the time because the financial data is updated frequently...

Sorry if this is a newb question,

Thank you for any help in advance, and thank you all for all the help that I have gleened off this site in the past that you never knew about


My first post here so apologies if this has been covered before.

I'm a long standing Access developer but this is the first time I have had to perform this particular task. Briefly, I have taken ownership of a huge SQL database with circa 1500 tables that uses various Access frontends to pull and push data.

I have a problem which should be straight forward but there is obviously some "idiosyncracy" that I'm missing. We're using 2007 with a SQL 2005 backend, and the job was to 'simply' upgrade the backend to SQL 2008. So far, the SQL database has been created, indexes and stats updated and have confidence in the SQL database. However, the Access front end pulls some data from a DB2 system, locally processes it and then pushes it into SQL. Currently the Prod system in Access / SQL 2005, processes the whole 250 query proceduer in 1hr 45m, now with exactly the same setup but in 2008, takes 6 hours.

The spec of the new test box is much better than the current Prod box, so no concerns there. I've ruled out any network issues, as this box is used for quite a few production databases.

I think it is a communication problem between the way Access is processing data at the Client side. Taking a very simple append as an example from a local table into SQL 2008, which took in Prod (2005) took 20 mins to append 1.2M records, now takes over 2 hours to complete.
I have changed the driver to user Native Client 10.0 rather than SQL Server driver as previously.

I'm not in a position to start moving over the queries to SSIS as there is simply too many, or infact re-develop in an ADP, so I have to work with what I've got, annoyingly.

Any suggestions what may cause the process to run so slowly? Anything to check or suggestions?

I was under the impression that SQL 2008 and 2005 were very similar so wasn't expecting to run into any issues. There is something different about the way that Access must process the data or the connection to SQL that is causing the problem.

Any help appreciated.


Alright, here's what's up...

I'm attempting to create a report for my personal finances that basically re-creates a duplicate of my monthly credit statements for quick comparisons to my real credit statements for aberrations in charges. That means each set of data from each respective month has been grouped from the 15th of last month to the 16th of this month and, basically, all is well in the summing of the world except for my monthly subtotals. Each month you make a payment, say on the 18th for the last month's credit history. Right now, because the payment is happening in the 'current' month, my statement totals are being reduced by the payment from the previous month when I would like them to simply compound.

Let me put this more decisively:

The report has an ascending month grouping with descending dates.

I would like each respective subtotal (found in the monthly footer) to be able to carry the respective balance over from the last month, essentially creating a 'running sum' but better put, a running subtotal.

I've tried simply setting the property for the subtotal to 'running sum' but it only wants to work descending, it seems. Additionally, I attempted a DSum() function on my totals but I couldn't quite figure out how to force the criteria to only accept totals of months equivalent or less to that of the current month.

Any ideas on how to make this happen?

Thanks a bundle,

So, I'm still having a bit of the same problem but I think I've taken steps to rectify it.

What I've done, is by a series of queries, I've obtained my monthly, compounding subtotals with the [Invoice Date] field as my key. Each respective [Invoice Date] has only one entry.

Now, back to my report:

The report has information shown for each record based on Date, then amount, payee, etc. Each record has an [Invoice Date] field that is calculated based upon the actual [date] of the transaction. So, if there are 30 transactions in a period ending 08/27/04, each transaction carries the [Invoice Date] of 08/27/04 and subsequently, in my subtotals query, each [Invoice Date] carries a compounded subtotal.

So, back to the report:

Each set of transactions is grouped by their respective [Invoice Date]. Now knowing that I have been able to calculate my subtotals and assign them each to a respective [Invoice Date] is there a way to display those subtotals in the footer of each [Invoice Date] grouping on the report?

I hope this is clear! Thank you so much everyone who takes their time and energy to help young 'ins like me. I've saved countless hours with your help.

Thanks again!

I have a report which displays data about activity over a period in time which is taken from a number of queries and displayed in unbound fields using Dcount function.

I wanted to be able to use a macro to RunSql to out output the data to another table. I have tried using a button to a macro somethin like

INSERT INTO TblActivity (SalesPerson, CallsMade, MeetingsBooked) VALUES (txtCurrentUser, txtCM, txtMB)

but each time I run the macro it opens msgboxes asking for the data and i cant seem to work out why its not picking up my fields.

Any clues, thanks?

I'm working on a database I didn't create that is linked together like an M C Escher painting.

I've been asked to amend an existing report by adding in an extra totals box, but am having problems with duplicates.

I'll try and explain.

Say I have 3 members of staff: Steve, Bob and Gary, and each man has a number of hours available to them each week that they can use to visit clients (Steve has 20, Bob has 30 and Gary has 40). Each man visits clients and records their visits in a table that is then added to the database. The information recorded includes the StaffID (i.e. Steve, Bob or Gary), the name of the client visited, whether that client placed an order (a simple Y or N) and the time taken for the visit. However, the database contains a field that displays the total hours available to each man for each and every entry.

What this means is that there is a Total Hours field that contains the same information depending on the ID of the staff member, i.e. if Gary's information is being added, the Total Hours column will always contain 40, if it's Steve's information then Total Hours will always contain 20 etc)

If a report is run from this information as it is, would it be possible to create a calculation, in the Design section of the report, to add together the total hours available, without including duplicates? I.e. I simply want 20 + 30 + 40, but if I try to simply Sum that field I get 20 + 20 + 30 + 20 + 40 + 40 etc.

I hope that makes sense. I'm trying to make it as simple as possible for myself by simply adding a calculation to the existing report, rather than having to create extra queries as, as I mentioned above, this isn't my database.


I'm fairly new to VBA in Access. I want to run a Query using :
DoCmd.RunSQL SQL_text
However I'm experiencing problems in generating the SQL_text variable. The problem is related to the SQL code. I read some articles about it and I tried to apply that knowledge. Unfortunately, with no success. Please could you help me figure out what did I do wrong. My SQL_text variable is defined as:

SQL_text = "SELECT [Step9: Renaming Comment columns].[Target Region], [Step9: Renaming Comment columns].[Payroll Region], [Step9: Renaming Comment columns].Business_Unit, [Step9: Renaming Comment columns].[BU RPT], [Step9: Renaming Comment columns].[Sales Org BU], [Step9: Renaming Comment columns].[Sales Specialty Code], [Step9: Renaming Comment columns].[Sales Title], [Step9: Renaming Comment columns].[Employee Name (Last, First)], [Step9: Renaming Comment columns].[Employee ID], [Step9: Renaming Comment columns].[Manager Name (Last, First)], [Step9: Renaming Comment columns].[Manager Employee Number], [Step9: Renaming Comment columns].[Mail sent to Mgr (dd-mm-yy)], " & _
"[Step9: Renaming Comment columns].[Handled By], [Step9: Renaming Comment columns].[Manager Notification Sent], [Step9: Renaming Comment columns].[Manager Notification Sentdate], [Step9: Renaming Comment columns].[Days Outstanding], [Step9: Renaming Comment columns].[Generation Date2] AS [Generation Date], [Step9: Renaming Comment columns].[Letter Issue Date], [Step9: Renaming Comment columns].[Viewed Date], [Step9: Renaming Comment columns].[Letter Passively Approved (dd-mm-yy)], [Step9: Renaming Comment columns].[Accepted Date], [Step9: Renaming Comment columns].[Reject Reason], [Step9: Renaming Comment columns].[Quest CL Status]" & _
", [Step9: Renaming Comment columns].[CEE CL Status], [Step9: Renaming Comment columns].[Measurement Period], [Step9: Renaming Comment columns].[Emp Status Code], [Step9: Renaming Comment columns].[SalesLetter Employee#Active Flag] AS [Sales Letter Employee Active Flag], [Step9: Renaming Comment columns].[Block View By Manager], IIf([Generation Date] Is Null,[Why not Created],'') AS [Why not Generated], IIf([Generation Date] Is Null,[Other Comment for not Created],'') " & _
"AS [Other Comment for not Generated], IIf([Quest CL Status]='Escalated',[What action taken against Escalation],'') AS [What actions taken against Escalation], IIf([CEE CL Status]='Generated' And [Quest CL Status]'Accepted' And [Quest CL Status]'Escalated',[Why not Accepted2],'') AS [Why not Accepted]" & _
"FROM [Step9: Renaming Comment columns] LEFT JOIN [FY11 H1 CEE Comments Table] ON [Step9: Renaming Comment columns].[Employee ID] = [FY11 H1 CEE Comments Table].[Employee ID];"

The code crushes and displays a Run time error 2342 "A RunSQL action requires an argument consisting of an SQL statement


I've managed to get myself confused (not hard to do). I am trying to create a SQL query with criteria taken from the current record in another recordset and open it. I get the error "Run time error '3061' Too few parameters. Expected 2." This occurs on the line

Set rst3 = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

Here's the function

Public Function RemoveOnhandIng()
Dim dbs As Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("SortedShoppingList", dbOpenDynaset)
Set rst2 = dbs.OpenRecordset("Pantry Contents", dbOpenDynaset)
With rst1
End With

Dim listQuantity As String
Dim pantryQuantity As String

Dim mySearch As String
mySearch = "[Ingredient Num]=" & rst1![Ingredient Num]

Do While Not rst1.EOF

listQuantity = rst1!Quantity
If DLookup("[Ingredient Num]", "[Pantry Contents]", "[Ingredient Num] =" & rst1![Ingredient Num]) Then
'pantryQuantity = DLookup("[Quantity]", "[Pantry Contents]", "[Ingredient Num] =" & rst1![Ingredient Num])
With rst2
.FindNext mySearch
End With
pantryQuantity = rst2!Quantity
strSQL = "SELECT ConversionFactor FROM MeasurementConversions WHERE FromUnit = '" & rst1![Unit Num] & "' AND ToUnit = '" & rst2![Unit Num] & "'"

Set rst3 = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

If Not (rst3.EOF) Then
End If

If rst3.RecordCount 0 Then

If (rst1![Unit Num] rst2![Unit Num]) Then
listQuantity = ConvertUnits(rst1![Unit Num], rst2![Unit Num], rst1!Quantity)
End If
End If

End Function


This code is taken from Fornatians post way back in 2003, i am having some problems with it and dont know if it is the original code or me being dumb.

Basically the code runs a maketable query and then uses the results and runs a mail merge. The code runs fine through to the highlighted segment. Where i get a Run time 13 type mismatch.

Public Function CreateLetters()
' Open a letter in Word and insert text - used by menu command.
'You must put Word in the DAO Reference Library.

Dim Dbs As Database
Dim rstTEMPOwnersMerge As Recordset
Dim rstSourceTable As String
Dim appWord As Word.Application
Dim intPages As Integer, StrMessage, I As String

Dim Worddoc As String

'Make declarations of Source and MergeFiles
'Set the temp table created by query as rstSourcetable

rstSourceTable = "TempOwnersMerge"

'Set location of word document

Worddoc = "H:AccessPlan & Directory DBReportsPlanDistLetter.doc"

'Set it so that it doesnt come up with warnings re deleting data and such

DoCmd.SetWarnings False

'Run query which adds records to tempownersmerge (query is addtable one)

DoCmd.OpenQuery ("qryPlanMailMerge"), acViewNormal, acReadOnly

'Turn warnings back on

DoCmd.SetWarnings True

' set db to current

Set Dbs = CurrentDb()

'set rstTEMPOwnersMerge recordset to rstSourceTable (which is in turn set to TempOwnersMerge)

Set rstTEMPOwnersMerge = Dbs.OpenRecordset(rstSourceTable)

' If no records are returned then exit

If rstTEMPOwnersMerge.RecordCount = 0 Then
MsgBox "There are no matching records for your criteria, please try again", 0, "No Records"
Exit Function
End If

'Switch to Microsoft Word so it won't go away when you finish.
On Error Resume Next
AppActivate "Microsoft Word"

'If Word isn't running, start and activate it
If Err Then
Shell "c:Program FilesMicrosoft OfficeOffice" _
& "Winword /Automation", vbMaximizedFocus
AppActivate "Microsoft Word"
End If
On Error GoTo 0

'Get an Application object so you can automate Word.
Set appWord = GetObject(, "Word.Application")

'Open a document based on the memo template, turn off the
'spell check

With appWord
.Documents.Add Worddoc
.ActiveDocument.ShowSpellingErrors = False
End With

MsgBox "The letter is now on screen and is ready for final editing and merging", 0, "Letter Ready..."

Set Dbs = Nothing
Set rstTEMPOwnersMerge = Nothing
Set appWord = Nothing

Set StrMessage = Nothing
End Function

I have yet to test the rest of the code but hopefully its is just the one bit i am stuck with. For further info, my query is called qryPlanMailMerge and the table the query adds to is TempOwnersMerge.


I posted this same post in the forms discussion forum but didn't get much of a responce, so I thought I'd try here as well.

I am trying to set up a report menu similar to the one found in the Northwind database. A difference between my database and Northwind is there is not an option group on the reports menu. This is what I have done;
I have a table called “Product Types” that has a list of “Product Descriptions” and a product ID key field.

On the Reports Menu, which I have called “Reports Menu,” there is an unbound list box called ProductSelect. It has the following row source:

SELECT DISTINCTROW [Product Types].[Product Description] FROM [Product Types] ORDER BY [Product Types].[Product Description];

Below the List Box is a button called “Preview” that has the following code behind it (this code was copied from the Northwind Database and modified slightly to meet my table/field/report names and to do away with the Option group information):

Private Sub Preview_Click()

Dim strWhereProduct As String

strWhereProduct = "Product Description = Forms![Reports Menu]!ProductSelect"

If IsNull(Forms![Reports Menu]!ProductSelect) Then
DoCmd.OpenReport "GenericAddressBook", acViewPreview
DoCmd.OpenReport "GenericAddressBook", acViewPreview, , strWhereProduct
End If

'DoCmd.Close acForm, "Reports Menu"

Exit Sub

Resume Exit_Preview_Click

End Sub

The Sub Report “GenericAddressBook” is based off of a query similar to the one that the report generated by Northwind is. I am encountering the following problems:

The first part works great. If nothing is selected by the list box the resulting report is a complete list of product purchasers. I am encountering the following problems:

If I select a product from the list box and press the button I receive this error:
Run-time error ‘3075’ Syntax error (missing operator) in query expression ‘Product Description = Forms![Reports Menu]!ProductSelect’
If I select DEBUG I am taken to this line in my code:
DoCmd.OpenReport "GenericAddressBook", acViewPreview, , strWhereProduct

After studying this error I thought that perhaps it may be caused by ‘Product Description’ being two words. I placed an underscore between them in the code so that it was ‘Product_Description’ (I made no changes elsewhere to tables or queries).

However, with this change I am then prompted to enter a Product_Description. If I do this I am the report then opens, but the fields say Error rather than having data in them.

If anyone has any idea what I have done wrong I would appreciate suggestions.

Sorry this post is so long winded but I thought I had better include as much information as possible to give you the best idea of my problem.

P.S. I know that a report of this nature can be generated similarly by inserting the prompt [enter product description] in the criteria field of the query but I hope to avoid that as there will be hundreds of products and I though it would be a pain to keep having to refer back to another table before running the query.

Hope someone can help.

I need to be able to use a variable to "name" a table created by running a "Make Table" query. The variable is a field in the both the original source table, the query and the newly created table.

We have mutiple stores who create "batches" of invoices etc. Each batch when closed is exported as an Excel spreadsheet and emailed to our accounts office.

My application creates a new unique "batch number" for each new batch for each store based on a store code taken from a set up table and combining it in a string with Day of Month, Day of Week and todays date/time. Thus multiple batches can be created by a store in the same day but each has a unique number.

I want to use this batch number to "name" an new table I create with a Make Table Query. At the momement I have to nominate a name for the new table in the Query ie. "XXBATCH" where XX is the two letter store code, then email this table as an XXBATCH.XLS file. Thus each emailed file has the same name?? This causes problems at the receiving end with overwrites etc.

If the above cannot be done can anyone suggest an alternative way of renaming either the newly created table with a variable or the export table (perhaps using DOS batch file?)

Appreciate your support. Great site by the way. Very helpful.

I need some advice in designing tables and queries for a credit card application logging system.

The initial table design was so bad that it made it really difficult for me to design the all important queries. So I have decided to start from scratch again and redesign the tables.

I will explain the whole process carefully from start to finish.

Our company acts as an agent for well know credit card companies and banks.
The company employs Sales Reps that are located in busy public areas around the country. Their job is to find prospective customers for credit cards. The Sales Rep will fill out a credit card application on behalf of the customer.

At the end of the day the Sales Rep will have on average about 20 application which are in turn sent to our 'data centre' to be logged and sent on to the banks etc.

Basic Sales rep information is logged:

- Who took the application (Sales rep)
- Where it was taken (London, Manchester etc.)
- Date app was taken by sales rep
- When the app will be dispatched (always the keyed date)

Also, basic information about the applicant themselves (this helps us track complaints):

- Surname
- Date of birth
- SEQ Number (the unique number printed on the application)

If an app is missing mandatory information (postcode, date of birth etc) the Sales rep will not get paid for the app. It is very important that we log this information as the Sales rep will want to know "Why haven't I been paid!!" Currently this information is written down on an A4 summary sheet and sent to the Sales rep at the end of the day.

Here is an example of the summary sheet:

Sales Exec: John Doe
Date apps taken: 01/01/2003
Venue: London

Apps received: 20
Apps rejected: 6

Missing info: 2
Illegible: 1
Other error: 3


1 with Invalid ID type checked
2 with missing postcode
1 with illegible date of birth


My aim is to create this summary sheet automatically by getting the data input user to log if there is any missing info from
the app.

Please take a look at the following screen grab of the application:

The left hand side of the app is what the data input user currently logs. This part works well and has a basic table underneath on which I run queries. I have now added a new section on the right hand side (criteria) to allow the user to log additional information. This works well but for the bad table design underneath and hence why I cant create the queries that will in turn create the summary sheet as seen above.

For now ignore the right side of the application and let me explain how the 'criteria' section works. It's relatively simple.

Firstly you will notice three check boxes: 'Approval criteria not met', 'Invalid ID type checked' & 'Late application'. If any have been checked it should show up on the summary sheet as above.

Next there are 5 dropdown boxes. Why 5 you ask? Well because there are never mre than 5 mistakes per application.
Each dropdown has the exact same list contents which can either be Missing or Illegible (this you would indicate by using the toggle buttons), Below is just some of the dropdown list contents.

Name to appear on card
First name
Current address
Current address postcode
Landline number
Resident type check box
Period of current residence
Previous address
Period of previous residence

So, for example, if a postcode is missing you would choose 'Current address postcode' from the dropdown and then click the 'missing' toggle button. This would then add to the summary sheet as above.

Lastly the Comments box. If the data inputer has anything futher to add about this application they can insert it here.

God, this is the most painfully long post ever. Hope you understand me so far!

Note that if two applications are logged with 'missing postcode' then it should tally in the summary sheet as you can see above. Same goes for all other missing/illegible info. Although I'm sure you've gathered that already.

Right, now, Suggestions Suggestions Suggestions! I really need help on this one as I totally messed it up the last time.
I await you replies.

Thanks for reading

Hi guys!

I have my word template set up such that some data is copied from the open form into the document and some data is taken from a table which is created from a query based on the content of the form (the record number). This works well HOWEVER (there's always a however isn't there ) where two users click on the button at exactly the same time or within a very short space of time the query only runs once and either the database crashes (bad) or the document is created for the second person with the wrong data (very bad).

How can I make it so that only one person can run the query at a time (ideally build in a delay in processing so that the query will run for them when the first person has finished...failing that a message to say please try again later)? They will always be working with a different record.

I'm a bit stuck as to what to search for in the forum for an answer to this so haven't really searched.....sorry

Many thanks

I had made a program using VB6 with Access as database to keep my inventary. I had used the ODBC Server for connection of the program to the databse by the following command through ADODB:
Public Sub connection()
Set conn = New ADODB.connection
conn.Open "dsn=Pearl"
End Sub
Here "Pearl" is the name of the Access File which is my database.
Initially my program was running quite satisfactorily. But suddenly now, it had got extremely slow in making connection with the database. My program has a lot of tables and text boxes to show me the stock or accounts of the customers or values. So whenever I pass a query to display the stock of inventory or my customer's account, it takes unusually very long time to display the contents on the datagrid (I had used DataGrid to display tables in forms). Sometimes, the time taken is so long that the program hangs up. One thing I found was that if I passes a query in which some calculations are to be made and then resulting table was to be displayed in the datagrid, the calculations were done quite fast. What was actually delayed was the writing fo the resulting data in the table in access and then display of the resulting table into the datagrid.
Then I tried making connection with the database using the following commands:
Public Sub connection()
Set conn = New ADODB.connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Documents and SettingsgaganMy DocumentsCopy-PD-14th Mar'08Pearl.mdb"
End Sub
Here the connection with the database, was quite fast and quick, infact like before (when I was not having any problem with ODBC server). All the calculations were done very fast. Infact, when i passes any query, the results are displayed almost instantaneously in the text boxes. But the main problem in this part is that no data/table is being displayed in the datagrid. I think there is some connection proglem between the datagrid and the tables in access, using this type of connection.
Can anyone, please tell me the reason for this or any soultion to this.

I have a table that stores records based on a field in another table/form. Because this field changes multiple times I always want the most current entry in the pop up table/form.

How would I got about making sure when I run queries/reports that only the most current data is taken from this table? Should I add another field to the table that has a date/time and have it =Now? Would this be the best way?

Thank you!!

The Bradford Factor
For those who don't know what the Bradford Factor is, its not another stupid reality tv show, or the title of john le carre 'esque spy novel. I was hoping for the second when my HR manager asked me if I knew what the Bradford Factor is?

Any way I digress... I do that a lot.

The Bradford Factor is a way of scoring the staff and organisations' sickness levels/quantity/ratio/something to do with giving people a bollocking, for taking the Mickey, and trying to scive off work to often.

Any way the calculation goes something like this :-

a. number of periods of absences due to sickness
b. total time taken off sick in the year (number of days)

bradford factor = a*a*b

so basically if a staff member is genuinely sick, and takes a week off work they would get a score that looks like this :-

a = 1 (because they have only taken off 1 working week this year)
b = 5 (because they took 5 working days off)

bradford = 1*1*5 = 5

now if a person takes the same number of days off in a year but does it in 5 different periods (like every couple of Mondays, cause they have a session on a Friday night, can't take their beer and are still feeling the effects of it Monday morning)

a = 5 (cause they call in sick at five different occasions)
b = 5 (because they take off the same number of days)

bradford = 5*5*5 = 125

you get it??

Anyway long story short, I made the HR manager a Staff database a couple of months ago, the kind of staff details type deal like

Table - Staff – containing stuff like :-
ID number, name, role, address, other stuff like that.

Table – Disciplinary – containing stuff about complaints against staff etc :-
Disciplinary ID, Staff ID, Date, Details

Now she wants it to work out the Bradford factor for her.
Because she’s to busy to do it manually!

So I’ve added another table
Absence - It contains the following
absenceID - autonumber to catalogue all the absences
staffID - to link it to the staff member
absenceType - to differentiate between, holidays, sickness or compassionate leave
startDate - the first day of the period of absence
endDate - the last day of the period of absence
length - number of days taken off in that period of absence.

I now want to be able to work out the Bradford factor for each member of staff.

I included length field in absence table to store that part of the calculation.

So I thought I’d do a query that first Counts the number of times a staff ID is in the absence table :-
SELECT Staff.ID, Count(Absence.ID) AS CountOfID, Absence.Type
FROM Staff INNER JOIN Absence ON Staff.ID = Absence.StaffID
WHERE (((Staff.ID)=[?]) AND ((Absence.Type)="sick"));That gives me the ( a ) in my calculation

A second query then to get the ( b )
SELECT Absence.ID, Absence.StaffID, Absence.Type, Sum(Absence.Length) AS SumOfLength
FROM Absence
WHERE (((Absence.StaffID)=[?]) AND ((Absence.Type)="sick"));So those queries get what I need to carry out the calculation.
But I can’t work out how to make access run them, store the values then carry out the calculation And return the Bradford Factor to a field that I have created in the staff table, all in one go.

Any help would be much appreciated.

Do i even need to do it all in one go?

Did I mention that this isn’t for any type of assignment or anything like that I’m not in college, I’m 30, and I work for a charity? So we don’t get the money to get things done professionally

I can visualise what I want to do but I just haven’t got the knowledge to pull it off. It’s a bit beyond my average IT guy skillset.
Thanks loads

Hi there,

I'm trying to copy an Excel formula into Access 2010

my formula (currently not working) looks like this:

PRICE*(Round(Choose(MATCH 0,2,2.5,3),2.5,2.2,2.1),0)-0.01)

The idea being that the PRICE will be multiplied by a matched number, if within a PRICE of 0-2, it will be multiplied by 2.5
THEN rounded up
Then 1p taken off to give you your lovely £3.99 price

I think some of the Excel forumlas dont work in Access possibly? So if anyone can help, this will save me exporting to excel and running a macro in there every time.

Thanks for any suggestions


Sorry i wasn't sure where to put this thread...

I have a table that has rows/records of parts. It contains fields for description, part no., quantity etc. However it also has fields headings of product names that the parts are used in, in these fields there is a number, these numbers show how many of a part is used in that product.

I also have another table which lists every product sold, with a serial no. being the unique key. Here the product names are listed in the rows/records, not in the field headings. I have created a query from this table which shows each product uniquely and the total amount sold.

I have a form which is linked to the parts table, it has a combo box where the part is selected and then shows the details of that part in text boxes on the form; This also includes a box for each product, and how many of the part is used in it. There is also a text box showing the total amount ordered since the part was created.

So basically i need to get the total amount sold of each product multiplied by the the amount the selected part is used in the product, then have that sum taken away from the total amount of ordered of that part.

Part total amount - (amount of product sold X amount of part used in product) = Stock of part

The problem as you may have already guessed is that i can't get the rows from the products table to identify with the columns from the parts table.
My idea was to have a subform which lists each product and the amount sold of each, then do the calculations in code on the form... however i can't get individual text boxes showing the amounts of each product, since they can only be linked to fields. I've tried to select a specific product in the default value property but it doesn't work. All i can get is a combo or list box which when a product is selected it shows the amount sold in a text box.

What should i do?

I run access 97

Thanks for your time and help

Kind Regards,


The problem i'm trying to solve is;

I have a process that takes x number of days at each stage, my query shows how long was taken at each stage and from overall start to finish, with a finish date.

I would like to create two queries or tables of data from the initial query that shows the following;

For each product going through the process I know how long it took overall and the finish date. Can a query or table of data be produced from this that will repeat this record x number of times, where x is the total length of days taken and show a date that corresponds to the finish date but is one day less on each record until the first record represents the date the product started the process? This is so that later I can chart this over the time period, with all products stacked in a bar chart.

The second query or table that I require is similar to the one above but creating new records or a query that shows the product x times for the duration of the particular process taking the last process and rolling the finish date back from there.

Hope that makes sense, i did think about exporting the data to Excel and running a macro to repeat the data into a new sheet with the manipulation of the data being created by a macro, but I know I will hit the row limitation of Excel doing it that way. If I can do this in Access then I can ODBC into Access from Excel with a Pivot Table.

Any help much appreciated

Thanks in advance


Not finding an answer? Try a Google search.