I have a Access database of employees and I need to count how many worked for each organization in the company by month. I can't figure out how to break this problem down and solve it. I can do this ten ways from sunday in VBA, but I'm looking for an SQL solution.
My data looks like this (simplified) example...Table.jpg In this database, a process runs nightly and adds any new employees with the [CreatedOn] date. This value never changes. The [LastUpdate] field is updated to the time the nightly process executes as long as the employee is still at the company. So when an employee leaves, the last update field is no longer updated. So in my example data the employees in the 1st, 4th and 7th line no longer work for the company.
I've been trying to produce a query(s) with these results...Results.jpg I've tried several incarnations of various queries with subqueires to filter [CreateOn] and [LastUpdate] so that I can count the total employees in each organization at month end. None produce correct results.
My goal is to ultimately produce a chart that looks like this...Chart.jpg
I want to be able to find duplicate numbers in the "claim #" field that exceed 20 within the "date of service" field for a month. I have a date box which allows me to select data from only one month at a time or all. What I'd like to be able to do is group the results by month. Currently it is only matching numbers by day not month.
SELECT HMR.[Claim #], Count(HMR.[Org #]) AS Total, HMR.[Date of Service], Count(HMR.[Date of Service]) AS [CountOfDate of Service] FROM HMR GROUP BY HMR.[Claim #], HMR.[Date of Service]
I want to Count the amount of not treated improvements after 1 month in a report. The rules will be if the improvement cell is empty one month after the date the improvement is registered, then it should be counted.
I got this far: SELECT DISTINCT count(a.ID) as mCount, year(dimDate.Date) as [Year], month(dimDate.Date) as [Month] FROM admin AS a INNER JOIN dimDate ON (dimDate.Date >= a.sDate AND dimDate.Date <= a.eDate) OR (dimDate.Date >= a.sDate AND dimDate.Date = null) GROUP BY year(dimDate.Date), month(dimDate.Date) ORDER BY year(dimDate.Date), month(dimDate.Date)
i''m currently trying to count the amount of records that fall in each month in the date complete field. i am currently using a count query and it it bringing up the dates not the month.
I am working on a school project called employee skills. I have a table of skills, each has a check box, which if checked is true. I am trying to write a query that will find who has a specific skill.
i'm trying to create a query of all employees doesn't have any transaction for a certain range of date and will also shows the last transaction date they have.i have two databases one is the transaction file and the other is the user file.
I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
Hello all, I need your help please, i have a Form that also has a Subform in it. I need to create a Text box on the form that will display a number which will reflect the total enties made against a particular ID within a rolling six month period using the system date as a perameter. The number shown will only reflect previous entries made that fall within the six months from the current system date, and will need to update once new entry is made. Incase i have confussed the hell out of you, let me give you an example:
Example One
Entry made today = 24/02/08 Previous entry Date = 20/10/07
Textbox on Form = 2
Example Two
Entry made today = 24/02/08 Previous entry Date = 20/08/07
I think i am missing the obvious but cannot seem to get this one....
I am trying to get my form to return a value of the amount of working weeks in a month i.e. a 4 week month (like june - 4 mondays) or a 5 week month (like may - 5 mondays).
i can calculate the number of full weeks using the control source..
I have a table that has a field for month represented by a number. I would like to report by month but group by year. The report would group months 1-12 into year 1, months 13-24 into year 2, months 25-36 into year3, etc..... up to possible 20 years. Is there an easy way to identify and group the year? I was thinking of adding another column to the table and when I write the values to it include a year. I would rather do it some other way as the data is spilt and would require a lot of table modifications to distributed db. It would be easier to do it with a query or some code in the front end.
Recently, my parents have bought a campground and I agreed to attempt to build a database which they can useto keep track of openings, reservations and I want to be able to now calculate the price for the customer. I know I can easily create an "invoice" using the forms.
Originally, I thought this would be easy and it actually wasn't too difficult, I had everything set up to go. Then I hit a snag. The prices are by days, weeks, months and season and get slightly cheaper for each tier you go up. Charging for a week is cheaper than charging the daily rate seven times, makes sense right? it makes it difficult for me because, I'll use the example of nine days, for a stay like this you would charge for one week and two days.
I can't just convert the days stayed into a number like 1.3 weeks and multiply it by the weekly rate because it would be undercharging the customer by about $30 and this number would go up as the stay got longer. I know I could do something like an "IIf" statement but in order to cover all the variables, that would be a long equation with lots of spots where a mistake could be made. Through doing research, and asking here, I figured that it would be best to develop a UDF which would take the number of days stayed and convert it to something which would count them and turn it into a number like "1 week, 2 days". From there I will have to figure out how to make it so that the program knows which sections to multiply by which rates to build the price. I have taken a course in Access and have a huge book relating to the subject but I'm not quite sure where to begin.
I have a table with two fields named as "Month" and "Bank Holidays". I have put names of the month in this table for 2014 and the corresponding bank holidays of each month. Now I want to write down the piece of code that will execute when a button is pressed and check the current month of the year and then display the total working days ( After subtracting bank holidays of that month and weekends of that month from the total working days of that month).
What I have: A table with multiple Date/Time columns. Here's an example:
What I would like: A month-by-month summary counting the number of entries in each of the three columns, like this:
I'm indifferent as to what happens with the months zeros across all three fields (whether they show up in the report with zeros or are omitted altogether).
Smaller Pieces: I am able to create such a summary for one column at a time:
Not what I'm looking for: I can not figure out how to create a query that does this for all three columns. My best attempt was a disaster:
I have attached the example file for your convenience: Database1.accdb
To clarify, my issue isn't the format of the date. I know about the Month(...) function. My question is regarding how to count this data and split it into a month-by-month summary (see the picture above in the "What I would like:" section)
The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.
Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?
current SQL SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A FROM [One Year Data Lines] GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
Hi ya, the database that I'm working on has many employees for example 2 directors, 6 managers, 22 tutors (there are different types of tutors permanant temporary etc), 4 finance managers etc etc when you click on one of the employee type it expands and shows how many employee are under that section and their details etc, like when i click on director, there is this + sign beside it and when I click on it, it expands and shows me info on all the directors only.. How would I get that?? Do I have to make a seperate table for named employee type?? but then how would I add a relationship to it between employee and employee type?? how would I go about doing that...
Also I need to know how to do it since I need to use queries lateron which will only be about tutors and the courses they teach, and since there are many different types of tutors I'm kinda lost... Thank you for your help in advance!
I've been asked to make a 5 questions test to evaluation potential new employees' knowledge of MS-Access. Honestly, I need your input on what to ask. The goal is not to make a very hard test, but rather to see if somebody as at least basic knowledge of SQL and Ms-Access.
Some background information of what we do with MS-Access We use MS-Access as a central hub for different download from different systems. We make a lot of daily imports. We export queries in Ms-Excel. We open it from Access and format the report. We make hundreds of reports every day. We don't really use forms or reports.
What questions to ask? I was thinking of having 2 questions about SQL. One simple query with a formula, and a query with 2 tables (perhaps a left join). On what should my other questions be?
CUSTOMER ID / CUSTOMER NAME / PAYMENT ID / PAYMENT DATE
Only want to show the last of each payment per month in form based on query.
So if they have a payment in April of $900 on 4/15 and then a payment in May 5/2 $500 and May 15 $1000
It will show two rows...one for April $900 payment and one for May $1000 payment.
I know the logic is something like
If more than one payment row for month, show last, otherwise only one payment for that month show the one payment.
I tired just setting hte "last" paramater but it only shows the very last payment, not taking month into account. So it only shows the $1000 may payment. thx
Hey guys- I am importing data on a daily basis- some of it contains existing records, some of those existing records have updated information, and then the rest will be new records. I am importing into a table, say, tbImport.
I have 3 tables I am splitting this info out to- tbListings, tbAgent, and tbAgentOffice. Each record can have up to 3 Sales Agents on the file- Agent 1, Agent 2, and Agent 3. Each Agent has their own Employee ID, as well as Office ID, Mobile, Home, Pager numbers. Each Agent can belong to only ONE Office, but every office can have multiple agents. I am using the Agent's Employee ID (it's assigned by another SQL db- and I will never be assigning this freely to anyone else, or new agents- they come into the DB already assigned a unique code).
Agent 1, 2 and 3 can freely change positions (Agent 1 can be Agent 3 at times, etc etc) from record to record. However, I want to store all their contact info into a separate table, going for 3rd Level Normalization...
So what I have is this-
tbImport _____________________ fldInfoID fldWhatever fldAgent1Code fldAgent1Name fldAgent1Mobile fldAgent1Pager fldAgent1Home fldAgent1OfficeName fldAgent1OfficeCode fldAgent1OfficeFax fldAgent1OfficeNumber fldAgent2Code fldAgent2Name fldAgent2Mobile fldAgent2Pager fldAgent2Home fldAgent2OfficeName fldAgent2OfficeCode fldAgent2OfficeFax fldAgent2OfficeNumber fldAgent3Code fldAgent3Name fldAgent3Mobile fldAgent3Pager fldAgent3Home fldAgent3OfficeName fldAgent3OfficeCode fldAgent3OfficeFax fldAgent3OfficeNumber **plus a ton of other data- but you get the idea here
So- the tbAgent table will store all the contact info for every agent, and tbAgentOffice stores info for their offices. I know I'll have relationships from tbListings.fldAgent1Code (as well as Agent 2 and 3 at the same time) to the tbAgent.fldAgentCode. I'll also have a relationship from tbAgent.fldAgentOfficeCode to tbAgentOffice.fldAgentOfficeCode. So, when I do daily imports- I will need to run an update query from the tbImport table into the other 3 tables at the same time. My question is this- How do I cram the individual fields for all 3 agents, into one universal field in the tbAgent table at the same time? I can't get my mind around the concept here. How will it take all the fields for each agent in the tbImport table- and cram them into 1 record set (while checking for existing/update records) in the tbAgent table? How do I write that in the sql code to tell it to link Agent2Mobile into the same Record as Agent2Code, and not into the Agent1Code or Agent3Code? Right now it seems to me that there's no way to distinguish between the Mobile Number for Agent 1 2 and 3 compared to their own Codes to identify the records with. Does that make sense?
Any help you can provide is EXTREMELY appreciated!
Hi, I've lost touch of Access few years back & now I'm back again using Access... kinda rusty & yet rushing for a proj... so I'll very much appreciate if someone can help me on this..... :)
I've a database storing inventory details & after adding these details, i'm supposed to have another form where i'll start assigning these assets to different employees..& of cos, i'll need to edit this form over time if the employee left the company or asset is being transferred to another person. However, this asset list is quite comprehensive with thousands of records, so it isn't feasible for me to list all assets at one go & start assigning different asset to different employee... any idea how I shld go abt assigning? shld i minimise the search by invoice key & track no? (each invoice contains a few grp of items & each grp will contain several items, therefore, i've assigned an invoice key for each invoice & track no for each item.)
Hai guys Iam looking out for a sample DB for employees leave management. # With leave application form # Leave approval form # Leave status # All with username and password
On my form I need to limit the number of employees that can be entered using OnCall as the criteria or use ExpName or Employee ID
If this criteria is NOT met then disable On Call. In other words if they try and enter a 5th employee then the field On Call/checkbox is disabled
name of field is OnCall and actual control is named On Call
I need this to fire on the OnCurrent Event for the form which is named frm_Employees_on_Call_What_Order
In other words, no more than 4 employees are allowed to be on call at the same time. The fields for the query the form is based on are:
All are form the Employees table
Employee ID Employee autonumber primary key ExpName name of employee, includes first and last name On Call Yes/No checkbox used for if a employee is on call or not
I always have a problem getting this to work without a lot of effort and I know it should be easier than I make it out to be. Problem is say I have a bunch of records I need to summarize by month. Say Dec 05, Jan 06, Feb 06, etc. When I bring down the date col, I do something like:
Format([date],"mmm - yy")
And do a totals query with a second col that returns say a count for each period. This works fine except that when I sort I get Dec 05, Feb 06, Jan 06, etc.
How can I do this with out having to create a third col to do the sorting stuff? Should I somehow wait until the data gets to the report to do the date format stuff???
I am using the following code to get results from next month,
Code:
Month([ptdate])=Month(Now())+1
which works great to get next months results. I try to get what is left of this month by adding the code:
Code: Between Now() And Month([ptdate])=Month(Now())+1
This actually gives me results from July and August, eventhough it is Sept.Basically I want the results from the remainder of this month until the end of next month.
Using Access 2010. Have been using a simple query to count similar items. All of a sudden I cannot get the sum of the count. I don't know if I have done something wrong or my program won't work correctly.
The DB is attached. The only query shown is an example of what I want to total, but I cannot get any total.
