Ok, I'm driving myself crazy! I've built quite a few databases in Access and pretty much know what I'm doing, but I've hit a brick wall on what seems to me a very simple database...
I'm trying to make a database that will keep track of my investments. I'm practicing to be a day trader in the Forex market and want to put something together that will let me see how I'm doing on an average basis.
In my 'Trades Table' I have an AutoNumber, Date, TimeEnteredTrade, CurrencyPair, NumberOfLots, TimeInTrade, and ProfitLoss. With this data I'm trying to group everything by date, since I might have multiple trades per day. From there I want to know how many trades I made that day, how many were profitable (count), how many were losses (count), total $ of gains (sum), total $ of losses (sum), and Net Profit (difference of profit-losses).
I've get the group by date to work fine, I can also get a count for the total number of trades grouped by date. Where I hit the wall is trying to get it to show the total trades>0 AND keep the total number of trades. I've used 'where' and have gotten it to show me >0, but when I do that it also changes the total number of trades for that day...
Hi, Im not sure how to ask this but i need to add together the total amount of times a see a persons name in a field i.e a person signs for 5 boxes with 5 signatures i need to be able to add those together so it gives me a value of 1 is there anyway i can do this?
First of all, we have a database for all company customer cases (through out the whole year of 2006). Every case has its own priority level.
‘Priority’ column has three option, ‘High’, ‘Medium’, and ‘Low’.
We are new to Access, and we are trying group all the case month to month (group them by their created date) on separate pages. At the end of each monthly summary, we’d like to do a number count on ‘High’, ‘Medium’, and ‘Low’.
Here is a sample that we are trying to get to…
Sample Datebase, Case #// Created Date// Priority Level 001 12/5/06 High 002 12/7/06 Low 003 12/3/06 High 004 12/1/06 Medium 005 11/9/06 Medium
Sample Report we are trying to get to... Summary for December: Case #// Created Date// Priority Level 001 12/5/06 High 002 12/7/06 Low 003 12/3/06 High 004 12/1/06 Medium
Total Case: 4 High Priority Case: 2 Medium Priority Case: 1 Low Priority Case: 1
I have a report due the first of each week in which I need the cases open and cases closed for the previous week, the week two weeks prior and the 2007 and 2006 year to date on two different types of cases. I have a case management table with a field for Type of Case, date assigned and date closed that I uses in my queries. Presently I have two query, one that generates only Type 1 cases from the Case Management Table and another for Type 2. I then use the Type 1 Query in another query that limits the results for Type 1 cases to those opened last week, one for those open two weeks ago, one for 2006 YTD and one for 2007 YTD. In these 4 queries I have one field [Type of Cases] and I have the query count. I then do this for Type 2 cases and then go through the whole process to do Closed Cases. All my queries have criteria to automatically filter the dates to the time periods mentioned above. I then have one report query that I put all the number in for my report. This query has 16 fields with the numbers for each period, last week open and closed, 2 weeks open and closed, etc. I then generated a report that takes these numbers from my report query and puts it in a report format automatically. As you can imagine this takes some time to go through each query to generate these numbers, so I was wondering how I may do this differently. Also, I have experienced a problem when a field produces no records I get a blank sceen with nothing under the Count of column and get the same thing for my report. How can I fix this.
I have created an invoicing system for a CD shop There is a transaction table which has each individual cd sold as a seperate transaction. Each transaction has an order number, so there can be more then one cd sold per order, but they all still have their own record in the table.
im now invoicing each order by mail merge based on a query that finds all the details on every transaction but wht ive found is that the mail merge puts each transaction onto a different page even if its from the same order number as another.
does anyone know how to group each order in the query so that all the items in one order come in a single invoice?
Hi, I have a query which numerous fields, and I need to make a report based on the query. However I need to group 3 fields in the query and press the sum button on 2 of them, Qty and Total Price (which is qty*price)
I need to do this so in the report when a particular wine is purchased more than once, instead of repeating the peoples name who bought the wine, it will only show 1 and automatically add the rest to the total price.
I dont know how to group within a query, can someome please tell me how? Thanks.
Hi to all; I have one code of 6 digits; each digit refer to a group; first 2= product group; beverage; live animals,….itc (24 product groups), the 3ed digits= food product, the 4th-6th digit= detail product group; vegetables, fruits,….,the rest of digits refers to product name, carrot, apple,…itc , example 070511 How can I use the query to sum the product value at different group level; example the first 2= product group, ..? Do I have to split my code to 6 cods to do my calculation? Thank in advance for help majed
I have created an invoicing system for a CD shop There is a transaction table which has each individual cd sold as a seperate transaction. Each transaction has an order number, so there can be more then one cd sold per order, but they all still have their own record in the table.
im now invoicing each order by mail merge based on a query that finds all the details on every transaction but wht ive found is that the mail merge puts each transaction onto a different page even if its from the same order number as another.
does anyone know how to group each order in the query so that all the items in one order come in a single invoice?
Hi all I am trying to find a way of finding the number of a group of sessions as a percentage figure. e.g. total number of clients attending 1-3 sessions = 20%, 4-6 =15%, 7-12 =21% 1-2years = 8%, etc. and entering this calculation in a report.hope you can help. Dave
Question on grouping within Access Reports: A simplistic view of the report I'm trying to generate is as such:
Company Name - Company Description - Employee
I am grouping by Company name, and I am hiding duplicates of Company Description because they can be long. I also have the Company Description as Allowing to Grow.
The problem is that the first record gives the company, the description, and the employee name on the first line just fine, but the second employee name won't be listed until the Company description ends. When the description is about a paragraph long, the other employees look very seperated from the initial record.
can form objects be grouped? i currently have numerous buttons on a form that are shown according to a button selection. my current code makes all buttons visible / invisible singularly but i wiondered if they could be grouped/ named and the get the code to make the group visible / invisible?
I am trying to group time entries so that I can say, between 8AM and 10AM there were this number of calls. I have a field called Time_Assigned with numberous time entries (such as 08:15:33 AM) corresponding to another field called Incident_Type (such as medical). The data spans a whole year so I have several thousand time entries and I would like to show how many incident types occur between such and such hours. Thanks again for everyone's generous help.
Hi all, I have a db for logging meeting action points. Each action point has a description and owner. I'd like a query/report which will produce a list of action points grouped by owner (easy), but with a particular owner's action points at the top.
Essentially, rather than do a GroupBy and ascending sort, i need to specify the first group that is displayed. I don't care in what order the other groups appear.
Eg.
Action Point Owner 1. Task 1 DC 6. Task 6 DC 8. Task 8 DC 3. Task 3 AG 4. Task 4 AG
So, above if i just did an ascending sort, the DC records wouldn't be at the top.
I have a table which stores meeting room booking information in half hour slots. If someone books a 2 hour meeting then 4 records would be produced – one for each half hour. I want to produce a query which will group the data by room (ScheduleID) showing the max and min times (ie the initial start time and final end time) for each event and the event details. The table structure is as follows
SELECT [Schedule Details].ScheduleDetailsID, [Schedule Details].ScheduleID, [Schedule Details].CustomerID, Min([Schedule Details].ScheduleStartTime) AS MinOfScheduleStartTime, Max([Schedule Details].ScheduleEndTime) AS MaxOfScheduleEndTime, [Schedule Details].[meeting purpose] FROM [Schedule Details] GROUP BY [Schedule Details].ScheduleDetailsID, [Schedule Details].ScheduleID, [Schedule Details].CustomerID, [Schedule Details].[meeting purpose];
Can anyone tell me where I have gone wrong. It does not group the data as I want it to ie by room, then time, with only the initial start and final end times. Thanks in advance. Peter
I have a query which has many sums and counts on things like "Company name", "region name" and "Development Name".
I'm using this query for a report to do lots of percentages with, but now i need to filter this also by a date period.
So the user choses "Alex Homes" as the company name and then "July 2006" as the reporting month, and i need all the sums and counts to stay the same and only count/sum the records in the chosen month.
I have a set of evaluation response data. There are a number of question (like 20), and each respondent answered the question on a scale of 1 to 5. Thus I have a table Responses where each column is a question and each row is a Respondent's answers. Each cell has a value between 1 and 5 (inclusive), or is possibly NULL.
What I want is to be able to list out the number of people who responded with a certain evaluation for each question. So it would list the number of people who gave a 1, a 2, a 3, and so on for each question. So I'd get something like:
---------Q1 Q2 Q3 Q4 Q5 ... -1- x x x x x ... -2- x x x x x ... -3- x x x x x ... -4- x x x x x ... -5- x x x x x ...
Ideally I'd like the questions to be on the rows and the numbers as columns, but I can just do the transposition when I make this as a report.
Any ideas of a SQL Query that will get me such a table? I'd really like to avoid VBA if possible (I'm writing this database to be used/maintained by a non-programmer), and I'd like to not have to develop like 20 different subreports in order to print out this information.
I am having a problem that I hope has a very simple solution that I am somehow overlooking.
I have simplified my query for the purposes of this question. I have a query that only get the date and amount of a transaction. I want to group the information by date and have the transaction amount summed. For some reason it will not group by date.
Here is my query displayed in SQL: SELECT Transactions.Date, Sum(Transactions.TransTotal) AS SumOfTransTotal FROM Transactions GROUP BY Transactions.Date ORDER BY Transactions.Date;
And here is a sample of the data returned: DateSumOfTransTotal 12/13/20055.12 12/13/20055.12 12/13/20055.12 12/13/20055.12 12/15/20051.15 12/15/20051 12/15/20050.12 12/15/20056.56 12/16/200519.14 12/16/200512 12/16/20058.16 12/17/200511.11
Why will it not group all the 12/13 or 12/15's together? I have done other queries like this and not had this problem. What am I overlooking?
I have a table GIS_Subs with following fields: Force_No ( Foreign Key) Subs_Dt Block Yr Subs_Amt (number) Running_Total I wish to update Running Total for each record based on previous pay sorted in Ascending. I am able to make a running sum but then it clubs all PersonNo of same date like: [COLOR="Blue"]SELECT GIS_Subs.Force_No AS FN, DatePart("yyyy",GIS_Subs!Subs_Dt) AS AYear, DatePart("m",GIS_Subs!Subs_Dt) AS AMonth, DSum("Subs_Amt","GIS_Subs","DatePart('m', [Subs_Dt])<=" & [AMonth] & " And DatePart('yyyy', [Subs_Dt])<=" & [AYear] & "") AS RunTot FROM GIS_Subs GROUP BY GIS_Subs.Force_No, DatePart("yyyy",GIS_Subs!Subs_Dt), DatePart("m",GIS_Subs!Subs_Dt) ORDER BY DatePart("yyyy",GIS_Subs!Subs_Dt), DatePart("m",GIS_Subs!Subs_Dt);COLOR] How can I calculate it for each Person seperately?:rolleyes:
I'm in the process of setting up a form, and I have 4 yes/no fields that need to be in it. I want to group the fields so that only one of the yes/no fields will be able to be selected. The fields are: Pass, Fail, N/A, and obsolete.
I tried setting up an option group, but I can't seem to get the information to filter back to the table that is capturing the data.
Group Header: Division Group Header: Subdivision Detail
Is it possible to get the Division to only appear once per value?
e.g.,
Division Subdivision Detail Subdivision Detail
NEW Division Subdivision Detail
etc.
Right now the Division appears before the subdivision every time, even if the division is the same as before. I did change "Hide Duplicates" to "yes", but that didn't help.
