Report / Query Conundrum
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 Replies
ADVERTISEMENT
Oct 19, 2006
:confused:
Hi
I have done a lot of reading on the site and tried everything i could find that i thought would work, but with no luck.
Here is my problem.
I have a single report that based on a selection of a form will print the report for the selected machine. So what i am wanting to do is have a list box with all the machines in it, the user will then select the machines that they need reports on. I would like to then click a command button and have the report print for each of the selected machines in the list box.
So the report will cycle through the selection and print.
Is this possible?
Best Regards
Stigmata
View 3 Replies
View Related
Dec 8, 2005
Hi people,
I have an existing stock control database that has evolved over the years from the "Inventory Control" sample supplied with A97.
We (until now) supplied spares from one manufacturer. The spares items list was provided to us in an excel sheet and easy to append to a Access table.
We now want to supply spares for two new manufactures who also supply spares list in an excel sheet.
The problem:
All 3 manufactures excel sheets are very different in layout format of number etc. and approximately 30,000 lines in total.
The question(s):
> Do I spend ages messing around copy/paste into one "super excel sheet" then append to access or some how have 3 tables (one per manuf.) suppying to one form?
>Can access handle 30.000 records in one table? Maybe a silly Q. I presume very slow on search?
Any suggestions are very welcome.
View 2 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
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
