I am trying to display five columns from three seperate tables. I thought the GROUP BY operator would fix this, but I have more than one column to select. The query is fine but I have redundant fields showing. Oh, I'm not worried about the calculation "[employee.salary]*0.1 AS Salary_After_Raise." I just want to concentrate on narrowing the fields down first.
SELECT ID, FirstName, LastName, Salary, [employee.salary]*0.1 AS Salary_After_Raise, City
FROM worksat INNER JOIN store ON worksat.store = store.storenumber, employee
WHERE worksat.store = store.storenumber
GROUP BY city, id, firstname, lastname, salary
ORDER BY city;
I am working on a very large table that i have to group by Min & Max Dates.
ColumnA ColumnB 1-Jan Melbourne 2-Jan Melbourne 3-Jan Melbourne 4-Jan Melbourne 5-Jan Sydney 6-Jan Sydney 7-Jan Sydney 8-Jan Sydney 9-Jan Sydney 10-Jan Sydney 11-Jan Sydney 12-Jan Melbourne 13-Jan Melbourne 14-Jan Melbourne 15-Jan Melbourne
I want the above table to provide an output as below:
Min Max City 1-Jan 4-Jan Melbourne 5-Jan 11-Jan Sydney 12-Jan 15-Jan Melbourne
i have done this in excel by sorting dates and then comparing values in Column B.
I have groups that run horizontally down my report. Is there a way to group items vertically on the report based on the horizontal information.
For instance I have a client list with the total of the transactions they made. Then I would like to run in the same row the breakdown per quarter showing all 4 quarters in the same row as the client name, but separated into columns for each quarter.
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
I am trying to display five columns from three seperate tables. The query is fine but I have redundant fields showing. I thought the GROUP BY operator would fix this, but I have more than one column to select.
SELECT ID, FirstName,LastName, Salary,City FROM worksat INNER JOIN store ON worksat.store = store.storenumber, employee WHERE worksat.store=store.storenumber GROUP BY city, id, firstname, lastname, salary ORDER BY city;
I am trying to produce a report or query that lists 11 attributes for several projects measured each month over the duration of each project. i.e. It is required to have the attributes for project A listed (horizontally) for months 1,2,3,...etc (vertically) followed by those for projects B for months 1,2,3,4,5,6... etc. Can anyone advice on how to do this please?
i have 5 fields facid custid empid amount invdate.
i need to be able to get totals on the amount based on fac, cust & emp based on a date range given by user, but here's the kicker.. i need to get the current month totals, month totals - 1year (so same month but for last year) the date range totals, last yeardate range current ytd, last ytd
i did it using code, but the performance isn't that great. there are 2800 customers it has to cycle through and figured one of the resident experts can help with a query. i've used dsum() but not quite getting the results i should. i think one of the problems is the user has the option of filtering the data based on the 3 fields.
anyways if you can help i'd greatly appreciate it..
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 have three tables. Risk, Names and RiskAndNamesJunction table. I have the junction table because I have many to many relation (meaning many people can be connected to one risk and many risks can be connected to one people).
The problem is that If I make a query to show the people related to the risks, if there are many people for one risk then it will put the people in different rows. Meaning that for risk 2 I will have three rows, because there are three people connected to this rows. See the attached file!
What I would like to do is to have a query which (in case there are more than one risk owners) puts the second name in another column, the third name in another column and so on. So I will have only one row per risks.
The attached file is a dummy file, so there are only maximum three names per risk. In the real file the maximum is five names per risk. So I am talking about no more then five extra columns. (So I am talking about a query which would put the first finding in the first extra column, then the second item in the second and so on till five. It there is no third or fourth or fifht item then the columns remain blank).
Unfortunately I have to do this because our mother company works with excel and they are sticking to this format in excel.
I am trying to build a database for my extensive DVD collection and I want something that shows more than one record per line (I ultimately want something with just an image and title for each record with maybe 4 records per line). This is my first venture into using MS Access, but I've gotten the basics down and have already set up the table & form. The only time I have found something that would display the way I want things is in a report, but it will only display it with multiple columns in print preview mode (which won't work because the titles are going to be hyperlinks and need to be clickable). If anyone has any ideas, they would be greatly appreciated. If I'm not on the right track and this can be done from within a form, then please set me straight. Thanks again in advance.
I'm really hopeful that somebody can help us out here. I'll explain as best I can.
I've got a table, where details of particular online environments are kept. Each online environment is assigned to up to four people. By this, it may be assigned to nobody, or it may be assigned to four seperate people, or anywhere in between.
What i'm trying to create is a query where the user of the database can type in a particular persons name, and it will return all the details of any record where that person is mentioned in ANY of the four colums, which can then be used to generate a report for that particular individual.
However, i'm really unsure of how to do this, and am aware that there is probably a really simple solution to my quandry, that will leave me kicking myself up the rear like theres no tomorrow.
Any advice as to how to proceed would be hugely appreciated, genuinely.
I've got all the columns i require in my query and and i've averaged these, however i need to create a final column which contains the Average of all the other Column Averages.. if you get what i mean! Put it this way i have columns A, B, C. All the numbers containd within each column have been averaged so now each column contains a single value. ie.
A = 10 B = 20 C = 9
So now i have to create column D which will contain the average of those results above i.e.:
D = 13
I would preferable like to contain this within a single query rather than having to write a vb module and having to add extra tables ect.. but that might be the only way!
So if anyone could tell me the best way of achieving this it would be much appreciated. I've tried a couple things like concatenation: Average: [Score1]&[Score2]&[Score3]
I'm trying to make a payment report which prints a separate pay sheet for each employee based on what job they did. I don't want to get into too much detail here, but basically, a name is associated with each action in a setup like this:
I want to have a query that gives one column that looks like this:
Bob Fred Jeff Jim Joe
I already know how to sort and group by, but how would I write a query that contains one column, then another column when that one ends, then another, et cetera?
(I need to know this, because it would be a lot cleaner to show one report for all 5 jobs than a report for each job separately, and of course, that's how the boss wants it!)
I have a table of Cabinets. Each cabinet has 20 fields for Hardware (HW1, HW2 etc)and 20 for Hardware Quantity (HW1Qty, HW2Qty etc). Not all fields are populated.
Hardware is entered as an ID number eg "Hinge" = 620 and "Handle" = 750
I have a database that I just imported a table and I am trying to get the top 5 by desc order of my member count. The problem is some of the counts are duplicated as 1 but the other columns are not a duplicate. I am attaching an example of the Excel I am importing, but this is just an example. I have multiple product across all states and products and then there is WLP, AGP, MMP under Legacy.
Then I have the top 10 top1dx and the ranking is actually 1 to 10. The layout was pulled asking for the top10 diagnosis or dx1 and then give me all the dx2's that fall under that dx1. So I might have 100 dx2's but only need the top 5 but as I said with the count of 1 on many, if I do my query, it will give me the top 5 and chop off any 1's but then I don't get each ranking 1 through 10. I will post an example also of what I need for the output and just having a difficult time with the right query. Here is the query I used:
Code: SELECT OP.CM_ID, OP.LEGACY, OP.TOPRANK, OP.MEMBERCOUNT, OP.ADMITQTY, OP.STATE, OP.PRODUCT FROM OP WHERE ((([OP].[TOPRANK]) In (SELECT TOP 5 TR.TOPRANK FROM OP as TR where OP.TOPRANK = TR.TOPRANK ORDER BY TR.MEMBERCOUNT DESC)) AND (([IP].[MEMBERCOUNT])>1)) GROUP BY OP.CM_ID, OP.LEGACY, OP.TOPRANK, OP.MEMBERCOUNT, OP.ADMITQTY, OP.STATE, OP.PRODUCT ORDER BY OP.MEMBERCOUNT DESC;
Even if the top 5 ends up showing only 3 in the mix, that is fine. As long as I have in the end every legacy, product, state, the dx1, dx2, toprank accounted for by memebecnt desc.
I am trying to concatenate values from seperate columns. Which is fine, however if there are no values in any of these columns I wish to include a dot (.). I also need to add a dot (.) inbetween values.
I have three tables that are named different, but have the same column names, but different data in them. The data in each table is linked by a master table that has a primary key.
What I need is a way to make a form with a box where I can search on a value that could be in any of the three tables and then the associted information from the site will display in the above boxes.
(Site Master Table contains all site information and the primary key) (The three tables are sales order information. Stage Only table, Install table, and srop ship table)
Hello! I'm new here, and I'm back into Access after a few years of not using it.
What I'm trying to do seems simple, but I can't seem to get the sytax down.
I have 6 columns that have dollar figures in them. I want to get the totals for all 6 columns to show in one field. I'm using the sum([field]) to total the individual columns, but I can't seem to get all of them together in one sum. Is this possible?
I want to create a query which shows the sum of the number of all four invoice types for a specific date regardless of the Location_No. By putting something like "Invoices_total: Invoice_type_A + Invoice_type_B + Invoice_type_C + Invoice_type_D" in the top field I am able to get the query to show me the total number of invoices per date AND location, but I would like the total number of invoices for a given date regardless of location number. How can this be done?
I can't seem to find a way to SUM two seperate columns on my report. I've attached an example of what I am talking about. I was able to add the SUM feature to the 'Estimate' column, but it doesn't allow me to add that to the 'Plan' column too. The only options available are Count Records or Count Value.how I can have these both SUM seperately within their columns?
Let's say I have three columns; apples, berries and bananas. I need to capture, either using the GUI or the SQL if any of those are not null, then bring that row back. So the rows that come back from the query are only ones where one of those three columns is not null.
In excel it would be if(or(apples<>"",berries<>"",bananas<>""),X,Y)
