i have a db consist of order table of three basics no 1 is battery no 2 is parts and no 3 is i have made a query transaction based on that.
Now what i need is to get total monthly sale amount of above three category .. then same as for daily and also how can i show all these reports in one summary report.
i intially, I wish to have attendance stats for 3 groups of people over 2 days in a report
i have the two queries for the raw data, but wish to combine into a report that will ultimately become a "dashboard" report for half a dozen queries., and can only get one or the other to work do i need to combine the queries into a summary query of sorts?
Hi All,
I have a approximately 70 queries in my database. I would like to be able to run a query which would run all of the queries and output the number of records for each query. Ideally, these would then be written to a table so that the user could then just read the values from the table for the latest results, rathe r than have to execute the whole thing again.
The user may wish to select which queries to run. I was thinking that I would need a table as follows called say tblQueryResults:
QueryToRun - Yes/No - DateRun - NumberOfRecords
Query1 - Yes - -
Query2 - Yes - -
...
...
...
Query70 - Yes - -
So my first dilema is to work out how to run all the chosen queries that the user wishes to run. The user will probably have all 70 ticked as Yes initially.
Should I run this from VB code with a whole lot of VB statements. I would like to loop through the whole table and collect a list of all the queries to run based on a positive Yes for some or all of the queries. The results must then go and be written into the same table under the date it was run and the number of records that was found for each query.
The whole reason for doing this is that queries which return no records need not be run by the user - saving the user time etc. I appreciate that this query will take a considerable amount of time - given that it could be as many as 70 being run one after another.
Thanks,
Evan
I have three tables with data.
Table1 is data for meals.
Table2 is data for room costs.
Table3 is data for payments made.
Each of these tables has a foreign key for EventID.I'm trying to produce a report that will show, for each EventID:
The total billed (which is meals + rooms)
The total paid (from Table3)
The balance due (the difference from the two above).
Do I have to create summary queries for each table?
Is there an efficient way to return a -1/0 instead of some number/0?
A table has 4 outer joins to views that return a value or null depending on if that field represents a Federal lease.
These 4 outer joins work.
The result is a ID_Wells column and 4 columns with any combination of values or Null.
This line of TSQL:
ISNULL(ISNULL(dbo.vRE_FedLeaseType.Fed_Lease_Type, '')
+ ISNULL(dbo.vRE_FedLeaseMinOwn.MinOwn, '') + ISNULL(dbo.vRE_FedLeaseSurfOwn.SurfOwn, '') + ISNULL(dbo.vRE_FedDirHzLease.Expr1, ''), '') AS HasFedLease
This creates another final summary column [HasFedLease] that returns 0 or some number.
For the Rule Engine - 0 is False, any other number is True.
Access links to this SQL View. Is there a quick way with out a UDF to convert numbers > 0 to a -1?
Code:
SELECT dbo.Wells.ID_Wells, dbo.vRE_FedLeaseType.Fed_Lease_Type, dbo.vRE_FedLeaseMinOwn.MinOwn, dbo.vRE_FedLeaseSurfOwn.SurfOwn,
dbo.vRE_FedDirHzLease.Expr1 AS CountOffedLeaseDirHz, ISNULL(ISNULL(dbo.vRE_FedLeaseType.Fed_Lease_Type, '')
[Code] ......
I need to add a column to a crosstab query that counts all of the occurrences of "P" in a particular row in the crosstab query. Basically "P" stands for "Present" and I need to know how many days each employee was present for his/her shift. The SQL for the crosstab query is as follows:
PARAMETERS [Forms]![frmDashboardReports]![txtStartDateAndTime] DateTime, [Forms]![frmDashboardReports]![txtEndDateAndTime] DateTime;
TRANSFORM First(tblAttendance.AttendanceCode) AS FirstOfAttendanceCode
SELECT tblAttendance.EmployeeName
FROM tblAttendance
WHERE (((tblAttendance.AttendanceDate)>=[Forms]![frmDashboardReports]![txtStartDateAndTime] And (tblAttendance.AttendanceDate)<=[Forms]![frmDashboardReports]![txtEndDateAndTime]))
GROUP BY tblAttendance.EmployeeName
PIVOT Format([AttendanceDate],"Short Date");
This returns an "AttendanceCode" against each employee against each day in the specified time period. I just need to be able to "sum" those codes in a column.
I am basically looking for a way to display the total number of records for a selected number of tables and queries in my database on one screen or on one single page view. The problem with using reports or forms is that they tend to bind you to a single table as a record source. This summary would require multiple tables and queries. The summary data would look something like this with a total coming from a Count() or count type function:
# of records: 3000
# of records to insert: 500
# of records to delete: 400
# of records to update: 2100
# of records changing location: 100
and so on. One from each table or query.
I have a query run that gives me a list of records that I view on a continuos form. What I want is to press a button and run a macro/Append Query to add a Single Summary record to another table.
For example my query spits out this data
Part # Quantity Serial Number
GO2 1 123
GO2 2 456
GO2 2 789
What I'm looking to get is
Part Number Total Quantity Serial Number 1 Serial Number 2 ..
GO2 5 123 456
I'm stuck on a couple of things.
1. Getting a new single row to append.
2. Getting Serial Numbers from several records to save on to a single record.
I am currently developing a database to provide a friend with an auction tracking and ordering system.
I will have a number of questions the first is related to the Home Page/Dashboard/Summary form I am creating. I basically have a few sections, one of which is a combo box offering the user to select an auction to view in the summary section. This summary section contains the list of lots but I also hope to to expand on this and create multiple text boxes, each containing the answer to a number of queries (totals etc).
I have written all the queries and can see the results however as the form does not have a record source I would like to know how to make each text box populate with the result of different queries (ideally in vb - I am using ms access 2010)?
I have tried a few things, control source doesn't apply as I have no record source (i guess). I investigated Dlookup/Dcount but am unsure if these apply for the same reason. I understand I am likely to need a recordset etc in vb and have already tried a few things but unfortunately none of them work.
I have a very simple single-table database with 23 fields. Some of the records have only two or three fields populated. I would like to be able to print a summary of only the populated fields in each record.
It would Ideally look something like:
Record 1 Name
Field 1 Title: Field 1 content - Field 2 Title: Field 2 Content - Field 5 Title: Field 5 content
Field 10 Title: Field 10 Content - Field 11 Title: Field 11 content - Field 12 Title: Field 12 Content
Field 21 Title: Field 21 content - Field 22 Title: Field 22 Content
Record 2 Name
Field 1 Title: Field 1 content - Field 2 Title: Field 2 Content - Field 5 Title: Field 5 content
Field 10 Title: Field 10 Content
Record 3 Name
Field 11 Title: Field 11 content - Field 12 Title: Field 12 Content - Field 21 Title: Field 21 content
Field 22 Title: Field 22 Content
I have a report that uses a subreport for summary/totals information. It was working perfectly until I made a change to the format. Now, the name of the subreport (e.g. subrBalances) is printing at the top of the section. It was not doing this before. Why is this happening and how do I get rid of it?
View 5 Replies View RelatedI have a report that is a summary of multiple records in it. What I have next to the record on the report is a button.I have report that are labeled:
rptIncident Summary
rptIncident Report
and a button labeled
"open report"
I would like to click on the button and then it open the rptIncident Report with all the information, not just the summary. I will put a picture so you know what I am trying to accomplish.Is this a simple Onclick event with a where condition or what? Or does this go beyond to having something to do with VBA?
I have a database of high-school football players, and I am looking to print out single page reports (or forms) that will show detail from several tables and queries. This will act as their resume when they visit schools on recruiting visits. The reason for needing query items, is that I have developed queries that return the most up to date height, weight, 40 time etc., and that single most up to date number is what should print, not the entire table. When I try to build a report it will let me bring in multiple tables, but not queries.
View 2 Replies View RelatedIt's been a long time since I have worked with Access. Here is what I did and what I am trying to do. I'm basically at wits end.
I created a table which consists of fields that contain data pertaining to project management. The Engineering manager wants to be able to do reports on the different projects based on what steps of the project has been finished and who it was assigned to.
Here is an example.
Step 2
Heat Transfer complete? Yes/No drop down box
Step Assigned to: Employee drop down box
Date Completed? Date/time field
Commments: A field for text that pertains to the step if there was a promlem etc etc.
As it stands right now there is a total of 12 steps with each step ranging from 1 to 6 questions like the above example.
For the first report he wants me to create, I need to the following data (which I have the query pulling from my table) Project #, Customer, Engine Make, Engine Model, and the first step where any task is marked at No, and the person assigned to the task that is marked no, the step comments, and he wants is sorted by step.
I have all the data fields from the table included in the query as the way I understand it, the query needs to run through each record, and stop when it finds the first no answer for a step and report it for each record. My problem is, I have no idea on how to accomplish this. Anyone have any ideas?
QUESTION .
First I will enter info into TBL Radio Receipt, from there I want the information to jump to TBL Radio Info and prompt me to complete this table. Then it will jump to either TBL New Radio Information OR TBL Repair Information. This will need to be completed. How does this happen, I mean-- How do I create the relationships to reflect this path?
2. How do I attach a pics of my problem to this thread?
Hi,
How can I separate my application so the users can still use queries and reports on a db? I don't want them touching the tables or application directly so it looks like I need a way to create a separate application or something along those lines that is linked to the tables that the users want to query. Any suggestions?
Thanks,
scratch
I have already used the option in the 'Startup...' menu so that the user only sees the database window with the tables, queries and reports etc. But the user can still press the 'Design button' in the database window. And especially for my queries and reports that would be a problem if they altered things in there by accident.
Is there a way to secure this? Can I hide the queries but still let the user access one table and the reports?
I have tried to search for answers and the runtime command removes everything so that nothing is visible :p
Hope You can help :)
I am trying to create a new query and a new report in my database. The program will not let me. The "new" is grayed out. Can anyone help me? :confused:
View 14 Replies View RelatedI've got several access databases located on our file server. Numerous users throughout the office have access to open and view them as has been the case for several years. Since the beginning of the year we have started seeing a brand new issue occur every couple of months in a few of our databases. One day everything will work fine, come in the next day and all of the macros, tables and most of the queries are gone. I have noticed when this happens the queries vanish in ascending alphabetical order, the first 2-10 or so may still be there but everything after that and in reports and macros are gone. Recently we've also had another access mdb start doing this problem and only one user other than the system administrator has access to that file, it is also located on the server and in a secured folder.
The only fix I've found is to restore from a tape backup from a few days before to confirm it's a good copy but this gets annoying.
Any thoughts as to the cause or solution?
All databases effected are located on a central server, get a nightly backup, have a list of tables almost completely made up of linked tables from multiple ms sql server 2000 databases. The files are Access2000 format.
Thank you in advance for your expertise, I'll add any furhter details you may want/need.
I am updating an existing distributed access database.
I have made changes to an existing report (from my workstation - I have admin rights on the DB). The changes show up on the database. However when a client generates a report, the changes do not show up. What can I do??
I was recently doing a little Spring Cleaning in my database and deleting old tables and queries that were no longer used or had been for experimental purposes. Unfortunately, I unknowingly deleted a query that was being used by a report I still needed and it took me several hours to find and correct the problem.
Not wanting to repeat this mistake, I began searching for a way to display my reports, queries, and tables and all the objects each is linked to. I thought it would be nice, for instance, to have a hard copy list of all my queries that showed where they are derived from and what other objects reference them. Is there a way to do this?
I've tried using the "Relationships" button and working with the Documenter, but I haven't found what I'm looking for. I have also tried a Google search, but, admittedly, I may not be using the proper terminology to get good results.
Thanks for your time.
Hi all,
I'm trying to create a database to track all the students details, I've created the data base with all the tables, relationship etc. i tested it with dummy data too and it worked fine.
I then imported the proper data from a .csv file, using the "File-Get External Data-Import" method rather than a macro, it imports the data with a couple of errors but the data still got through, i had previously created queries and reports and they worked fine with the dummy data but will not show any of the new imported data, its like it hasn't got a link or something but if i create a linked form, it will pick up the data, meaning the relationships are working. hence my problem
Thanks
Dellero
I've inherited a database with many queries (most based on a single table). There is a requirement to change the name of a few fields in the table. Is there a way to find ALL queries/reports that are utilizing the fields that need to be changed so I know what queries need to be modified?
View 3 Replies View RelatedHello,
Am posting this message (also on reports forum) as i dont know whether the problem is at report or query level. Hope you can help.
Hello all, this is probably simple but im new to all this!
I am trying to produce reports that show details for only the past year, at present my reports are bringing up details of all the previous years also. Is there any way you can put conditions on reports ( or should it be in a query) that tells the report to only include dates i.e. today - 365 days. At present i have to change the dates every day both a start and end date. Is this possible?
Thanks
I KNOW I'm making this harder than it needs to be.... I have a union query that pulls from two sales tables. I'm setting up a report where a person can select a manager name, start date, and end date for those union'd sales. I can do all this no problem in the query, but I want to be able to have this information selected on a form, instead of through popups, to bring up the report. How do I link the form information to the query? I can't get the report to show information it "read" from the form. Does that make sense?
Please help!
HI folks
I have a db. I have created a search form which when you click the search button executes a query to find the required records.
The query is currently outputting to printpreview of the query.
What i want to be able to do is have the option to select the correct record and then print it. The search can either be exact and return 1 record or use a wildcard and return a few records.
I have the report setup and working I just can't for the life of me figure out how to link everything.
Maybe I've missed something I don't know.