Query/Report Fun
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 Replies
ADVERTISEMENT
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
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
Aug 11, 2006
Hi
I have o form based on a query that I can search in for last name etc.
And then I have a button for opening a report for the person that displays.
Here is the problem now:
If I have two persons with the same last name I get a report of several pages also showing the person that is not displayed in the form but have the same last name.
What can I do in the query just to get the person only on display showing on the form?
ID is the primary key
Mikael
View 1 Replies
View Related
Sep 17, 2006
hello everybody,
Im facing the following problem.
I have several tables with information about one subject, now I want to place all the information from the subject in one report. Now I know I can do this with a query with loads of joins, but when I try this I don't get any result.
Is it possible to fill fields in a report based on a query? Say something like
me.test.text = "Select year from tbltest where city = " me.test.value
I hope my problem description is enough, otherwise I'm more than willing to explain.
Thanks,
Peter
View 4 Replies
View Related
Jan 9, 2007
Hi all
I have a report rptTeamPickStats which source is a query qryTeamPickStats
The query has columns such as name, id number etc which are GROUP BY and hours, cases as SUM and there is a DATE field where I originally had a WHERE statement specifying the from and to dates.
I want to be able to modify the where using VB so I don't have to have several queries.
The code below doesn't work (probably won't take you long to realise that) but just to show kind of what I'm after.
I want to query all the TLName where the date is between to dates.
DoCmd.OpenReport myReport, acViewPreview, "", _
"[TLName]='" & myFilter & "' And qryTeamPickStats!Date >= #" & myFromDate & "# And qryTeamPickStats!Date <# " & myToDate & "# And [OTcode] " & myOp & " 'NA'"
Hope this makes sense
Cheers
Kev.
View 5 Replies
View Related
Feb 26, 2007
Hi,
I'm making a database at work for entering expense claims and then exporting them out for import into the main pay system. I thought I was going well, until I got round to creating the reports.
For info, I'm using Access 2000 on Windows 2000.
I have several tables (of course) but the ones which are needed for my queries are the employees table (tblEmployeeDetails), the claims table (tblClaims) and the elements table (tblClaimElements). They are set out as the following:
tblEmployeeDetails
EmpNumEmpName
1Bob Smith
2Bill Jones
3Tim Stapler
tblClaims
ClaimNum EmpNumBatchTypeKeyedDate
1 1A12/02/2007
2 2A09/02/2007
3 1F11/02/2007
4 3A09/02/2007
5 2D11/02/2007
6 1A21/02/2007
tblElements
ElementNumClaimNumValue01Value02
3110.004.00
4115.000.00
18179.9519.56
1223.250.00
334.490.00
1235.950.00
9325.000.00
21412.004.44
13418.006.00
2527.004.00
12535.000.00
19629.990.00
21610.001.25
564.000.00
The reason I have set it out in the way shown is because I want to avoid any duplicated information. Plus elements need to be added in with ease at some point in the future, hence I set them in a table of their own.
I need to group the information together slightly in that each element (as shown on tblElements) has a value of Ex.VAT and VAT and there are a few ElementNum's which fall under one element. The elements on the final report/query is split by ElementNum as follows:
1 > 3 = element 1
4 > 6 = element 2
7 > 9 = element 3
10 > 12 = element 4
13 > 15 = element 5
16 and over = element 6
Any element with ElementNum of 16 and over falls under the "Other" category as only elements 1 to 5 need to be displayed separately on the reports.
The query result I need is like the following (hope it copies across OK):
Element1Element2Element3Element4Element5Element6Element7
EmpNumClaimNumValue01Value02Value01Value02Value01Value02Value01Value02Value01Value02Value01Value02Value01Value02KeyedDateBatchType
11 10.004.0015.000.0079.9519.5612/02/2007A
134.490.0030.950.0011/02/2007F
164.0039.991.2521/02/2007A
2223.250.0009/02/2007A
2527.004.0035.000.0011/02/2007D
3430.0010.4409/02/2007A
I've tried searching various criteria on the forum over the last week to try and find an answer, but as I'm not sure how you would put my problem into words, I've come up with a blank :(
Was wondering if anyone could point me in the right direction to get the query to put out the information like I need as I'm getting to the point of pulling my hair out (and I don't have much to start with!!)
Thanks in advance,
Jon
View 2 Replies
View Related
Apr 2, 2007
Here is the statement in my query:
ItemType: Left([itemNumber],InStr(1,[itemNumber],"-")-1)
It takes the following string "M4-1234567" and displays everything before the hyphen. Yet when someone doesn't type in a hyphen it gives me an error. This prevents me from generating my report. How can I fix this other than beating the person(s) who didn't type in the hyphen?
View 6 Replies
View Related
Apr 24, 2007
I hope I put this in the right place, but I have a problem.
I made a report from a Query, and in the report footer I have a unbound text box with this expression. =Sum([Estimated Time]) I run the report and this is the error I get.
"The expression is typed incorrectly, or its to complex to be evaluated. Example a numeric expression may contain too many complicated elements."
I also have another unbound text box with the following expression
=Sum([Actual Time])
And it works great. All the data in the Estimated Time field is manually entered just like the actual time. All information is in the same table.
I just don't understand.
Please send help.
Thanks,
Pujo :confused:
View 7 Replies
View Related
Apr 26, 2007
Hello all again! I’ve gotten to the point of making the reports for my database, and I’ve got all the detail ones going, but I need some assistance on the summary report. It seems to be a little over my head. I figured out the coding, but I’d not be able to group it nicely.
Access 2003, and I put the detail into the attachment, as it would be easier to show in Excel. I made three tabs, data format, what I want to accomplish, and how I think it would be done.
Well, since I took the time to re-look at everything, here’s basically what I need.
I can do it in sections (groups) for the divisions and centers, so that’s not an issue.
I can get Month Year for this month =Format$(Now(),"mmmm yyyy",0,0)
What’s the format for last month? How do I subtract one month?
Second, for the info on the bottom, assuming I named the date fields [current], [less one], [less two] etc… How would I setup the query to use the fields? I’d like to include the query right in the field. Would it be
IF ((Year(Now())*12 + Month(Now()) = Year(cboDate)*12 + Month(cboDate)), SUM([Pieces])
I was shown this formula to check two dates are equal for months and years. I’m not 100% sure if this will work for the SUM of the pieces.
For previous months, would I substitute [less one] for Now()?
So, I had planned on making the greatest post ever, now I just hope you aren’t as confused as I am… Sorry.
View 2 Replies
View Related
May 29, 2007
Hi all,
I need some help with a query in a Report. :confused:
I need to group the Total Sum in quantity of a Product. These are based on the:
MarketId
Product Code
Product
Flavour
Size
In the query I am listing all the above fields, thus it is giving me a list of Products that have been sold in all the Markets.
So for example if I need to know how many Chocolate Bars where sold having product code : 222222, it will give me the quantity of how many Chocolate Bars where sold in every Market.
What I need now is to group the number of Chocolate Bars and display it as a Total, irrelevant of which Market they had been sold to.
Any help will be much appreciated thanks a lot.
View 1 Replies
View Related
Sep 12, 2007
Ok, I was on here a couple of weeks ago, and got some answers that gave me the form I needed. :) Now I have another problem. I need a report that, among other information, brings up who a file has been most recently checked out to. I have a query that the report is built upon that gives me all the information I need, except I can't get rid of the duplicates of check outs.
The tables I am working with in this query are as follows:
tblFiles (this is where the file name and description is kept)
tblCheckedOut (this table has the Folder ID, User ID, and Date/Time Checked Out)
The main thing I really need help with is a query that will pull up the most recent Date/Time CO with the Folder ID, and not show the duplicates of a Folder ID. The problem is that none of my lines are true duplicates. The Folder ID can be duplicated, and the User ID can be, but the Date/Time is Uniqe because I use seconds in the time format. So using the SELECT DISTINCT has not been helpful, or using the MAX function. Unless I am just using these wrong.
Any help is much appreciated.
View 7 Replies
View Related
