My database records order details for analysis. I have created a form to monitor individual trends in orders placed by my customers. As we supply bespoke items each customer has a list of products which they alone purchase, i.e we do not supply stock items, each item is specifically for one customer (they can then buy the items on a regular basis). The products are assigned to a customer in the products table.
The Analysis form I have created displays the customers name and two graphs. The first graph shows total sales each month, this graph updates as I navigate through the records (i.e. a different graph is loaded for each customer). The form also contains another graph, this is where the problem lies! This graph shows orders of the individual products, controlled by a drop down list containing all the products we supply. I need to limit this list to only show those products relevant to the current customer select on the form. (The products are assigned to a customer in my products table). Currently I achieve this by using a query which when the form is opened asks me to enter a customer ID, the drop down list is then limited to the products supplied to that customer.
What I would like is for the drop down list to update as I navigate through the records. i.e. when customer A is displayed show all the products assigned to customer A, when customer B is displayed show all the products assigned to customer B.
I’ve attached a snapshot of the form to help explain what I mean.
I have a database where I record grades for my students. I would like to create a macro so that every time I record a grade as an "F", the student will receive an email that will contain an attachment of their scores. A lot of the information I want to include in this email is in several different tables. So to make it easier, I have created a query that lists the following:
Students name (SName) Student's email (SEmail) ID# of the form I use to record their grades (ID) ID# of the studen'ts work (WorkID) Date of their work (CreatedDate) Date I graded their work (AuditDate) Grade="F" (Accuracy)
How can I create something that will automate emailing of this information--to the individual student with only their information? I am using Access 2010. If I can't automatically send the emails, I would like to create a button in my audit screen that will send a report containing that student's information to them.
I have created a database that generates a report with customer debts. I have a lot of customers and in a report each page is a different customer with individual debts info. What i would like to do is to create a button and distribute individual report page as pdf to individual customer by email. I'm using Lotus notes. I have searched the forums i found something but it was not what i was looking for.
I'm toying with the idea of designing a database to simplify a task I currently undertake at work (using excel) - comparing price movements in the shares of a certain company with the average movement of the share prices in the sector, and displaying any material discrepancies on a certain day in a report.
From my limited understanding of access I think I would need:
- "PriceData" Table, with the fields - date, share, price, dailymovement% - "SectorData" Table, with the fields - date, sector, price, dailymovement% - "Relationship" table, specifying the sector that relates to each fund - A query which summarises the above, returning the date, share, and the two movement percents and then copies them into a temp table. - A query/report which pulls out the relevent (material) descrepancies from this temp table and presents them in a nice format.
Is this possible and am I along the right lines?! Any input would be greatly appreciated!
I have sort of problem - I'm trying to desing the database for one of the organisations. The entities are: Family, Child, Referral, Voucher, Receipt, Provider, Activity.
That's ok so far. I've been asked to do the option for the new address of the Child - in case if they move to the new place and keep the old one as well.
Family table is the 'child table type' for the Child table - family can have more than one child, but one child can be part of only one family (one to many relationship).
I thought to make a seperate table 'tblAddress' and store all addresses in this table - then link them to the family - this however requires from me to have additional field for - for instance - second address, third address, which in case there was no change in address would remain empty value. The other option - which obviously is absolutely waste of space is to add straight away additional address fields in the 'Family' table itself.
Don't you know of any other, more appropriate solution? If you need more detail - I can send the copy of the database which I have created so far. I would appreciate any help.
I work for a train maintenance company and to keep track of the defects we use access. Our data is stored in tables (eg unit1) and each defect is assigned a fault code (eg TRD.99). These codes are then used to report to our customer where errors our occuring on the trains.
There are 17 categories of code defined by the 3 letters at the start and the specific problem is stated by the digits. I need a method of tabulating the codes by unit number and a total given in another column. To do this I need a code to count the number of times each three letter code appears in the column of each units table and place the value in the corresponding column in the overview table. I then need a code to add up the total faults for each unit like the sum function in an excel spreadsheet. The final table should look something like this
Is there an Add in for Microsoft access that will using a gui based method, run queries, set up automated reporting (task Scheduler) in an easy to administer method. Quest Toad has a new add in Toad for Data Analysis. I am looking for something similar for access. Right now I am doing this manually via creating macros, etc. But there really should be an easier way. Thanks
I am designing a database for my company that keeps track of new classes and new courses created. I have a form for each with the create a class form taking the course title from the courses table/created course from the create a course form.
Here's my dilemma. I want to use cascading combo boxes for class location, facility and room but I think my table structures and relationships may be incorrect.
Here is a list of my tables and their fields:
tblClass ClassID (PK) CourseID (FK) InstructorID (FK) StartDate EndDate StartTime EndTime ClassMin ClassMax Materials (yes/no) MatNotes EmpID (this is the ID of the employee who requested a class creation) LocID FacID RoomID LogID (Logistical Coordinator ID, if applicable)
Course CourseID (PK) CourseName more...
Employee EmpID (PK) FName LName more...
Instructor InstructorID (PK) FName LName more...
Logistical LogID (PK) FName LName more...
Locations LocID (PK) Location
Facilities FacID (PK) Facility LocID (FK)
Rooms RoomID (PK) Room FacID (FK) LocID (FK)
When I run a query with class, locations, facilities, and rooms no information shows up. If I run the query with the relationships between facility/room, facility/location and room/location removed (leaving the only relationships between class and these three) it works. Why is that? Shouldn't there be a 1-M relationship between Location, Facility and Room? Or, should there be a M-M relationship and I should create tables with each PK?
I apologize for the long post but this has become quite frustrating (despite the simple logistics).
I have created a table in Access with the data I need to put into a line graph.
The data is in this format:
[Table]....
This just a snapshot of the data. What i would like to do is produce seperate line graphs for each town, charting the temperatures over the three years. Because there is so much data i need to find a way which can produce graphs on mass.
I am not sure where to post this so could someone please move it to the appropriate area thanks.
I want to make a graph that will show how many employees are in the company for each year (Going back 10 years and keeping up to date) I have never done this before and our teacher feels that its best if we learn on our own (basicaly he's an idiot).
How do I go about using the pivot chart?
I have made a query which adds up the total number of employees at the current time.. but apart from that I havent a clue. I want the graph to have the years on the bottom and numbers up the side (1,2,3,4,5 etc) and then the number of employees that were in the company during that year will be plotted.
I will use a table that is to be used for archiving to mke the query as it will have all the employees who were ever in the system record.
The table is thus:
Employee_ID Emp_First_Name Emp_Surname Age Emp_Home_Number Emp_Mobile_Number Emp_Address_1 (so on and including postcode) Date_Started Position_ID
I have no idea what to do other than to rename the axes and change the increments, but they are jsut numbers, no dates or anything.
I am a bit of a db virgin! So I would appreciate some assistance please. I am trying to display a graph in the form view of a db. The db is comprised of a standard form and table set up but the data comes from a series of linked excel spreadsheets. Basically it is a way of displaying student data for parents evenings at school and I would like a graph to make it the data more visual.
I have a quality control database that has a QCEntry table that contains information about each sample the QC technician takes from production. This table has a one to many relationship with the TestResults table, where the tests performed on the sample and their results are stored.
QCEntry table is structured like
Code: EntryID Product Lot Number Day Time 1 AB-500 121323 12/23 5:00
My question is: Is there a way modify large amounts of data like this using a query or some other method to look like this? Kind of denormalizing the tables?
Code: Product Lot Number Day Time Carbonblack MFI AB-500 12323 12/23 5:00 50 10
I am attempting to create a metrics analysis table from another table. What I would like to do is copy the structure (only) from table 1 into a new table. Change all the fields in the new table to text (except for an ID field which would be an autonumber). Then run a seperate group by query against each column, counting the values in each group (i.e. first query would have two fields The grouped column and the column count.
Once I have these values I would like to concatenate them (with the count in parens) and then push these values back into the new table under the appropriate column.
My code does this. I basically loop through a recordset that runs to each column/field groups and counts and then Edits the new table with the concatenated data.
My first table is 170 fields and 38K records. The issue is that it's too much for Access to handle and it blows up (on field 123) Telling me the File is too large. The file does explode to 1G. Then I can shrink it back down to 67mb by running a repair and compact... and then run the the data for the rest of the fields in that table. When I compact again I get about 80Mb.
So now I have two tables, both with an ID field... so I try to link them together (via a make table query) and meld them into one table... but it keep running into that "File Too Large" issue.
How can I have two tables in a database file with a combined size of 80Mb, but when linked together are too large for the database file? Does it have something to do with having all text fields?
I looked up the limits to MS Access and the field count doesn't appear to be an issue since it's nowhere near 255... So what's the problem here?
I have a table [Control Table] with the fields [Date signed] and [outcome] date signed is formatted as dd/mm/yyyy and the outcome field is a drop down with the options granted, not granted ect
I am looking for a way to present the data using specific date ranges.
I have found 2 possible avenues;
Dcount in a select query:
w/c 01/04/2014 GRANTED: DCount("[Date signed]","[control table]","[Date signed]>=#04/01/2014# And [Date signed] <=#04/06/2014# And [Outcome]='Granted'")
w/c 01/04/2014 Not GRANTED: DCount("[Date signed]","[control table]","[Date signed]>=#04/01/2014# And [Date signed] <=#04/06/2014# And [Outcome]='Not Granted' And [Reason not granted]='Assessed'")
w/c 01/04/2014 Discharged: DCount("[Date signed]","[control table]","[Date signed]>=#04/01/2014# And [Date signed] <=#04/06/2014# And [Reason not granted]='Discharged'") etc...
But I would need to create the multiple queries 52 times each for the different count value per week
My 2nd option
I have looked at crosstab query, but I cant find a way for it to list the specific dates I need it to query e.g from
I do not know why this has been so difficult. I have search google and even this forum. There is no clear cut explanation on how to build a button on a form to export a pivotchart to a jpg. Does anyone know how this can be done. please show code.
I have a number of deliverables and want to create a graph for each one, I can get a list of deliverable via a select statement but not sure how to create a graph from that
For example I have this sql statement as the row source for a graph
Code:SELECT Team.TeamLeadName AS Expr1, FormatPercent(Avg(RawData.PercentComplete)) AS CurrentProgressFROM Team, RawDataWHERE (((RawData.TeamLeadNumber)=[Team].[TeamLeadNumber]) AND ((RawData.DeliverableDesc)="RACE Off-the-runs v1"))GROUP BY Team.TeamLeadName;
"RACE Off-the-runs v1" is a deliverable
I had the idea of taking the list of deliverables and iterating through them, for each iteration set the row source for a graph as
Code:SELECT Team.TeamLeadName AS Expr1, FormatPercent(Avg(RawData.PercentComplete)) AS CurrentProgressFROM Team, RawDataWHERE (((RawData.TeamLeadNumber)=[Team].[TeamLeadNumber]) AND ((RawData.DeliverableDesc)="deliverableNAME"))GROUP BY Team.TeamLeadName; where deliverableNAME = the current deliverable in the iteration
The problem is 1)I’m not sure if/how to set the row source with a variable (in this case the variable is deliverableNAME) 2)How would I make sure that each iteration its creating a new graph, rather then just overwriting the existing one. Basically how to have each iteration create a new graph. 3)How would I store these graphs, each one on its own form, can they all be on one form (access seems to limit the height a form to 22 inches)
New user here so apologies if I post in the wrong place.
My colleague and I are trying to put together a database which automates a very time consuming process which is currently being done in Excel.
The purpose of the database is to pull together a load of actuals from SAP and then the forecasts we have put together and then chuck out a whole load of graphs and a summary spreadsheet/ report for the upper echelons;ons of management.
The data is in the database and so far it is all going well - but we have hit a stumbling block. We need Access to calculate some cumulative totals so that we can throw the whole lot into our excel graphs but we can't figure out how to do it.
We are using ye olde Access 97 so our options are a little limited.
Can anyone give us a starter for 10? If you need anything more technical, I can post whatever information you need.
i have a graph in access, and when i print it on a laserjet printer, all of the data and text prints. When i print the graph on a inkjet, only the text prints...not the graph. How do i solve this?
I have a larget transaction data set in access with Datetime column/filed.
I have been running pivot queries to excel to do analysis of the data but the datetime field is returning too many unique values for the pivot table to run.
What is the best way to reduce the datatime field to date only and where should this be done?
i.e. should I have a calculated field that trims datetime or should I set someohting up in Powerpivot?
I am about to start a project where I must link an Access front-end database to an Oracle backend. I have about a year of Access development behind me now, and now here is the next challenge.
What I have to do is create analyses of data and output the data mainly in the form of diagrams (i.e. graphs or charts). I have seen how this can be done with Automation in Access in a book by Alison Balter (Mastering Access 2000 Development), by controlling the Excel object etc.
Here are my 2 questions:
1. I am considering buying another book on Access now, something that will contain more examples/methods for presenting information as a graph with the help of Excel. Should I stick to buying an Access book, or should I look into getting something that is more Excel programming oriented, i.e. something like an “Excel programmers compendium”? Any recommendations/tips to get me started would be appreciated.
2. The users want the graphs that I produce to be saved as a .PDF automatically at the push of a button in the Access GUI. How is this done? Do I need special software, or can I use something like PDFWriter to automatically convert the Excel graphs into .PDF’s?
Thanks in advance for the tips and have a nice day! :)
Using Access 2010 32-bit. I have a report that contains a Graph Chart (class = MSGraph.Chart.8). The reports works fine using the uncompiled .accdb. When I compile to .accde, the report is not rendered...I get an empty report screen as though Access is going to produce the report and nothing happens. I have to terminate Access using the "X" at the top right.
