Query Advice Required
Mar 28, 2007
Hi All
Im after some help with a query im building.
I have a table called QuizResults which has 10 Yes/No fields. The table also has a field called Site.
Each site will take a quiz and the problem I am having is with the statistics side of things.
What I need is a query that will work out the percentage correct for each site. All the data is stored in this 1 table. There are no relational fields as they are not required. I have tried a few different ways but each method I use involves me making many many queries to work this out.
To summarise I need 10 percentage correct fields, broken down by site.
Please can anyone help?
Many Thanks
Dazstarr
View Replies
ADVERTISEMENT
Apr 13, 2006
I am about to create a system where I want to use MS Access for data and MS Outlook for email,contact management etc. I have a dilemma (what do I use for storing the contacts)? I know I can use Access and use VBA to send emails etc but I also want the user to be able use Outlook directly to send emails etc as normal using that contacts (Outlook) list.
Using Office XP at the moment btw.
I would appreciate comments as to the best way forward before I start.
Thanks in advance.
View 2 Replies
View Related
Jan 29, 2006
Hi,
I'm currently putting together a database for a medical NGO in Cambodia (http://www.medicorps.com/updates/cambodia.html) and am looking for some advice. The simple database is for logging client referral cases by Cambodian doctors to a team of international doctors. I haven't used access in 10 months and despite programming in access for 5 years progress is very slow. At the moment I'm designing the input and search forms. I was thinking that a more logical approach would be to convert the forms to access data pages and put the database online. I haven't used data access pages but from what i know their fairly limited?
The goal would be the ability to log/search the data with auto updated pull downlist based on the actual data. Ultimately I want the data compiled and emailed to a email list from withing the website. The trouble is I have no idea how to do it.
Thanks in advance for your help.
View 5 Replies
View Related
Jul 1, 2005
I'm looking for advice on the best method to accomplish the following from the esteemed members of this Forum (You all have provided excellent advice in the past to this Access Dummy, with my thanks), (I've also searched the forums without result):
I would like to make several fields "required" fields on my form, easy enough, in that I set the Required property on the table to "Yes".
What I would like to happen on the form is that when a user tabs out of a required field, a message box pops up that says "This is a required field" and/or when they click any of the following command buttons I've created, "Save Record", "New Record" or "Close Form", that a message box pop up and list the required fields that they missed.
Any ideas, with code, macros, or other solutions would be greatly appreciated, keeping in mind that I'm just not that swift to start with.
Many Thanks,
Photoguy
View 9 Replies
View Related
Nov 3, 2014
I have made a form based on related tables. it requires me to fill out every field, which I don't want. I didn't make them required. Why does it do that?
View 3 Replies
View Related
Jun 3, 2007
Hi,
Before I post on here I always try to look back through the posts for similar problems – however I don’t know where to start looking to solve my problem.
I use access to store and record the details of my sales. As well as the prices, I also store the date of the sale.
I want to be able to add up the value of the sales for each day so I can see how much we sold – but I don’t know how to go about it. I’ve tried performing a query and then applying a filter by form – however when I click on the “Date” column, the drop down selection displays a list of identical dates:
12/05/07
12/05/07
12/05/07
12/05/07
12/05/07
12/05/07
12/05/07
12/05/07
11/05/07
11/05/07
rather than just one date:
12/05/07
11/05/07
10/05/07
And when I click on one of the dates it only shows the data for that individual record, rather than the data for all the records on that date.
Could anyone please nudge me in the right direction? Should I be looking to perform a normal query? Or do I need to work on a different type/advanced query?
Eventually my intention is to create a form which links the queries to a report so I can print out the data, however for now I’d be overjoyed to simply be able to display the values in a table.
Any help would be greatly appreciated!
Thank you for your time.
View 5 Replies
View Related
Dec 2, 2007
Hi :DFirst off, i am a complete novice in this area, so be kind:D I’m currently doing a Bsc in Computing, and we have a database module ( my worst subject :( ) the current assignment is to create a DB for a specific task, and one of the objectives is to create a login page for the DB . I tired to use Access's built in 'user and permissions wizard' but had no luck there.I have a staff table, and linked that to a permissions table, I assigned each member of staff a user level (5 levels in all). I then created queries to filter each of the 5 levels, and used parameters in each of the 5 queries to prompt for a username and password. I then created a form for each of the queries. i then tested each of the forms, and it asked for the username and password, and I was in, I thought my idea had worked, however, if I enter I wrong username or password, the form will still appear :( .I’m wondering if there is a way to validate the query parameters? or is there an easier way I can do this? I know i am probably going about this in completely the wrong way. All I need is a main login page, which will offer each user a different GUI.Any advice will be greatly appreciated. As aforementioned, this is not my best area, and this is the first time I have actually used access :(.Thank you in advance :)*EditI have searched th eforums, and cannot seem to fidn anythign on parameter validation, but did find a lot of ways to code a login page. i do not want to do this so i will stick with using access's built in users and permissions. I know that to login you need to click the shortcut that is created, but if i take my DB elsewhere how can i get that login page to be displayed? becuase if you click directly it will nto let you in , u need to click the shortcut, so how could i get around that?? thank you :D
View 1 Replies
View Related
Nov 3, 2007
Good evening all!
Part of the function of my Db is to produce quotations. I specifically need one table containing line items and will use another table (update query) which will contain the 'quoted for' items. Transfer will be based on numbers of users required. I have the basic idea in mind; i will have a form with drop down to select numbers of users and then some Vb to take records from one table to the other. I can do that fine. The bit I am struggling to structure in my mind is as follows:
In a quote there will be typically three line items
1) Software
2) Installation / Configuration
3) User Training
I can easily do as described above but that relies on the main table being pre-populated with all three line items. however, I'd prefer to have the ability to have prices for daily rates, relating to installation and training, in another table so as to be able to change/update them and not have them fixed in a table.
So I think I would want to have the three line items update query across buy somehow control the list price of the services element controlled elsewhere.
Any advice? Many thanks.
View 1 Replies
View Related
Nov 3, 2007
Good evening all!
Part of the function of my Db is to produce quotations. I specifically need one table containing line items and will use another table (update query) which will contain the 'quoted for' items. Transfer will be based on numbers of users required. I have the basic idea in mind; i will have a form with drop down to select numbers of users and then some Vb to take records from one table to the other. I can do that fine. The bit I am struggling to structure in my mind is as follows:
In a quote there will be typically three line items
1) Software
2) Installation / Configuration
3) User Training
I can easily do as described above but that relies on the main table being pre-populated with all three line items. however, I'd prefer to have the ability to have prices for daily rates, relating to installation and training, in another table so as to be able to change/update them and not have them fixed in a table.
So I think I would want to have the three line items update query across buy somehow control the list price of the services element controlled elsewhere.
Any advice? Many thanks.
View 4 Replies
View Related
Jan 15, 2008
I have a list of events and what the out come was on a particular day or date.
To give you some idea, the event is basically number given to a day and the outcome is all possible things that “could happen” and that did happen (so not all 160 possible Outcomes are used all the time).
So it is quite possible to have:
Event 1 (This could be “Monday”)
Outcome 12 (This could be “Rain”)
Therefore as you collect your data over time the “Outcome Diff” would = 12
The problem comes when I try to create a report to automatically calculate the “Outcome Diff” this in a Query as the “counter” that should have been added at the time this was created was not done at the time. I also do not mind if this has to involve creating to a table, as long as I get the “outcome diff” calculated in MS Access, I do not mind how this is done. I also cannot use date diff as event mean more than just an index for the day.
Event, Outcome, Date, Outcome Diff
12, 1, 14-Feb-95, 12 (0 to 12 = 12) i.e this is the 1st event outcome of 1
22, 1, 19-Apr-95, 10 (12 to 22 = 10) i.e this is the 2nd event outcome of 1
29, 4, 07-Jun-95, 29 (0 to 29 = 29) i.e this is the 1st event outcome of 29
34, Ect...
*This is just a representation with test data.
Just for further clarification: the reason this is need this is to find out difference in "event" days as opposed to just an average.
View 1 Replies
View Related
Feb 4, 2008
Hi all,
I have a table of employee sickness / absence records with the following structure and data:
http://www.geocities.com/cyngorsir_ynys_mon/TBL_SICKNESS.gif
I have figure out how to write a query which displays the number of days taken for each sickness (END_DATE minus START_DATE).
http://www.geocities.com/cyngorsir_ynys_mon/QRY_DAYS.gif
My problem is that I would to write a query to display the number of days taken for each of the 12 months of the year.
In the example of Employee 4 their sickness spans two different months, I can't get my head around how to produce the correct result which should look like:-
http://www.geocities.com/cyngorsir_ynys_mon/OUTPUT.gif
The only way I can think of doing it is by recording each day of sick individually rather than just the start and end days?
ANY input / comments / observations would be greatfully received!
Thanks
View 3 Replies
View Related
Jul 7, 2006
Hi,
I have some basic VBA and ADO recordset exposure, and would like to create a form, that:
- allows a user to search 4-5 fields (client & business name based information)
- return options of records to view that match that data
- allow user to make a selection, which then provides another form with full details of that client's record(about 20 fields)
Can anyone provide an example or perhaps guidance on a good tutorial, that will take me through this process gently (and help me understand the process)!
Regards and thanks
Mack
View 1 Replies
View Related
Sep 13, 2007
I need to see the records of a specific date very often. I have designed a simple query (Field : My date field; Criteria : Enter the Date) and it is giving info I want. However, sometimes it shows no records making me wonder where all the data had vanished. On checking up further, I discover that the date I specified was a Sunday, a holiday, when no data entry takes place. What I now want is that if the query returns no records, it should also give a message that "the date you specified could be a holiday. please check" so that I would not panic. Is it possible?
Alternatively, the query can look up the day of the specified date and return the message. I shall be grateful for help for any of these query designs.
View 2 Replies
View Related
Sep 12, 2005
Hi All,
I am relatively new to Access having created my first database a few months ago. I have hit a problem and I am not sure if I can solve this with queries or I need to move to Visual Basic.
I want to create a table containing the stock a product for each day for the next sixty days. I have the current stock in one table, a monthly forecast of sales in another and purchase orders in another.
I want to use the monthly forecast divided by how many days in that month and move forward day by day allowing for purchases coming in.
The tables (simplyfied) are :-
Stock Table.
eg.
Code Stock Qty Date
101 10 11/9/05
102 200 11/9/05
Forecast Table.
eg.
Code Month Fcast Qty
101 P09 30
102 P09 90
Purchase Order Table.
eg.
Code Due_Date PO Qty
101 13/9/05 100
The table that I would like to create would contain Code, Date and Stock.
eg.
Code Date Stock
101 12/09/05 9
101 13/09/05 108
101 14/09/05 107 ...........
102 12/9/05 197 ...........
Any ideas/direction would be welcome.
Carl
View 4 Replies
View Related
Dec 10, 2006
Hi,
I have my Query looking at multiple colums on a single table.
The table has approximately 2000 records.
I would like to create a Query that searches for criteria in one colum only. I have created a Query and providing i search for exactly what is written then it shows results, e.g. when i seach for Horse Furniture then i get many results but if i search for Horse i get no results?
I would like the search to be dynamic, so when the end user runs the query they are presented with a dialog box asking what they wish to search for (this i have no problems with) however i need to know how to get my query to be dynamic and provide results based upon single information.
is this possible and if so then how?
Thank you in advance for your help, i have been banging my head for a while on this one and am getting no where.
Bev:mad:
View 2 Replies
View Related
May 17, 2007
Hi
I am struggling with something im sure is quite simple.
I have two tables - the first has employee name and each record represents 1 telephone call answered. The second table also has the employee name but this table has a field with calls answered.
What I need to do is crosscheck the calls answered by employee in both tables. For example:
Table 1 has 3 records for John. (This means 3 calls answered).
Table 2 has 1 record for John with a calls answered field which is populated with the number 4. (This means 4 calls answered).
What I need is a report with both these sets of data and a new field with shows the the difference between the two.
Can someone please help me.
Many Thanks
View 4 Replies
View Related
Jan 29, 2008
I have a table set up for training with the following fields. Date, Employee Name, Class Name.
I have entered all the current data. I want the database to tell me who doesnt have a class completed.
I also would like a code to put in my query that will show recurrent date, although there is different recurrent dates dependent on the class.
Any help would be appreciated!
Thanks in advance.
View 4 Replies
View Related
Jan 11, 2008
Hi everyone.
What I currently have is 2 columns in one table 1 is required and 1 isnt.
However what i need to do is work out the sum value of the greater amount from each line.
IE
A B
1 2
1 0
2 1
which would give a total of 5 as its adding B1+A2+A3
I tried creating a 3rd columb and entered this IF Formula but apparently access doesnt support If formulas
=If([claims]![Mileage1 IR]>[claims]![Mileage1],[claims]![Mileage1 IR],[claims]![Mileage1])
my plan was to get the "IF column to show me the higher amounts which could then be totalled up.
Any ideas where I may be going wrong?
Kev
View 12 Replies
View Related
Apr 27, 2007
I have been asked to create a database in Access 2000 that will hold 1.6 million postcodes. There will be four fields within the a table one holding the postcodes, and three fields holding information as to whether or not the post code is classed as good, neutral or bad. The requirements are to allow a user to run a query that asks thenm for the required postcode and then displays the relevant information (good,bad,neutral).
There requirement is that the search is done as fast as possible returning the required results. Has anybody got any ideas as to the best way of doing this.
View 2 Replies
View Related
May 11, 2007
My database is built using Access 97.
I have the following table and 2 queries:
Table: “Materials Master Sheet”
field: “Material”
field: “Re-Order Level”
field: “Re-Order Quantity”
Query (crosstab): “Most Recent Count Numbers”
field: “Material Name” (joined to table’s “Material”)
field: “Count Quantity”
Query (crosstab): “Orders Pending Delivery”
field: “Material” (joined to table’s “Material”)
field: “Total Orders”
I am attempting to create a query called "Count vs ReOrder" with the following fields:
1. “Material Name” - comes from “Most Recent Count Numbers” query
2. “Order” - if the inventory count has reached the reorder level point, then 1, else 0; criteria: 1
expression: IIf([Most Recent Count Numbers]![Count Quantity]<=[Materials Master Sheet]![Re-Order Level],1,0)
3. "Suggested Order" - reorder quantity - count of any orders pending delivery; criteria: > 0
expression: [Materials Master Sheet]![Re-Order Quantity]-[Orders Pending Delivery]![Total Order]
This works great if all materials have orders pending delivery. However, if there are no orders pending (material not displayed in crosstab query), the material is not displayed (even though, in reality, the suggested order should be >0)
Currently does this:
material 1 - count = 1, re-order level = 2, re-order quantity = 8, orders pending = 4: shows material and suggests ordering 4 (correct)
material 2 - count = 2, re-order level = 2, re-order quantity = 4, orders pending = 4: material not shown (correct, as suggested order would be 0)
material 3 - count = 2, re-order level = 2, re-order quantity = 4, orders pending = 0: material not shown (incorrect! should be shows material and suggests ordering 4)
How can I go about addressing the missing “Orders Pending Delivery” numbers?
View 2 Replies
View Related
Nov 7, 2014
I've been trying unsuccessfully for weeks to try and query a usable data set that would later be used for calculated values in a report.
I'm building a Hospital Acquired Infections Database for my facility to record data and process reports automatically. Previously, I built the system in Excel using VBA and userforms, but the size of the file has become too cumbersome, and now takes too long to process my reports.
So here's the issue.
UNIT_DATA TABLE:
UNIT
INF_MONTH
INF_YEAR
PT_DAYS
CVC_DAYS
IUC_DAYS
VENT_DAYS
APV_DAYS
PT_LEVEL TABLE (There are many fields in this table for recording purposes but I will only list the ones I need for reporting purposes):
UNIT
INF_STE_MAJ
SPECIF_SITE
INF_MONTH
INF_YEAR
So the important numbers I can't seem to collect are the total number infections per month from the PT_LEVEL Table for only select SPECIF_SITE infections.
the Tables are joined Left to Right as Follows:
UNIT_DATA.UNIT - PT_LEVEL.UNIT
UNIT_DATA.INF_MONTH - PT_LEVEL.INF_MONTH
UNIT_DATA.INF_YEAR - PT_LEVEL.INF_YEAR
The UNIT_DATA Table is a monthly collection for the 5 metrics (PT_DAYS, CVC_DAYS, IUC_DAYS, VENT_DAYS, APV_DAYS) regardless of whether or not the number for each is zero. Every unit in the database will have a record for each month and year.
The PT_LEVEL Table will only have records if and when a patient develops an infection. This means there is no cumulative monthly data for the PT_LEVEL Table Data based on SPECIF_SITE infection type.
I need to first do a roll up count for every month and year in the database, for which I was initially trying to use the UNIT_DATA table for since it contains every month and year. The problem is when I try to query the SPECIF_SITE from PT_LEVEL, I can get the number of Infections for months where infections where present for each unit and null values, for each month, and each year in UNIT_DATA, but when I include a where condition to narrow the view to only selected SPECIF_SITE's it only shows data for that SPECIF_SITE for months where they occured.
I need a cummulative monthly aggregation of the totals for a selection of SPECIF_SITE infections, for every unit, for every month, and every year, since July 2010.
View 14 Replies
View Related
Dec 10, 2007
Hi
Can anyone suggest a method for doing what the title asks. I basically have a single table with several fields. One of the fields is the length of music tracks in seconds. What i want to do is to set criteria so that when a query is run the records to not add up to more than 900 seconds.
1stly) Is this "do-able" using queries or do i need to start implementing sql statements which i have limited experience of?
2ndly) Can anyone recommend a suitable method to do so provided it isn't very complex.
My knowledge of Access is Intermediate.
Kind regards
View 14 Replies
View Related
Jul 10, 2013
I am trying to create an update query. I am trying to update a field in a table with the current date as a request.
I have a table named tblTest and a field named Date2 that I am trying to update with the current date, the button that the VBA is applied to is in a form name frmTest. This is my code:
Private Sub Command39_Click()
Dim t1 As Date
t1 = Date
db.Execute("update tblTest set tblTest.Date2") = t1
End Sub
But when I press the button I get:
Run time error '424'
Object Required
It highlights the 4th of code....
View 8 Replies
View Related
May 12, 2005
Im in the process of building a database for a friends business, and im a bit of a newbie with access.
Id like to get some opinions on structure and overall how i should build the Database.
My goal is to have two types of clients ... donors and buyers.
A client can be both a donor, a buyer or both.
When a client is a donor, they get a certain amount of credits added to their account.
When a client is a buyer, they will be purchasing those credits from a donor.
heres an example of what i want to accomplish;
John smith donates 500 credits; I enter John Smiths info and credits into his profile;
Jim Doe buys 100 of John Smiths credits; I want the DB to automatically update Mr. Smiths Credits, and then add 100 credits to Jim Does profile.
Also, I want John Smith to be able to purchase credits from Jane Johnson, and again, have the credits added to John Smith and deducted from Jane Johnson automatically.
Get my meaning here?
The tables will also contain the typical client info ...ie; Name, Address, Phone, SSN etc...
Can i/Should i do a seperate table just for credits and link it to the client tables? Should i create seperate tables for Buyers and Donors?
Also, I have an excel spreadsheet with formulas to do credits already, but when i tried to import it into a table in access, it didnt work so well.
Any opinions on table structure, design etc would be greatly appreciated
Thanks all for lookin in
View 3 Replies
View Related
Jun 4, 2005
Hello to all,
i have a non-windows application and i would like to create a vb program to print invoices.
I would like to send to this program a txt file with all the values (qty, vat, customer name etc with vertical & horizontal positions in the form etc..) and then superpose all i need to print with an image (gif or jpg wich is the my customer invoice presentation.
In fact i have 2 layers , one with all the value i print and another with the invoice image background.
I'm a beginer with VB, so i need advices to create this program, maybe someone did this already.
Thx in advance
VINCENT
View 2 Replies
View Related
Sep 3, 2005
Hi all,
Im fairly new to access and im having trouble constructing a stock control system that can create sales orders and adjust stock levels accordingly, hold customer details linked to sales orders. Ive spent about 20 hours trying to do this and its just pickled my brain, ive searched everywhere but sometimes im uncertain what exactly it is im looking for. Can anyone give me some pointers?
I have 7 tables at the mo but its 4 of the tables i need for the sales order:
tblcustomerdetails
customerID,first name, last name (general customer details)
tblorder
orderID, customerID, delilvery address fields..., subtotal, total, delivery
tblorderdetails
orderID, productID, productname, description, listprice, quantity, discount, linetotal
tblproducts
productID, catagoryname, productname, description, costprice, listprice, profitmargin, suppliername, instock, reorderlevel, quantityperunit.
what im trying to do at the minute is contruct a subform for a form that would require entering the products into through a combo box selected by productname and then autofill the product description and listprice. Ive ended up deleting all my forms and queries because nothing seemed to work right. I will then add this sub form to a form containing all the customer information and the total price for the subform this then needs to be output to a report for printing, but i can figure that out later. Ive attached my database if anyone wants a look if you dont understand my jibberish.
thanks
View 6 Replies
View Related
