I work for a company that maps schools and school information and data for a state project. I have put together a DB to enter and track all of this information. Currently, I am trying to produce a report that will group by County, then show School Districts w/in the County, and then show Schools w/in the District. On the County Group header, I would like to show the Count of Unique/Distinct School Districts w/in the County and show the Count of Schools w/in the County.
The problem I am having is that I have not been able to get the count of distinct Districts w/in the County. I am able to get the correct count for the number of Schools w/in the County and Districts. I would like the District count to show in the RED boxes on the form.
Im new to access and i have a problem!! I have a report that i need to be sent out monthly to 3 people. The problem is i dont know where to start. I have been looking through the forum, but nothing that i can get my head around, so there anyone out there who can help???????
Hi I'm in need of some help it's been many years since I've worked with Access and I'm struggling a little.
I have a database I am making that logs customer airport drop off and pick ups and I want to produce a report that merges the Drop off date and the pick up date into one column and I can't seem to get access to do this.
Currently I have 3 tables
Main Info table with the following records Booking ID(Linked Primary Key) | Customer Details |
Outgoing Table Booking ID(Linked Primary Key) | Outgoing Flight Date | Outgoing Flight Time | Outgoing Flight Number
Inbound Table Booking ID(Linked Primary Key) | Inbound Flight Date | Inbound Flight Time | Inbound Flight Number
So I want a report that will get both the outgoing flight dates and inbound flight dates and put them in one column so I can create a report that tells me what pick ups are needed on each date.
I have a crosstab query which returns values in a range of categories. I have 5 categories. My problem is that sometimes a category may not return a value in 1 or more of the categories. This means that when I am displaying this data in my report I haven't got the data in the correct columns.
In the underlying queries for this I tried using a LEFT JOIN forcing the category, but when I executed the crosstab I got errors in all fields.
I have a sub report on my form to show all data needed for a delivery note.
these fields are :
House Type Plot No Product Delivery Note No
when i use my query to search orders in my subreport - it will show the required data but will also show data i didnt search for ? why is this?
Sometimes my search results in the subreport show further down the subreport than the irrelevant data - all i would like is for it to show the specific data not the irrellevant data.
I have created a report that prints a transaction input via a form. All the data has been posted to tables while the document details are still on the form. The source for the report is a query that gets its "Document ID" from the current form as its CRITERIA. This works fine. I click a button and the report prints.
Now I've added a datasheet that lists all the "Document IDs" that have been posted within a given date range. I've added a Macro to open(reprint) the same report when any Document ID is double-clicked. This is working except that it prompts for a Parameter Value and references Forms!DocDataEntry!txtDocumentID. (Note: this is the name of the original data entry form which is no longer open)
If I manually type the Document ID (that I just double-clicked) in the parameter box, the report prints correctly. But this shouldn't be necessary.
I know I'm missing a WHERE clause on the Macro that opens the report but nothing I've input works. I can't even hard code a document number. Actually any Where clause provided prompts additional parameter boxes to open and they ALL require the SAME INFORMATION... the Document ID.
I'm thinking that the Criteria on the Document ID in the query should be changed to allow a Document ID from any active source.
I have a query where I need to find out the average score of employees over a date range. I have a field called date where I enter a date range (Between [START DATE (MM/DD/YY):] And [END DATE (MM/DD/YY):]) and another field where I enter the name of the supervisor. Now the table I have created contains the scores on a daily basis. So what I want is that when I enter the date and the name of the supervisor it should give me the average of the scores over that date range of all the employees under that supervisor along with the name of the employee.
I have two tables, a Property table and a System table. They are linked so that there are many Systems linked to one Property.
Each System has a risk score and these vary within each property. My Property table has a Property risk score field and in here i would like the maximum System risk score to go.
I'm just wondering how i would do this using a query? All the system risk scores have been entered already.
I am unsure whether this is best done in SQL or VBA, however, I do believe it is possible in SQL so I will put it here.
I have a field 'value of purchase' in which an items cost is inserted. What I am wanting to do initially is automatically deduct 20% off the value of purchase every year until it reaches zero. Now I don't want to deduct 20% off the new value every year, for example:
The price is $1000, 20% deduction is $800. But then year 2 it will be $600 then the next year $400 and so on. So I suppose the first year 20% is taken off the original price, then for year 2 40% and so on (always off the original price not the newly found values).
I want to display this in a new field 'current price'.
I am just not sure how to create the query so it will automatically deduct these amounts per year. If anybody has any advice/help it is muchly appreciated.
I have a database that was designed for our needs (not by me) and I have limited database experience. I am looking for someone who can guide me through simple query modifications to existing queries for a flat fee. Please respond if interested and the amount charged for services. Chris O'Reilly
I'm missing a bit of experience dealing with this query. It's very simple. I'm selecting log activities from COMLOG. However I only want the most recent DateStamp entry of each Cust_FK entry listed.
Because there are more logactivities, there are more/identical Cust_FK values. Would I first have to group these?? I just don't know how I would go about it.
SELECT COMLOG.LogID, COMLOG.DateStamp, COMLOG.Cust_FK, COMLOG.LogActivity FROM COMLOG;
I've searched the forums but can't seem to find anything similar. I work for social services and we have a database that stores information on children who may need services from adults when they reach 18.
What I need to do is create a query (so that a report can be run) that will show those children who will turn 18 during the year Apr 1st - Mar 31st but I'm not sure where to start. Can anyone offer any pointers please?
The fields within the database are: Client ID, Name, Surname, Date of Birth, Address
I have DB with a Switchboard that has a button for Bin Number (Part Number). It is based on a parameter query that asks you to input your Bin Number. Once you enter the Bin Number it pulls all information for that Bin Number from a table that contains the Bin Number, description, Manufacturer P/N and Manufacturer and shows this in the query.
What I need to do is have this information each time a different Bin Number is entered to be stored in specific table (datasheet style) for printing out just a list of specific Bin Numbers and their information. As the parameter query stands now, each time I input a different Bin Number it clears the previous information. Can I tie in a make table query in some manner that will store the selected Bin Number info?
Can someone make suggestions on the easiest and quickest way to set this up? Keep in mind I am still learning Access so please be detailed in your comments. Thanks in advance for any assistance.
I have four fields that typically have a quantity amount in them.
I also have a field with a date.
I would like to say something like this
If fielda = 0 and fieldb = 0 and field = 0 and fieldd = 0 and in the date field, the date is >30day from today’s date add a 1 to this field, otherwise leave it blank
I have an access database that links to some SQL Server tables. These tables hold time tickets which keep track of the time that an employee is logged into a job and records the associated product code and work code. All jobs have a work code, and some have a product code. The product coce is the most accurate way to track time, so if a product code is present I need to use that. If it's not present I use the work code.
I currently have a query called 'main' that gets all records in a specified date range and creates a 'code' column. This is a hybrid of the product code and work code using the method described above.
IIf([ProdCode],[ProdCode],[WorkCode])
Everything looks fine there, but when I go to a new query to get total hours by code ('TotalHoursPerCode') by grouping code and getting the sum of ManHour, it doesn't work.
Instead, it lumps all lines that have a product code together but then combines all lines that have no product code into a single line at the top. Instead of using the 'code' column to group.
Here's the SQL from the 'TotalHoursPerCode' query.
SELECT Main.Code, Sum(Main.ManHrs) AS SumOfManHrs FROM Main GROUP BY Main.Code;
I have data that I imported into access (Table1). Basically, the file shows two values with Header, Component. I need to group the Component together with a delimiter & (or any symbol) when the header is the same.
For example of what I have: Table1 Field 1 – Header Field 2 - Component
FG1 | SA1 FG1 | SA2 FG1 | CO1 FG2 | SA3 FG2 | CO2
What I am trying to get is Table2 Field 1 – Header Field 2 - Join Component
Let's say that I've got the following tables: - tblCustomers(IDCust,NameCust) - tblEmployees(IDEmpl,NameEmpl) - tblProjects(IDProj,IDCust,IDEmpl,Duration)
My query: SELECT tblCustomers.NameCust, tblProjects.IDProj, tblProjects.Duration, tblEmployees.NameEmpl, Sum(tblProjects.Duration) AS SumOfDuration FROM (tblProjects INNER JOIN tblEmployees ON tblProjects.IDEmpl = tblEmployees.IDEmpl) INNER JOIN tblCustomers ON tblProjects.IDCust = tblCustomers.IDCust GROUP BY tblCustomers.NameCust, tblProjects.IDProj, tblProjects.Duration, tblEmployees.NameEmpl ORDER BY tblCustomers.NameCust;
I am asked to display this, "List the Client Numbers, Name, and Balance fields in the design grid. Sort the records in desending order by the Balance Field." AND THIS: "Display the top 25%"
I need to know how to show the top "25%" when I press run, in the datasheet without manually having to arrange them.
Case 3 does not work. with " " . So what do i do here to show Both records?
My second problem is with the reports opened with the above Case code. The search routine (apart from Both) work fine. It finds the records i ask of it and it opens a report to show them, however, there are instances where no records are found, and the report still opens!!. In my search form, what code can i use to test my query for "null" data before it even gets a chance to open the report?
i am trying to update a field within a table. But i dont want to update the whole of the data within the field just one part of it.
The field is the Tutor Group for students in the format 7B, 7D, 7F, 7H, 7M, 7S, 8B, 8D, 8F,... etc where the number represents the year group! i want to use an expression that would add(+) 1 to the year group but leave the letter the same as this doesn change. By doing this the above list would change to 8B, 8D, 8F, 8H, 8M, 8S, 9B, 9D, 9F, ...etc
I attempted to use the following expression: ((*)+1)(*) but it didn't work.
I should have had the year groups separate but i am given an Excel flat file which has details of the students and doesnt separate the the two.
any help would be much appreciated! hope someone can help!
I need to retrieve average values from another table for certain chemistry values based on the [Date_Loaded] and whether or not one or two silos were used when loading the shipment (as indicated by the [2_silos] checkbox).
Scenario #1 - one silo
I need average values of the last four samples based on the [date_Loaded] and the appropriate silo.
Scenario #2 - two silos
I need average values of the last two samples from each silo based on the [date_loaded], [primary_silo], and [secondary_silo].
Would anyone have any thoughts on this? I've tried crosstab and select queries, but I can't get anything to work.
My ultimate goal is to have a form where users select the shipment and the appropriate chemistry averages automatically populate the data fields.
Hello all, I need help with making a query this will check the OrdQty for all items in a specific order against the Sup1inv field, then if supplier 1 can not fill the order have it check against Sup2inv field. Here is an example of the data im working with
The part i am having trouble with is making sure the query "understands" that both items on order 555 are the same order so the end result would tell me that order 555 needs to be placed at supplier 2, not 1.
Original Table ozip dzip shipdate wgt Transport cost
A B 12/1/07 20 450 C D 4/1/08 15 890 E F 9/1/08 78 750 A B 12/1/07 42 789 X Y 1/1/08 17 900 C D 4/1/08 34 90
Needed Output
ozip dzip shipdate wgt Transport cost
A B 12/1/07 20 450 A B 12/1/07 42 789 E F 9/1/08 78 750
I need to create a query which extracts rows with wgt >60 and also WHEN the fields ozip, dzip and shipdate are same, the query should add the wgts and then sum of wgts should be >60- for example the first and fifth rows should be extracted since sum is 62.
I have attached the output that is required for the above example. please help
Hello, need help with the Trim() function. In a query I am using this SQL:
SELECT TRIM(tblAllMembers.First_Name), tblAllMembers.Middle_Name, TRIM(tblAllMembers.Last_Name), tblAllMembers.Address1, tblAllMembers.Address2, tblAllMembers.City, tblAllMembers.StateOrProvince, Trim(tblAllMembers.Postal_Code) FROM tblAllMembers WHERE (((tblAllMembers.City) Is Not Null) AND ((tblAllMembers.StateOrProvince) Is Not Null) AND ((tblAllMembers.Country)="USA"));
However when I run the query, the First_Name, Last_Name and Postal_Code doesn't trim leading and following blank spaces.
