Access 2007 query that has a specific date range between #6/1/12014# and #7/1/2014# returns the date I need. How do I now get it to pull just occurrences on Fri Sat and Sun of the month of June?
View RepliesI am trying to create a query with a parameter on it, but on a date box, but only searching for a specific month, without regards to the year. So what I want is for me to run the query, a pop up box asks "which month?", I then put in Jan for example, and the results will show all records with the month Jan regardless of the day or year.
Is this possible? If so - whats the code I would use?
I am trying to create a mailing list of patients. Let's say I am creating a mailing list for February. I need the mailing list to consist of people who have had surgery in February from the beginning of the database, and people who have had surgery three months ago, so anyone who had surgery in November. I have created a form that has a button which is connected to a query, the form has a unbound textbox where I can enter the month in (2 for February). Then the query uses the datepart function to search for this month in their date of surgery. But this only gives me people for surgeries with february, how would I get people who have had surgery three months ago in the same query.
View 2 Replies View RelatedIs it possible to have a query that uses criteria to pull data from a specific table?
For example: IIf([Result]'"Negative",(tblNegative goes here),IIf([Result]="Positive",(tblPositive goes here].
The tables are just text, but the query would be too long if it was used.
i need domain aggregate functions to count the occurrences of specific value in a field, and when it exceeds 1, display null. or 'group by' subqueries with count() to see if you should populate the field or not.
View 3 Replies View RelatedI 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 have a query which I'd like to create which would be to retrieve records from a specific month of a specific year, in this format "01/14" "02/13" etc..
Obviously it would need a prompt box for the query for the user to input the month and year.
When attempting to print a report in Access which was setup to print to a specific printer I am getting the following message.
This document was previously formatted for [printer name] which is currently not available. Do you want to use the default printer [printer name]
I don't understand because the printer and port name it is referencing is available and matches perfectly! I've tried removing the printer and deleting the port and re-adding it with no success.
I have an expression that sets an AI Buy It Now value that is 125% of AI Value and rounds it, which follows AI Buy It Now: Round(([AI Value]*1.25)/10)*10
I need to add to this expression that I only want this calculation applied if AI Values are greater than 999. But I can't figure out how to add this stipulation to the above expression. I have tried IIF but it is not working.
I have a basic query off a currency table :
Quote:
SELECT tblCurrencies.CcyID, tblCurrencies.Ccy
FROM tblCurrencies
ORDER BY tblCurrencies.Ccy;
Now - I have a separate table of balances, which is linked to the currency table by the same CcyID, and which also has identifiers to link it to other tables (e.g. AccountID)
As part of the above query, I want to return the count of unique AccountID's in the balance table for each currency. So in other words, I want to know, for each currency, how many unique accounts exist?
Each AccountID could appear one or more times in the balance table (one-to-many relationship), so I only want to count the number of unique ID's.
So I started with the following :
Quote:
SELECT tblCurrencies.CcyID, tblCurrencies.Ccy, Count(tblBalances.AccountID) AS NoOfAccounts
FROM tblCurrencies INNER JOIN tblBalances ON tblCurrencies.CcyID = tblBalances.CcyID
GROUP BY tblCurrencies.CcyID, tblCurrencies.Ccy
ORDER BY tblCurrencies.Ccy;
But this just gives the number of AccountID's per currency (regardless of duplication within them)
I found this article which informs me that a Count(Distinct) query won't work in Access and to use subqueries instead.
I am currently creating an Access 2007 database for calculating salesperson commissions. I have a table with 5 fields I'm working with: SalesRep, SOWRep, TerritoryRep, Period and Commission. I am trying to build a query that will calculate commission for a salesperson for each record where their name appears in one of the first three fields. Each time their name appears, they get the commission listed in the Commission field for the stated Period. I have managed to do this part successfully. My problem now is that I am creating a query that will sum all of their commissions by Period. I have run into a situation where sometimes a salesperson will not appear in one of the rep fields, resulting in NULL values in the previous queries. How can I create a final query that will sum correctly even if there are NULL values returned on the previous queries?
Here is what I have so far:
November: Avg((SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin1] WHERE [Period] Like "November*")+(SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin2] WHERE [Period] Like "November*")+(SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin3] WHERE [Period] Like "November*"))
I have been trying to import an excel sheet (a specific excel sheet in the workbook) using this method but I get an error:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Customer", "C:Download.xlsx", True, "CustomerFormatted"
where "CustomerFormatted" is the sheet I am trying to import in to the "Customer" table.
The error I get is Run Time Error 3011, The MS Access engine could not find the object "CustomerFormatted" make sure its name is spelled correctly ....
Is there any other way I can import an excel sheet in to an access table?
How do i archive Ms Access 2007 table records with specific date?
Any easy way to do it without writing any macros?
I basically have General Date field (e.g. 10/1/2014 6:34:11 PM) and I want to limit the results to only a specific month and only to show reuslts after 6PM. I tried everything and still stuck.
View 2 Replies View RelatedI 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.
How do you put the Month on a form based on the data in a query?
View 1 Replies View Relatedis their a way to have a query to only show data on every monday in current month.
Month([datefield])=Month(Now()) And DateAdd("d",7,[datefield])
I want to export a query into a specific worksheet in Excel. Have tried DoCmd.TransferSpreadsheet acExport, but it appears you can't specify an existing worksheet or cell range with a query. Some have said tables only. I want to assign this export task to a command button.
The variables are:
Query name = TrainingDataQ
Excel workbook path & name = C:UsersmeDesktop2015AccessExportTest.xlsm
Desired Excel worksheet = RawData
I think thats all you will need to know. The data in the query varies but would be no more than about 500 rows.
Also for the next time i want to run the export, some code to open that same excel file and delete the data in the RawData worksheet so that i can export new data from Access?
I've looked and looked and am afraid that I didn't find this because it's not possible...
The forms in my db are not the clearest, but one way of simplifying it for the users is to have them click on the calendar icon and then have a few fields automatically populate with the selected date's month, another with the year, and another with the day of the week.
Is this possible? I figured out how to have a field populated with the date, but with all of the specific queries needed, I have to do it this way...
Any thoughts? Also, is it at all possible (this is totally separate) to have an icon on the desktop that brings up a login sheet for which the entry users only see the front end, but the db admins log in and see the back end?
Thanks in advance!!
E
I have a Table A that is a list of ID's. In table B, each ID (from A) could appear multiple times, or not at all.
How do I get a count of how many times each ID in Table A appears in Table B? ie. 0 or 1 or 5 or whatever.
I have two tables: Table #1) [Request Table] Customers request our company to stock a certain Part Number with a minimum value. Ajax - P/N "NP4i" - Min "30".
Table #2) [Sales History] All sales purchased and the "Qty-Total" purchased. For simplicity Fields are limited.I created a CrossTab Query that displays from [Request Table] P/N's requested/ min values (Row Headings) then showing P/N's purchased [Sales History] Value="Qty-Total" Sum and
Column=Format([Invoice Date],"mmm/yyyy") group by, showing last twelve months.
Ex: Customer name, Part Number, Minimum, May/2013, Apr/2013, Mar/2013 and so on.
AJAX, NP4i, 30 4 21 5 and so on.
The Crosstab query list only requested Part Numbers that have at least One sale wihin the last twelve months.If there are NO sales, the crosstab query will NOT show this Part Number.My boss wants to see the requested Part Numbers that were never purchased along with the ones purchased.He wants to address the NO sales, yet they requested we stock that particular Part Number for them.
Is there any criteria I can use to force the Part Number field on the [Request Table] side to populate even though there were NO sales within the last twelve months?
I have a form that is used to book a new event for a client who is already in the database.Within that form I have a subform that is based on a query which displays information from that client's previous event. I did this using a solution found in the following thread: URL...It is based on pulling the second to last record that is related to the current client. It works perfectly when booking a new event that has taken place on a previous date. However, if the previous event occured on that same date (but at a previous time), it doesn't register.
I would prefer a query that would pull the record previous to the current one, instead of pulling the second to last record out of all that client's events.it would also mean that if more events are booked, then a past event is opened in a form, the sub forms in that form will display the event just prior to the current record.
I have a query built that gives me info per employee. I built a question into the criteria that ask for the employees name. When a person types the name results are given. If the person mispells the name, results are not given.
I have everyones name on a seperate table. How do i create a pull down menu on the query to choose the names?
I have one table let's call is table1. It contains about 5 columns with standard employee data with about 10,000 rows of data containing about 1,150 employees. All employees have multiple rows of data. There is one column titled JOB. I need to pull all rows of data for each employee ONLY if there is at the minimum one value for the employee in the JOB column. I do not want to pull employees that have no values in the JOB column. They can be excluded from the query.
Example of all record for one employee. I need to pull all rows of data ONLY if the employee has a value in the JOB column.
Name EE_ID JOB CAT EFF_DATE
John Doe 1006 CLERK F 01-JAN-2010
John Doe 1006 P 21-JAN-2010
John Doe 1006 CLERK P 01-FEB-2011
John Doe 1006 F 01-MAR-2011
John Doe 1006 P 01-APR-2011
John Doe 1006 CLERK F 01-JUL-2012
I have 9 seperate tables - each of the tables has similar headers
Claim #
Agent
Pass/Fail
Request type
Record Date
I have built relationships between all the tables. I am attempting have a singular query be run based on start and end dates under "record date" . I have the criteria already set. But when I run the Query no information is pulled. How do I get the query to pull this data from all 9 tables?
I'm trying to create ONE query which would fetch me the number of occurences on a particular value in a field while keeping duplicates.
For instance let's say i've got the following table
Color......Value
Red.........5
Red.........8
Orange....1
Then i would like to have the following result from my query
Color.........Value.......CountOfColor
Red............5..............2
Red............8..............2
Orange.......1..............1
I know i could achieve this by creating a view first. For instance by saving this expression as qryCountOfColors
Code:
SELECT tblColors.Color, Count(tblColors.Color) AS CountOfColor
FROM tblColors
GROUP BY tblColors.Color;
And then make the following query
Code:
SELECT tblColors.Color, tblColors.Value, qryCountOfColors.CountOfColor
FROM tblColors INNER JOIN qryCountOfColors ON tblColors.Color = qryCountOfColors.Color
However I'm woundering wheter or not I could achieve it without creating the qryCountOfColors.I'm myself into a mental meltdown . I've been playing with the idea of creating of creating a subquery to achieve this but im unable to get to work
Code:
SELECT tblColors.Color As BANANA, tblColors.Value, (SELECT Count(tblColors.Color) AS CountOfColor FROM tblColors GROUP BY tblColors.Color HAVING (((tblColors.Color)=BANANA))) AS Expr1
FROM tblColors;