I have a select query from a few related tables, one relationship being many-to-many. The resulting query, when run would have several groups of records where each group has all but one field different (a text field). Is it possible to do a report where this group appears as one record with the results of that field as a list?
View RepliesHey,
I have a table, which is full of information, but could be broke down into something like the following:
Dan - Job 1
Dave - Job 1
Karl - Job 1
Steve - Job 2
I want a form to appear with the different job's on, and then from there I select which job I want to view the details for.
So for example, in the list it would say Job 1 or Job 2. If I click Job 1, it would bring up a form showing details for Dan, followed by Dave and then Karl. If I click on Job 2, it would just bring up Steve's details.
When I try and do the query, it lists all of the Jobs (Job 1, Job 1, Job 1, Job 2), where I just want it to group all of one kind together.
Is this possible?
Thanks
Dan
I created a query and everything is OK, but i wanted to group the result, here is the code
Code:
SELECT Equipment.Category, ([Equipment]![weekday_rate]*[Rent]![weekdays_num])+([Rent]![weekend_num]*[Equipment]![weekdend_rate])+(([Equipment]![weekday_rate]*[Rent]![weekdays_num])+([Rent]![weekend_num]*[Equipment]![weekdend_rate])*0.2) AS Income
FROM Equipment INNER JOIN Rent ON Equipment.Equip_ID=Rent.Equip_ID;
I want the total for all equipment which belongs to the same category
I need a subgrouping for a report I am working on.
I have a DateIn field that contains date as mm/dd/yy. I have managed to get a count of all of a certain type of document that came in during a certain period of time that usally spans a quarterly period. So typically there are three months in the query. I would like a subgroup that breaks the numbers up in the 3 month period by each month.
So if a total of 150 docs moved in 3 months it shows 50 moved the first month, 50 the second, etc. I believe this is grouping by month. I have not been able to determine the criteria for getting the query to group by a single month as well as the three months period being grouped by document type. Here is what I have so far.
SELECT ctblDocuments.DocTypeID, tlkpDocTypeList.DocType, Count(ctblDocuments.DateIn) AS CountOfDateIn
FROM ctblDocuments INNER JOIN tlkpDocTypeList ON ctblDocuments.DocTypeID = tlkpDocTypeList.DocTypeID
WHERE (((ctblDocuments.DateIn) Between #7/1/2005# And #9/30/2005#))
GROUP BY ctblDocuments.DocTypeID, tlkpDocTypeList.DocType
ORDER BY tlkpDocTypeList.DocType;
How can I group records based on values within a particular field? For example, if I have records of people with different annual incomes, what is the easiest way to allocate the records to income bands? I assume the starting point will be to create a band classification in Excel and import as a table. How do I subsequently run a query to achieve the desired effect?
View 1 Replies View RelatedHi,
I am trying to display the number of issues logged and closed for each particular month. My SQL query works, but the number of closed issues is where I get stuck. I can only see a way of displaying the number of closed issues per month by specifying the exact month number in the query (Loop?)
SELECT Year(bg_reported_date) AS [Year Reported], Month(bg_reported_date) AS [Month Reported], Count(dbo_bugs.bg_id) AS [Number of Issues Logged], (SELECT count(bg_last_updated_date)
FROM dbo_bugs where Year(bg_last_updated_date) = 2006 and month(bg_last_updated_date) = 1 and bg_status =5 and bg_project = 12 ) AS Closed
FROM dbo_bugs
WHERE (((dbo_bugs.bg_reported_date) Between #1/1/2006# And #12/31/2006#) AND ((dbo_bugs.bg_project)=12))
GROUP BY Year(bg_reported_date), Month(bg_reported_date)
ORDER BY Year(bg_reported_date), Month(bg_reported_date);
I have a table that has 2 colums.
Room_Number Items
1 Chair
2 Chair
2 Desk
3 Table
4 Computer
4 Radio
4 Book
4 Chair
6 Fish Tank
6 Chair
I want a query that will select rooms 1-6 and include all items for those rooms on one row. Example below
Room_Number Items
1 Chair
2 Chair, Desk
3 Table
4 Computer, Radio, Book, Chair
6 Fish Tank, Chair
I hope this makes sense. Thank you in advance.
Is it possible to group and summarize fields while in a query instead of doing it in the report section? Please find the query attached.
For each day, I need to group the "Expr1" field (shifts employees worked) and "Date" field and to summarize their corresponding "Cases" and "LBS" values.
This is how I need to see it (actual groups and totals):
01-Apr-086136121317.78SHIFT1
01-Apr-086935131899.94SHIFT2
01-Apr-087169144949.29SHIFT3
02-Apr-088229169379.26SHIFT1
02-Apr-089259202454.86SHIFT2
02-Apr-086754141018.96SHIFT3
Any help is highly appreciated!
Thank you,
Peter
I have Table as PL2008 and RPT2. Both tables have one column named PTUNID. My goal is to first Group, sum certain Columns in the PL2008 then match PTUNID from this Grouping to RPT2 Table's column PTUNID and then show the results which do not match.
I have written following Qry but what really happenning is its giving me all the results ( match and no match in both tables ) and Sum values are incorrect.
SELECT [PL2008].PTUNID, Sum([PL2008].[Total Hours]) AS [SumOfTotal Hours], Sum([PL2008].[Actual Cost]) AS [SumOfActual Cost], [PL2008].[PRFIELD]
FROM RPT2, [PL2008]
WHERE RPT2.PTUNID <> [PL2008].PTUNID
GROUP BY [PL2008].PTUNID, [PL2008].[PRFIELD];
Any suggesstion
Thanks
Niri
This is probably an easy question for you all:
I want to run a query that does a GroupBy with DATE and Counts the number of records associated with that DATE.
I can do this, but I also want the dates to group together by Month so I can count the number of records per month for multiple months in the same query. How do I do this?
Thanks very much
I'm losing my mind on this one. Here is my situation... I receive a daily excel sheet with these fields. I would like to import this workfile into access and would like to manipulate it anyway I want. The problem I'm coming into is that I can't collapse/group the datetime field into just the date when I run a query. I would like to be able to run a query on any date range the client registered, a query on any and all the unique dates the client purchased something, all the unique clients, etc. Here is a sample
Sales Name - Sales # - Client Name - Client # - Registered Dt - Purchased Dt
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/4/2007 10:21
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/5/2007 18:20
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/6/2007 10:21
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/6/2007 13:28
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/6/2007 13:28
Paul, John - 273 - Kelly, Maria - 2252 - 8/7/2007 01:22 - 8/9/2007 05:46
I would like it to show...
Sales Name - Sales # - Client Name - Client # - Registered Dt - Purchased Dt
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/4/2007
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/5/2007
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/6/2007
Paul, John - 273 - Kelly, Maria - 2252 - 8/7/2007 - 8/9/2007
and a different query like ( the purchase date doesn't matter here)...
Sales Name - Sales # - Client Name - Client # - Registered Dt - Purchased Dt
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/4/2007
Paul, John - 273 - Kelly, Maria - 2252 - 8/7/2007 - 8/9/2007
I read in other places to change the format, input mask, convert, char, etc. but nothing. Please tell me exactly what to do. I don't care if the time changes to midnight or 0:00:00. I will owe you my first born child.
I have a number field called Jobs.SquareFootage, and I want to run a query that categorizes the field into intervals. The intervals I want are:
0 to 50 squarefeet
51 to 300 squarefeet
300+ squarefeet
This is the query I'm coming up with so far
JobID SquareFootage Revenue
1 40 $5
2 30 $10
3 200 $400
4 250 $350
5 550 $600
I want to put the SquareFootage into the intervals I mentioned above, and get the total revenue for each interval.
I have a query that gathers information from the tables in my database and returns something like this:
Code:
Field Pest
1 Insect1
1 Insect2
1 Insect3
2 Insect1
2 Insect3
2 Insect4
Field and Pest are fields in two separate (but joined) tables. Field exists only once in its table, and multiple pests are attributed to each field. I'm looking to create a table or query in which the values would be listed as follows:
Code:
Field Pest
1 Insect1, Insect2, Insect3
2 Insect1, Insect3, Insect4
The purpose for this is to create a legend for a map used by another program based on the information gathered in the database. I do have other criteria for which fields and pests show up in this query.
I'm trying to construct an SQL crosstab query that will output data like the picture I've attached in the .zip file.
The four variables from the data table would be [Client Accounting].[Marketer] (the left vertical column), [Client Accounting].[Closing Date] (the higher level horizontal column grouped by month), [Client Accounting].[Write Off] and [Client Accounting].[Refund] (the lower level horizontal columns as sums)
The totals column at the bottom and the two vertical columns at the right would be made in the report and wouldn't need to be in the query.
This is what I have so far but I don't know how to add a second TRANSFORM statement to be included and grouped by month!
Code:
TRANSFORM Sum([Client Accounting].[Refund]) AS SumOfRefund
SELECT [Client Accounting].[Marketer]
FROM [Client Accounting]
GROUP BY [Client Accounting].[Marketer]
PIVOT Format([Closing Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items.
How can i make 3rd query which will give me all but taken items from query1?
(of course items from query 1 are in query2)
thx in advance
I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
Here's my problem. I need to generate a report that says how much of each individual product was produced and as well as the total produced for a specified category in a time period. Something like the following:
05 Catagory A: 02 Product AA, 01 Product AB, 02 Product AC
10 Category B: 07 Product BA, 03 Product BB
04 Category C: 01 Product CA, 01 Product CB, 01 Product CC, 01 Product CD
etc...
I currently have a query that queries a database and pulls out all products that were produced in a specified period and the categories they belong to and dump them into a local access table. Now what I need to do is search through the query results and count up how many of each product were produced (02 AA, 01 AB, etc...) and the totals for each category. The number of categories is pretty limited (6), but there are hundreds of product codes, so I need a way to do this without having to type in each induvidual product code as the requirement by which the query searches. Also, the product codes that get returned are different every day.
I was thinking something along the lines of take the product code of the first row and check for any others in the results that match and write that into another table. Then move onto row 2 and use its product code as a search parameter and search through the query results for any matches. Then continue that until the end of the query results. Can I do that? Is there a better way to achieve what I need?
I am trying to filter a form to show the entire weekend's activity on Monday but only yesterday's activity Tuesday through Friday. Using this code I can return Friday's results on Monday and yesterday's for the rest. How do I get the range Friday to Sunday?
IIf(DatePart("w",Now())=2,Date()-3,Date()-1)
Using >Date()-3 doesn't work.
Thanks
Bruce
I have a query that displays results in a form, but if the query is null, I want to display a different form, or just an error message that says something like "your query returned no results" (right now it will display the form with no fields)
I am a beginning Access/VBA user and have searched and browsed the forum for combinations of null/query/form, but haven't found what I need. Can anyone point me in the right direction?
Thanks for any help.
I am wanting my query results to appear in my main form so that i can edit them, rather than a table the query is just a look up of my form that stores about 500 records and stores all the same fields
any help would be appreciated
thanks
Is there a way to write one query that will work for different criteria and is run by different buttons to produce bar graphs or a basic report depending on the button the user clicks??
View 5 Replies View RelatedHi all :o
I am working on a query but can manage to get it to do what I want.
I have a main table with a job_ID and a faults table which documents faults that occur related to that Job_ID the tables are related with a one to many relationship. I want the query to bring up all the jobs even those with no faults, at present it is only bring up those jobs that have faults, how do I include details of all the jobs and if there are no faults then that field is left blank, probably very simple to do but after working on this db for hours my brain is no longer functioning!! :eek:
Thanks
Jackie
Hello all,
I have a table called tblODF and within that table, I have these fields:
- ODFNumber
- ODFScanDate
- Status (Combo box: Pending, Complete, Licensing)
- LastFollowup
In my query, I have the same fields, except I added a calculated field.
I added DayCount to
Codecalculate:(Date()-[ODFScanDate]
This should calculate the number we've had the ODF.
What I want to happen is, let's say the status is Maturity, I want the DayCount to say 0. If it's pending, I want it to calculate the actual days.
Is this possible?
Thanks
I've just started using Access 2007, but I've been an Access 2003 user for some years and have a couple of databases, both of which I've now brought over to 2007.
Something I've noticed and is probably very simple. In 2007, after I entered information in a table via one of my forms, I queried on that information but it wasn't included in the results. But everything else - from 2003 - was. I then went thru each field in the underlying table to "match" fields there were filled in for a 2003 record and now the new entry does appear in the query results.
This hadn't happened before in 2003 - it didn't matter if all or even particular fields were filled in. But now it needs for at least some particular fields to be filled in. Don't know if it's something different in 2007, or probably coincidental. I haven't changed anything in the table, query or entry form.
It's not a crisis (now that I see my results), but I'm wondering what is going on - now, that apparently wasn't occurring before?
Thanks!
I hope I can explain this clearly. I'd be really grateful if anyone could help.
I need to set up some sort of contacts database at work.
I need (I think, after having a little think about it) one table, filled with people, (their names, contact details ect) and basically various queries to pull up people in certain groups. Say for instance, people who attend meeting a, b, c, (in a simplised, condensed version of the truth)
I did think about doing this with various tables actually. A contacts table, a meetings table, blah blah, but anyway. (Any advice on this incidentally would be great).
My main problem though. We often have to email all the people who attend say meeting a. We have contacts on our email systems, but they're different from person to person, and as the company is updating email systems, we can't even send contacts lists to everyone, as they're not compatible.
What I'd love is to be able to call up a list of people attending meeting a from my database and email each of those people (their contact details would include email address of course)
Does that make sense?
Is there any way of doing this?
I'd be really happy if anyone knew...