Report/Query Question. In Over My Head :-(
Dec 20, 2006
Hi all, i am new to the forum and i hope somebody can help me..
Apologises in advance if this post is out of place..but i really do need help!
I have created a database in work that will be used to track training history for employees. It consists of two main tables:
Employees: Name, Start date, Department, Manager
Class: Class name, Class Date, Duration, Attendee1, Attendee2, Attendee3 upto Attendee 24 (Max amount of trainees in a class)
All is working ok, and i can populate the tables fine using forms. I can now view class information, and see who attended what class.
However i need to be able to view an individual employees training history. ie
Joe bloggs has attended the following classes:
Class 1, 12/01/06, 8 hours
Class 2, 14/01/06 8 hours
I think i need to create a form which will allow me to enter an employee name. This name will then be checked against attendee1, attendee2 etc in the class table and it will then flag back any classes that the employee has attended
I hope i have explained this ok, but i really do need some advice on how i can go about doing this..:confused:
I have very quickly realised that there is a wealth of knowledge on this site, so i am hoping that you guys may be able to help..
I have been working on this for a few weeks now, and books dont seem to have the answers!!!
Many thanks in advance
Noel
View Replies
ADVERTISEMENT
Sep 4, 2007
ok guys-
I am struggling here. I've been trying to figure this out, got a small portion of it completed- run the query and I run out of Temp disk space- so it never gives results.
Basically- I have all the property data in one table. The goal is to find the how a property listed for sale compares to all those properties similar to it have sold for in the past x amount of months. I want to compare it to all the other houses in the same area (these are Sold status). The Sold houses have to have the same number of levels (stories), be in the same area (Area) of town, have sold in the last x amount of months (eventually I will compare the houses that have sold int eh past 3, 6, 9, and 12 month time frames), and have a square footage range of +/- 15%.
For example-
There's a house for sale (Active status) for $200,000. It is 2,000 square feet, built in 1995, has 1 level, and is in Area 510. I want to compare it to all the other houses in the same area, similar to it- which means, +/- 15% of the Square Footage (1700-2300), in Areas 510, has only 1 level, and was built in +/- 10 years (1985-2005). I then need to take the average $/sqft of THOSE houses, and compare it to my one active house. Let's say there's 10 houses matching that description, and the Avg $/sf comes out to $240,000 on average for those 10 houses.
So- the final result will be:
123 Main St Active 2,000sf 1995 1 (level) 510 (Area) $200,000 $240,000
And then a calculation of Asking Price divided by the Avg Sold Price of Similar houses which, = 83.3%
All the data/fields are in one table. I have been trying to work nested queries- but, I've only got 2 or 3 specs in, and it runs out of space on my temp disc. So, I believe I'm building it wrong. Can someone please help me out to figure out how to accomplish this? I've got more to add onto it- but- I think if I can get this part figured out, I can do the rest on my own...
Thanks a ton guys!
View 5 Replies
View Related
Mar 7, 2005
Database: Invoices....
need a billing address, and a delivery address.
I have a table of addresses (of both kinds, of course), a table of invoices, and a linking table for the many-to-many, because each invoice has a delivery address, but may have a separate billing address.
in my linking table i have PKs from each table, but i also need to categorize each relationship as either a billing or a delivery address, so i added another number (1 = delivery, 2 = billing).
i've got a main query that populates general invoice Detail from just the main table (data source for main form)
the main form has two subforms, each one being populated by its own query with AddressType set to either 1 or 2, and form/subform relationships on the main table's PK.
i can't get my forms to update the linking table directly.
is this possible?
am i doing something wrong?
am i missing something obvious?
it would be nice to be able to search for the word "many".......
View 2 Replies
View Related
Mar 23, 2006
Hello all,
I need your help! Iam breaking my head for a day almost to solve this problem.
This is a timesheet application in MS Access in which I have a MS Access table with the following columns:
1. PersonID
2. SkillID
3. EntryDate
4. MON
5. TUE
6. WED
7. THU
8. FRI
The work hours for a person is entered once a week, at the end of the week. The person's SkillID can change but not in the same week. For ex a person scheduled as a Welder has to work as Welder for that week.
So, I need to find now all the PersonIDs with more than one SkillID in a week and flag them as errors.
Pls check the attached image. The first entry with EntryDate as "06-May-05" and Person_Code as "MK0259" repeating with 3 different Skill Codes. Then this is a problem which I want to hight light. Hope I explained clearly.
Can someone help please? Desparate!
Thanks in adv.
sgmuser!
View 2 Replies
View Related
Oct 25, 2006
Hi, I think there is a very simple answer to this but it is giving me brain ache!!
I have a form which displays or edits 'projects', this form is bound to the projects table
each project record is assigned a location, that location is selected from a combo box.
When the Location is selected in the combo box it stores the 'Numerical' value to the [location] field in the projects table.
my question is this:
how do i create a query as a base for a report that will list projects by location but give the location name (as it appears in the combo) rather than its numerical value as stored in the table?
I keep getting a 'type mismatch' error
I just cant get my head around it!!
View 5 Replies
View Related
Jan 4, 2007
Hi Folks,
First of all i apologise for posting double, but i m compelled. I badly need help. i m not an expert like you guys. I hope someone will surely come to my rescue.
I have a problem with summing values in a query that is based on 5 tables.
In my form i have a text box which shows the total quantity on hand. The formula comes from all 5 table fields. for example:
GOODS RECEIVED GOODS SENT REPAIR RECEIVED REPAIR SENT ISSUES
Trx_Qty Trx_Qty1 Recd_Qty Sent_qty Issued_Qty
The formula works this way:
Expr1: [trx_qty]+[recd_qty]-[Trx_Qty1]-[Sent_qty]-[Issued_Qty]
When the records are limited to one row only then all works as desired, but as soon as i add more records in REPAIR RECEIVED Table and REPAIR SENT Table i get multiple rows in the form. Also the sum is not correct. For example:
GOODS RECEIVED GOODS SENT REPAIR RECEIVED REPAIR SENT ISSUES
Trx_Qty Trx_Qty1 Recd_Qty Sent_qty Issued_Qty
40 10 5 10 20
In the above example the formula gives following result
Expr1: 40 + 5 – 10 – 10 – 20 = 5
Expr1 = 5 (This is quantity on hand)
Bu now when I add more rows to other tables I get multiple rows and incorrect sum. Example
GOODS RECEIVED GOODS SENT REPAIR RECEIVED REPAIR SENT ISSUES
Trx_Qty Trx_Qty1 Recd_Qty Sent_qty Issued_Qty
40 10 5 10 20
5
I m attaching the database for you to have a look at it and come up with the solution.
Please help me I m badly stuck
I tried using group by and sum functions but no use
[Sumoftrx_qty]+[recd_qty]-[Trx_Qty2]-[Sent_qty]-[Issued_Qty]
Please Help me out of this mess. It will be highly appreciated
i will b gr8ful
Thanx once again for investing ur time in reading
Note: Expr1 formula may be different in database as quoted here on this forum due to trial and run.
Regards
Darno
View 5 Replies
View Related
Sep 8, 2005
Hi
At the moment I am trying to send data ( a query) to a CURRENT Excel file, I do not want it to replace the current file but instead just update the cells of that file to the new values calculated in Access.
Is it possible to do this?
Cheers all
Bikeboardsurf
:confused: :eek: :( :mad: :confused:
View 2 Replies
View Related
Mar 23, 2006
Hi there
I have a listbox and all is well - the only problem is that the column heads are a little 'ugly' - my column names are usually something like 'catagoryName', 'productType' and 'price' instead of looking pretty, like 'Catagory Name' and 'Product Type' and 'Price' respectively.
Is there any way to edit/format the title/column heads without having to change all the tables and queries etc, so they look a littler prettier?
Many thanks
Edd
View 9 Replies
View Related
Dec 8, 2006
Hi, I would like to change the properties of either the column heads in a combo-box or the column names for tables. I don't think there's any way to adjust the column head properties, and I'm doubtful there's a way to change the column caption properties.
Some of my column titles are long, and I'd like to be able to word wrap them essentially. Anyone know if this is possible? Thanks.
View 5 Replies
View Related
Sep 2, 2014
I have a three-column query that tells me how many hours I have available per week for a given resource type (e.g. welders). I have a second three-column query that tells me how many hours of work I have planned per week for a given resource type.I'm hoping to produce a query (the source for a report) that will show resource types in rows and twelve months in 24 columns. the first column for each month will show how many hours I have available for all my resources, the second column for each month will show how many hours I have allocated.
How do I produce a query that will combine the other two queries, inserting zeroes where necessary considering that for any given week I might have allocated work to a resource that isn't available (because the inconsiderate buggers think they are entitled to holiday) or I might have a resource that has no work allocated (because I'm incompetent)?
View 8 Replies
View Related
Oct 18, 2005
Hi...
I have a query that when I run it normally (just click on it) then it runs fine. (It is a union query, getting it's data from 8 other queries (who has their dependancies)
But when I want to run a report from it, Access gives me an error saying "query is too complex".
I am flattered, but I would prefer access to work than say I write stuff that is too complex for it. :cool:
Any ideas?
I am confused by the fact that it runs when I double click the query, but the report bugs it out.
-Reenen
View 1 Replies
View Related
May 28, 2005
Hi all,
Member Table
attribute: Name,Date Join,Email
* the format of my date is 23-Mar-04
How do i exact the 'month' from the date. eg 23-mar-04, the month will be mar.
The poutput of my report should be as follows, therefore i need to exact the month of the date, is there a way in access that can perfrom this. THANLK FOR THE HELP.
Member Report
Month : Mar
Name: Alan
Date Join: 23-mar-04
Name: Tom
Date Join: 26-mar-04
Month: Arp
NAme: Mike
Date Join: 05-Arp-03
Name: Wilson
Date Join: 23-Arp-05
View 1 Replies
View Related
Aug 21, 2006
I have a table that contains transaction info including the date of the trans and the date of the order. Some orders do not have a transaction date yet. I'd like to have a report that shows a total dollar amount on all the orders and also shows the total dollar amount of orders that do have a transaction date.
It seems like I would somehow need to be able to have the report use two different queries, but I don't know how to do this.
Can anyone tell me how to do that?
Thanks.
View 2 Replies
View Related
Jun 16, 2007
I am new to Access and appreciate advise to design a report showing six months of transactions that will rollup the ending balance of January to February's beginning balance and then from February to March, etc. I design a query and a report but not able to proceed any further. Attached is a zip file with the Access file and a spreadsheet of the required report. Thanks in advance for any help.
View 2 Replies
View Related
Sep 3, 2007
I have many queries in my DB and would like to run a query to ascertain if some are unused and so can be deleted. Is there a query that can do this?(Clearly - if there are forms/reports with dependancies on some of these queries this needs to be accounted for).
View 3 Replies
View Related
Nov 3, 2007
I’m trying to write a query for a monthly report.
The table that I’m using has the following fields; CASE_ID, DISTRICT_OFFICE, TOTAL_ARREARS_ON_CASE.
I want the monthly report to tell me how many cases (count) in each DISTRICT_OFFICE have arrears (1) between 100-5000 (2) between 5001-10000 (3) 10001-15000.
Can this be done in one query and can you please show me how to write it?
View 4 Replies
View Related
Aug 5, 2005
How would I got about with this problem. What would the code be for this command button. I have got two combo boxes that contain Month(cbo_Month) and Year(cbo_Month) and located in them are month names and month number e.g. Jan 1 Feb 2 etc and year 2002 02 2003 03 etc in 2 columns. These are on a form that the user selects the month and year they want to show the expiry date of that item and then clicks on a command button that will look at a query with the expiry date column to show the results. For example there is a item that expires and the end of June 03. So the user goes to the form and selects Month Jan and Year 03 from the combo boxes and then clicks on the command button and this will then show a report with this item. I have tried everything with this and have ground to a halt. Hope this can be solved or guided. Have tried dateserial. Also what happens when they want the month Feb when it is a leap year. Any examples would be a help. Cheers
View 4 Replies
View Related
Nov 22, 2004
I have created a dispatch log that records the calls we receive. I need to create a report/query that calculates the number of calls for each call type (IE: Disorderly) and during which shift it occured on. These numbers are later figured into the department statistics.
I can query the duplicates but I cannot figure out how to sum each entry and seperate them by shift.(6a-2p, 2p-10p,10p-6a)
Any help would be appreceated. The access box the IT department has does not explain what I need to do with any clarity.
Dave
View 2 Replies
View Related
Feb 10, 2005
I have about 60 Query completed, each with its own statistical
percentages. Hoy can I make all these Query apear in one report.
Can I make the querys link and create and update a table or query?
Im stumpt?
View 1 Replies
View Related
Apr 6, 2005
I would like a report form to show monthly data from my Db on one form reading from 1 query.
The report form must show activity between each month and display the current month in the header. i.e.: Exercise activity for April 2005
currently, I have 12 querys and 12 report forms that the user looks at each month via a dropdown (CboBox) because I don't know of any other way to do this, I would also like to know how this would work permantly so there is no need to amend the dates each year, could anyone please help?
Regards, Steve
View 2 Replies
View Related
Feb 7, 2006
Hello all,
I have a small db with two tables in it. One table (called Property) holds information that is organized by using a property parcel number (APN) as the primary key. The other table (POI) holds different information related to individual records found in the Property table. Each record in the POI table also includes an APN, which has a relationship to the Property table field of the same name.
My goal is to generate individual reports where the top section shows one record from the Property table, and below that are listed the records found in the POI table that have the same APN. (Similar to the view you see with the subdatasheet when browsing the Property table.)
I created a query that does something close to the subdatasheet view, but it pulls all records from the property table and then displays the subrecords beneath them. I only want one APN Recordset per report, and would like to be able to choose which APN recordset is displayed. Ideally this would be done via a form with a dropdown menu that's source is tied to the APN field in the Property table, and a button that generates the query then creates the report based on this new query, and after printing deletes both the query and the report.
I've done some other Access DBs, but they have been quite simple and the queries I have used were not as advanced as what I'm attempting here. Any help is greatly appreciated.
Thanks in advance!
Xenos
View 1 Replies
View Related
Aug 11, 2007
Dear All
I am new to database and wish to learn access
i have some data from time and attendance system
the details of which are as follows (i am giving dummy data)
date time stno
07132007 085490045
07132007 1300 90045
07132007 095490046
07132007 1900 90046
The data is for a single staffno
i want the data to be shown as follows
date stno time(in,out) timepresent(in hrs,min)
07132007 90045 08:54,13:00;
07132007 90046 09:54,19:00;
is this possible in access
basically i want to generate my own report for data recorded from barcode readers
thanks all for sparing heir valuable time
srikamal
View 4 Replies
View Related
Feb 20, 2006
Hello all:
Every week I download new information into a table. The download has all the information, but the table will spill out any information from the download that is already existing in the table.
How do I print a query or report right after the download, that will provide a list of the new information just downloaded.
Thanks for all your help!
View 2 Replies
View Related
Apr 23, 2007
Can someone help me do this in a better way? I have built the two queries below to give me a montly sum of some church contributions. On the "Reportsfrm" form I have two combo boxes to choose the month. One of the combo boxes is setup to choose the months of the year names. This is used merely to put the name of the month on the Monthly Report. The other combo box chooses a number from 1-12 which is used in the first query below to choose the month for the query. This works fine, but makes the DB user use two combo boxes. Does anyone know of a way that they can just choose the month by name? Thanks in advance for your help.
SELECT MemInfotbl.ContribDate, MemInfotbl.ContribAmt, Month([ContribDate]) AS ContribMonth
FROM MemInfotbl
WHERE (((Month([ContribDate]))=[Forms]![Reportsfrm]![Monthcbo]));
SELECT Sum(Monthlyqry.ContribAmt) AS SumOfContribAmt
FROM Monthlyqry;
View 8 Replies
View Related
Jul 29, 2005
I needed to get a report that is based on year. The default report doesn't group them by year. Right now I have a query that extracts the year from each date. I'm guessing the next step is to count the number of occurences for each year. Then finally take each distinct year and base the graph on the count for each year. While I have an idea on how this may work, I don't know how to implement it.
scratch
View 2 Replies
View Related
Sep 14, 2005
--------------------------------------------------------------------------
I am trying to combine firstname and lastname in a listbox on a report. It seems to work for my forms but I haven't gotten it work yet for a report. Can someone take a look at my WHERE statement to see what I'm doing wrong. The report is based on the LastLogQuery2
I have the following sql statement
SELECT [LastLogQuery2].[FirstName] + ' ' +[LastLogQuery2].[LastName] FROM [LastLogQuery2] WHERE ((([LastLogQuery2].ContactID)=Reports![CallReport]![ContactID]))
View 6 Replies
View Related
