I am trying to create a report in Access that sums the number of instances of items listed in two separate tables.
Tables:
Impact (Customer Impacted, Customer Not Impacted)
Reason/Source (Mail, Keying Error, Other Error, Transfer Error)
For example I am looking for the number of instances:
- Customer Impacted = Mail
- Customer Not Impacted = Mail
I currently have a 2010 database that I use for clients and the work I do for them. In the sub-forum is the work I do for a specfic client. Currently I have a checkbox that I check if I want that specific Work Done on the there invoice. My invoice is a report/SubReport.
As you see in the picture I have 2 different items of work that I did. I was wondering if I can sum up everything I selected and show it at the button on my invoice (Report). So for this one it would be Total of 160.00 Right now the Text19 box that I have is in my report footer of my main report.
I'm trying to design a report which lists jobs that are overdue. I've got a field called [ExpectedCompletionDate] and a field called [Status]. The Status field is polulated by a combo box, which has the following options
Started
In Progress
Complete
My issue is that i'd like the report to list all the jobs which have exceeded the expected completion date and whose status is not listed as 'Complete'.I'm using the following code in the underlying query
Overdue: IIf([ExpectedCompletionDate]<Date(),IIf([Status] Is Not "Complete","Overdue"))
using Access 2000.I have a table with fields (image, info1, info2, info3).I want to produce a report where 15 records are displayed in a table like manner. each table cell contain the whole record and the table is 5 rows by 3 columns.
View 4 Replies View RelatedGood afternoon!
I have a database that tracks the withdraws and returns of shared tools.
The withdraw form has a check box on it that is checked to show removed when the item is ordered (this keeps that item from showing up in inventory searches). When the item is returned, that check box is unchecked, showing that the item can be withdrawn again.
When a person withdraws an item their supervisors name is also logged so that we know who to chase down when the item doesn't come back.
I have a query that is supposed to show what items are still outstanding based on a date range (I got the prompt for date range to work..yea me). However, the query shows every time that the item was borrowed.
Example...
I borrow a hammer and my supervisor is Bob. I return the hammer. It does not show as still withdrawn in my query.
The next I borrow the same hammer and again my supervisor is Bob. I return the hammer. It does not show as still withdrawn in my query.
The day after I borrow the same hammer and my supervisor is Joe. I return the hammer. It does not show as still withdrawn in my query.
Some other day I borrow the same hammer and my supervisor is Joe. I do not return the hammer. The two times I borrowed the hammer with my supervisor as Bob show up as well as the other time that I borrwed it with my supervisor as Joe shows up as well as the one that shows I still have it.
. One of the criteria in the query is essentially only show the items that have the check box checked (criteria=true). But that apparently does nothing, or (more than likely), I am doing something wrong.
This is the SQL:
SELECT tblOrder.OrderDate, tblOrder.Supervisor, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number
FROM (tblEmployee INNER JOIN tblOrder ON tblEmployee.GlobalID = tblOrder.GID) INNER JOIN (tblItemSpecifics INNER JOIN tblOrderInfo ON tblItemSpecifics.Serial_Number = tblOrderInfo.Serial_Number) ON tblOrder.OrderNumber = tblOrderInfo.Order_Number
GROUP BY tblItemSpecifics.Removed, tblOrder.OrderDate, tblOrder.Supervisor, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number
HAVING (((tblItemSpecifics.Removed)=True));
I've got a table with 23 columns. Column 1 is the ID row which has a unique client ID in it. Then we've got a column called 1st_Reason and one that goes with it called 1st_Transfer_Date. This pair repeats for 2 through 10. Each of the Reason columns can have a set value, for example "First Processor".
What I need to do is create a date with months on the Y axis and the 5 different reasons on the X axis. I need to count the number of "First Processor" across all 10 Reason columns for each month. I would need to repeat that for each other reason type, but if I can get one to work I can simply change the reason type.
Here's what I'm using to count May's total:
Code:
=Sum(IIf([Current_Reason]="First Processor" And [Current_Processor_Transfer] Between #4/30/2013#
And #6/1/2013#,1,IIf([1st_Reason]="First Processor" And [1st_Transfer_Date] Between #4/30/2013#
And #6/1/2013#,1,0)))+(IIf([2nd_Reason]="First Processor" And [2nd_Transfer_Date] Between #4/30/2013#
And #6/1/2013#,1,IIf([3rd_Reason]="First Processor" And [3rd_Transfer_Date] Between #4/30/2013#
[Code] .....
I get wildly inconsistent results. On one of the types, it's only counting where it's the 1st_Reason. Some of the others return the same value regardless of which month I have selected.
I am building a replacement Access 2013 database for our ageing Access 2003 database. I have an invoice form with a subform with a combo box that allows me to select which jobs to invoice based on the customer selected in the invoice form, which is working fine. I have designed my invoice and have the criteria [Forms]![Invoice]![ID] in the report query so that it just selects the record that is open in the invoice form. I have an ID (which is the invoice number) group on the report and it is then sorted by date and then job number. I thought it was all working fine when I tested it, it is the same method as I used in the 2003 database. However I have a problem, when I make up and print the customers first invoice it works fine, but when I go to make up a second invoice for the same customer the invoice includes all the jobs from the first invoice as well, and if I then go to reprint the first invoice all the jobs from the second invoice as there as well.
View 1 Replies View RelatedI've been running into this problem in Access. I have records which have more than 255 fields. The easiest solution would have been to get a table with more than 255 fields, but I can't do this, so I've had to split up the tables. The next easiest solution would be if there is a way to link the tables--I have a data field "IntAccNum" identified as the primary key in each table--so that when a new row is formed in one, it is formed in the others. This wouldn't waste space, since any row in the first table would necessarily need a corresponding row in other tables. Is there a way to do this?
If not...
The problem I run into is when I populate a row in the demographic table and then try to fill a corresponding row in another table. I use a command button to for a macro to "OpenForm", and then try to "SetValue" of the appropriate field in the second table. This adds a new row in the second table. This does let me populate the correct row of the second table the first time I try for each row. However, after this initial population, when I switch back and forth between the first and second table, I get an error in access since obviously I can't keep adding a primary key with the same value in the second table.
Do I need to do some sort of query to see if that row exists in the second table already. Or is there some simple solution to what must be a very common situation that I am overlooking?
I'm completely new to Microsoft Access. This project was thrown my way. I have an accounting database to track payables, receivables, financials, and deliquencies/collections. Is there a way to generate a report for any of the items in the previous sentence that haven't been completed to keep track of workload.
View 5 Replies View RelatedHi just started working for a Company and I found they have tons of
databases but no structure or standard, so I would like to build a tool that will go thrue a list of databases and get all the table names, the items and type. Is there functions in vba that can get me a list of the tables in the database and the get all the items in the database ?
Thanks on any input
I have items, but some of them have multiple names for one item. Is there any way to tell Ms access that that item has those other names? I have 5 fields, named "Model" "2nd Model Names" "3rd Model Names" "4th Model Names" and "5th Model Names."
View 12 Replies View RelatedI have a main form (Parent) along with a subform(Children). I want to have a button that generates a report with the Parent information as a header and the items in the subform as details. In addition, I want the report to show only the children that were recently added not all of the children.
View 1 Replies View RelatedSo I've been reviewing cascading combo boxes and I am finding some good stuff. But here is a preliminary step I need. I need to tag each record with something like what month are they assigned in, and what products. Then I need the combo box to display what that month is that they are tagged in, and then populate the second combo box. It is the second combo box that will show the record on the form. Its possible that there will be two items they are tagged with.
View 7 Replies View RelatedI have a form with a combo box named 'Venue'. The combo box values comes from a Table with a list of about 200 countries. As of now a user is able to select only one country. However, I want users to be able to select more than one country.
View 1 Replies View RelatedI'm having an issue with pound signs in my table for specific items. I've create a DB that when you click a command button it opens a form and in the form it has a notes field. In the notes field once where i had data is now pound signs.
The DB is setup so when you click another command button to close out, it saves the record. It works 98% of the time but the i'm concern about the other 2% of the time.
I have a table with inventory items, a separate table with storage charges per day (ex .03, .04, .05 per day/per item)
I have created some queries where I take the items & # of days they have been in storage and when I try and create an expression for storage charges based upon QTY & # of days I am getting results like it is multiplying whole numbers and not very small increments like .03
I have checked the math, and its not multiplying by 3 instead of .03... I cant quite figure out how it is coming up with the numbers.
Again, the pricing is coming from a lookup wizard to another table. It seems like it should be a very straight forward expression but I cannot get it to work. Does the figures being from a lookup have any issues?
I just want to know how to manage items in set and individual item. Suppose my product list are
individual items = A,B,C,D,E,F,G,H,I,J,K
and 5 pc set = A,B,C,D,F
and 3 pc set = G,H,K
How should I design the table. Previously I designed the table for individual items and whenever orders for set is placed user had to enter individual items with quantity.
E.g. order is for 5 pc set = 3000
A=3000
B=3000
C=3000
D=3000
F=3000
Now I need to just say 5 pc set and it should be automatically populated. And also if order have combination of individual and set items.
Despite Google I can't seem to figure this out.
I have some data in a format similar to:
Name / Style / description / speed / distance
john / driver / careful / 80 / 5500
mary / driver / careful / 70 / 7000
pat / racer / reckless / 100 / 6000
anne / driver / careful / 75 / 1000
peter / racer / reckless / 110 / 6500
don / snail / slow / 60 / 6000
I want my report to total by style, without details and to look like:
driver careful 13500
racer reckless 12500
snail slow 6000
How do you get a report to sum the group items by a specific item and to hide the details of that group summing?
In my tables i have used calculated fields. one of the fields is to "total expenses." In a report, i need to show the sum of all the "total expenses", the filed populates in the report but the cents are missing. for example if the amount is 6080.40 it shows as 6080. how can i get around this? I have tried changing the decimal point value to 2 at which point the value turns to 6080.00 when it should be 6080.40 (i am a beginner at this i am assuming the answer will probably involve c++ or visual basic's, two concepts i am not familiar with.)
View 2 Replies View RelatedI 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 RelatedHi guys, i have two databases. I need to Copy 'some' tables and reports from one database to the other one. Do i just do right click and Copy table and
paste it in another database.
Thanks,
Kon
Guys,
I want to write a report. I have 10 different tables now what I want to do is. Create a report from say 6 out of these 10 tables. These 10 tables have some common columns and some different. If a table doesnt have a particular column, it can be blank in the report. But say "title" is common to all tables, so it should pick up this "title" from each table. How can I do this???
Adwait
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 have a DB with 3 tables;
1) Customer details (address, phone etc)
2) Equipment they have
3) Service History
So that I can print a report showing all of the above on a A4 form I have a simple query which filters using a tick box (on Customer Details).
This works well unless I tick 2 box's. I then customer details, equipment & Service History get jumbled up and isn't record specific.
I'm designing a database for a laboratory. There are many tables that will contain test results. They all have in common a field called ID( primary key) linked by a one to one relationship because one patient has reports in different tables. The ID is unique to each patient. For the Reports, i hit the rocks... How can i structure a report that can extract a patient's records from the different tables where they appear in the database?
View 8 Replies View Related