Hello, My first attempt at a Crosstab query and I need some assistance with the output. The expression I use is below and it returns the correct infornmation by month, but i need the year to be included as well as the data goes back several years.
I would like it to calculate so that i get Jun-03, July-03 etc but i only get the option to choose one field type ie Year Month etc
I have two tables in my database. The first is a list of email addresses and the second is a much smaller list of email addresses I would like to remove from the first table. Is there a query or SQL statement that I can use to accomplish this task. Thank you from grateful Kenny.
First time poster, so I hope someone shows me the light and makes your forums shine! :)
I have a POS system that was designed in house which uses Access. I am trying to create some queries outside of the system to retrieve some data that has been being stored. I am basically wanting to know which items have been in stock for over 6 months and have never sold.
I have a query designed to show me this data for one store at a time, but nothing that shows me if an item is so poor it has never sold at any stores. This is my single store query:
SELECT Inventory_ProductTable.Artist, Inventory_ProductTable.Title, Inventory_ProductTable.Quantity, Inventory_ProductTable.Category2 FROM Inventory_ProductTable WHERE (((Inventory_ProductTable.Quantity)>0) AND ((Inventory_ProductTable.LastBuyDate)<Date()-"180") AND ((Inventory_ProductTable.masterStoreID)="CDT001") AND ((Inventory_ProductTable.DepartmentID)=1) AND ((Inventory_ProductTable.LastSaleDate)=""));
It is pulling the artist, title and category of the item out of the database to display the informtion that I am needing (for the most part). Quantity of 0 and last sale date "" basically means there is no record of it being sold, so this gives me my zero quantity sold criteria for anthing that has been in stock for 6 months or more (the last buy date criteria).
I am not sure what other info I need to provide, but I would be happy to do so. I am a n00b at this and really love to learn, so lay it on me!
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 created a query based on two akwardly designed tables that ultimately relate a member of Congress to a specific Committee and Subcommittee. Unfortunately I cannot alter the tables, as I receive weekly updates from an external source.
This being the case, once I have created the query, a "-" appears in the Subcommittee column, where the Member of Congress serves on the Committee, but no Subcommittee or the Committee does not have a Subcommittee. Although I could exclude the "-", however when I do this certain committees are removed from the final results. So I attempted creating an expression, where "<Not Assigned to a SubCommittee>" appears if the Committee does not have a SubCommittee. Unfortunately there are a few committees that fall under this. I created the following expression:
=IIf([HMC_Name]=("Committee on Budget""Committee on Standards of Official Conduct""Committee on House Administration"),"<No Subcommittee Assignment>",[HSC_Name])
Yet this expression returns #Error. Any suggestions?
Quote: "Find the total price (SUM) of all stock items in the database (use total query and find the SUM of the [price of stock]*[quantity on hand]"
Ive tried several times to do this, each time unsucessfully because im unsure how to go about it. i can get the sum of those two things, but i cant multiply the two.
SELECT clvpertech.clvtech1 AS Technician, Sum(clvpertech.CLVCode) AS CLVs FROM clvpertech GROUP BY clvpertech.clvtech1;
This is returning a sum of all the procedures a technician does for the entire database. This is working.
I need to break this down by day, week , month, and year base off of the datecomp1 or datecomp2 or datecomp3 or datecomp4 fields having a date in them.
I made this query to get the daily results and it works SELECT clvpertech2.clvtech1 AS Technician, Sum(clvpertech2.CLVCode) AS CLVs FROM clvpertech2 WHERE (((clvpertech2.datecomp1)=Date$())) OR (((clvpertech2.datecomp2)=Date$())) OR (((clvpertech2.datecomp3)=Date$())) OR (((clvpertech2.datecomp4)=Date$())) GROUP BY clvpertech2.clvtech1;
How can I add different queries to get the results by breaking down the date field for year, week, and month?
I tried this for the month but I get Invalid Procedure Call when I try to run it???
SELECT clvpertech2.clvtech1 AS Technician, Sum(clvpertech2.CLVCode) AS CLVs FROM clvpertech2 WHERE (((clvpertech2.datecomp1)=DatePart("mmm",[datecomp1]))) OR (((clvpertech2.datecomp2)=DatePart("mmm",[datecomp2]))) OR (((clvpertech2.datecomp3)=DatePart("mmm",[datecomp3]))) OR (((clvpertech2.datecomp4)=DatePart("mmm",[datecomp4]))) GROUP BY clvpertech2.clvtech1;
I am looking for assistance creating a query on a date field that will go back three business days from the current date. It must not show inclusive days, as in yesterday and the day before. Just the 3rd day past and not counting weekends. Also holidays would need to be considered. Thanks for any assistance RW
Hi all, I am utterly unsure if what I want to do is even possible:
I have two crosstab queries, qryRewCOCredit and qryWrapCOCredit which show the changeover (CO) times for the specified machine when they are NOT zero. (all zero entries don't show up).
There are many cases when there is a CO for the Rewinder on a specific day, but not for the Wrapper, and vice versa.
I want to make another crosstab query which performs a calculation. To keep it simple:
If (RewCOCredit>WrapCOCredit) Then 5-RewCOCredit Else 'WrapCOCredit>RewCOCredit 5-WrapCOCredit
i have a quick question... I am having a slight problem with a query that i am using in my database... i am trying to calculate miles per gallon. The query i have set up looks at the previous records odometer value and subtracts the current odometer value to get the miles driven since the previous fill... Here is my sql code.
SELECT fuel_use_tbl.unit_ID, fuel_use_tbl.fuel_use_ID, fuel_use_tbl.fuel_date, fuel_use_tbl.gallons, fuel_use_tbl.odometer, (SELECT TOP 1 Dupe.odometer FROM fuel_use_tbl AS Dupe WHERE Dupe.unit_ID = fuel_use_tbl.unit_ID AND Dupe.fuel_date < fuel_use_tbl.fuel_date ORDER BY Dupe.fuel_date DESC, Dupe.fuel_use_ID) AS PriorValue, [odometer]-[PriorValue] AS [Miles Driven], [Miles Driven]/[gallons] AS Expr1 FROM fuel_use_tbl;
The problem with this code is this... lets say i drive a brand new vehicle (its first record and it has no prior odometer value... the PriorValue reading shows up as nothing (NULL) when i then try to take [odometer]-[PriorValue] it shows up as nothing (NULL) while it should just treat it as [odometer]-0... i need help so that it shows an average mpg for every fill... Thanks for your help.
For a better example this is exactly what it looks like
Fuel Use ID is my primary Key and it is autoindexed... ive just been messing around with fake data and have changed the dates... thats why it looks a little strange
:confused: I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made. There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE SELECT qryTest2.CID FROM qryTest2 GROUP BY qryTest2.CID PIVOT qryTest2.TYPE;
----------- qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#] GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In")) ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.
I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .
My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.
Hello All, Your Help Required. I have send you a Database, in which I have used Crosstab query, I just want when I select the report from switchboard, and enter the datefrom / dateto (fields names) than click the preview report. Required report is open.
I have faced following problems 1-When I have selected the report and click the preview button. Report is not opened. 2-I have used cross tab query and link with the switchboard. But when I have run the query this msg is appeared “Microsoft Jet engine Does not recognized these field(name)”
I am am wondering if it is possible to create a crosstab query that displays alphanumeric values and not numeric (computational) values.
Ex: Table contains the following details:
Name Branch Bob 111 Bob 222 Joe 333 Pam 444
I want to use a crosstab so I can view the data as follows:
Name Branch1 Branch2 Bob 111 222 Joe 333 Pam 444
Is this possible? I've been playing around with it and it doesnt look doable. Perhaps there is a better way of getting the data into this format? Any suggestions would be greatly appreciated!
I'm trying to create a crosstab query with criteria that refers to a combo box on a form to allow me to filter data before the query is created. However when I refer to the combo box (e.g. = forms!frmSite!CmbSiteName) I am told that access does not recognise this as a 'valid field name or expression'. With a normal select query I dont have the same problem. Is there a way around this?
Admittedly this is my first time doing a crosstab query, but I have both my column and rowe headers set to group by, I kinda assumed this would group them in much the same way a pivot table does in excel
Any help on this would be great
Hmm, doesnt want to keep the spacing in it :( hopefully you can get the idea
I have a report based on a crosstab query and the data changes every time the report is run even though the data is not updated. Can anyone shed some light?
I need help with a crosstab query ( :eek: ). I would like the column headings to be the last 6 months, the row headings to be billers, and the data in the middle to be both the date that a payment was made (falling within the month headings) and also the amount paid in that payment. Sometimes there might be more than one payment to a biller in a month or there might be no payments to that biller in a month. All of the raw data needed is in one table. I have read about crosstab queries, tried to use the wizard, and looked at examples but I can't get it to work:confused: . Could someone show me how this should be written? :)
I need a query which displays data as a crosstab query would but gives me the capability to edit data in the query. Basicaly, I have a Resource column, a Month column, and an Allocation column (among others). I need the months to appear in columns and the allocations to be summed by month. (Months are never repeated though, so it's not really a sum...) Can anyone help me out on this?
My crosstab query doesn't take the textbox value from the form. It says it doesnt recognize [forms]![frmMain]![txtEndDate] as a valid field name or expression. How can I solve this?
TRANSFORM Sum(tblDowntimeHrs.downtimeHrs) AS downtimeHrsOfSum SELECT tblEquipmentType.Equipment_Type FROM tblEquipmentType INNER JOIN (tblCategory INNER JOIN tblDowntimeHrs ON tblCategory.category = tblDowntimeHrs.category) ON tblEquipmentType.type = tblDowntimeHrs.type WHERE (((tblDowntimeHrs.date)>=[Forms]![frmMain]![txtStartDate] And (tblDowntimeHrs.date)<=[forms]![frmMain]![txtEndDate])) GROUP BY tblEquipmentType.Equipment_Type PIVOT tblCategory.category_description;
in my select query with the same table, it works fine:
SELECT tblEquipmentType.Equipment_Type, tblCategory.category_description, Sum(tblDowntimeHrs.downtimeHrs) AS downtimeHrsOfSum FROM tblEquipmentType INNER JOIN (tblCategory INNER JOIN tblDowntimeHrs ON tblCategory.category = tblDowntimeHrs.category) ON tblEquipmentType.type = tblDowntimeHrs.type WHERE (((tblDowntimeHrs.date)>=[Forms]![frmMain]![txtStartDate] And (tblDowntimeHrs.date)<=[forms]![frmMain]![txtEndDate])) GROUP BY tblEquipmentType.Equipment_Type, tblCategory.category_description;
TRANSFORM Sum(VoosAeronTbl.ATE) AS SomaDeATE SELECT AeronaveTbl.Registo FROM MissaoTbl INNER JOIN (AeronaveTbl INNER JOIN VoosAeronTbl ON AeronaveTbl.MatriculaID = VoosAeronTbl.MatriculaID) ON MissaoTbl.MissaoID = VoosAeronTbl.MissaoID GROUP BY AeronaveTbl.Registo PIVOT MissaoTbl.MISSAO;
when I try to select between dates set on a form field like this...
TRANSFORM Sum(VoosAeronTbl.ATE) AS SomaDeATE SELECT AeronaveTbl.Registo FROM MissaoTbl INNER JOIN (AeronaveTbl INNER JOIN VoosAeronTbl ON AeronaveTbl.MatriculaID = VoosAeronTbl.MatriculaID) ON MissaoTbl.MissaoID = VoosAeronTbl.MissaoID WHERE (((VoosAeronTbl.Data) Between [forms]![ContAnFrm]![DataInicio] And [forms]![ContAnFrm]![DataFim])) GROUP BY AeronaveTbl.Registo PIVOT MissaoTbl.MISSAO;
...I get the follow error
microsoft jet database motor does not recognize [forms]![ContAnFrm]![DataInicio] as a field name or valid expression
Hi all, I am new here, I am never one to ask for help, but I am desperate to get some assistance and hoping someone may be able to help me. I have just basic knowledge of MS Access, but no where near the skills needed to acheive what I am needing. To explain, I am involved with a national memorial of all the Truck drivers who have been killed on our roads. I have a database in access, (near 1000 individuals and sadly growing) but wanting to create it into a form style. I also need to add a photograph of each deceased person and a second photograph of the plaque which they are on. Years ago I saw a form which I was impressed with, it looked so professional but never had the oportunity to investigate or disect it :)
I am hoping sks who will enjoy the challenge may be able to assist me.
more about the memorial can be seen at www.tarcuttamemorial.com Thanks Dianne
please note that I am trying to create a database regarding the inbound shipment management of a warehouse.I have the following tables that I need to link: SUPPLIERS ORDERS PRODUCTS INVOICES CASES
Every supplier has many orders.One order has one or many Products.So far is easy beacause I link them with one to many relationship.The problem is that one case has one or many invoices and each invoice has one or many orders.
So there is a conflict because the primary key of orders should be linked twice.
I need some assistance with how to use the Nz function. If you could please help me rewrite part of my SQL (shown below), I would appreciate it. I am a newbie and don't understand some of the programming syntax.
Background ----------- I have two columns, RevisedRequirement and TotalFCbyS in which I need to do some calculations and insert two new columns named Variance and VariancePct.
Issue ----- Both the RevisedRequirement and the TotalFCbyS fields can have nulls. I need to convert any instance of a null to a 0 so that I can perform the calculations and not get any error messages.
Current SQL Excerpt ------------------- This creates the Variance column: IIf(IsNull([qryProgramFundingAllocation].[RevisedRequirement])=True Or [qryProgramFundingAllocation].[RevisedRequirement]=0,0,([qryOpPlanForecast].[TotalFCbyS]-[qryProgramFundingAllocation].[RevisedRequirement])) AS Variance
This creates the VariancePct column: IIf(IsNull([qryProgramFundingAllocation].[RevisedRequirement])=True Or [qryProgramFundingAllocation].[RevisedRequirement]=0,0,([Variance]/[qryProgramFundingAllocation].[RevisedRequirement])) AS VariancePct
The SQL shown, if I understand correctly, treats the Null as if it were a zero, but does not replace the null with a zero. Again, please help me with how to re-write the SQL so that it converts the null to zero.
I created a form using a text boxes by using the field list icon and dragging the field I wanted to make into the text box onto the form. In the properties of that text box, under the format tab I set the format for "short date". Under the data tab in the properties of the text box I put in the control source this forumla =DateSerial(Year([Date of Service]),Month([Date of Service])+10,Day([Date of Service])). "Date of Service" on my form is the name of the source field it calculates from. What the forumla does is calculates from the date entered into my Service Date field and whatever date is in that field it adds 10 months to it. This field with this formula in it caluclates the date of my contacting the customer to setup an annual service for them. I also have another field that calculates the annual service date using the same formula only changing the +10 to +12.
When the Date of Service it entered the Contact Date field automatically adds 10 months to the date entered into the Date of Service field. Same of the Date of Next Service field only instead of 10 months it adds 12. So my formulas are correct. However when I go to my table I can not see any of the caluculated dates entered into the table. I can see it in the datasheet view of my form. My question is how do I create a query to pull only contacts dates from this form for a specific time frame? When I attempt to create a query I can only see table fields which do not contain the formulas for calculating the dates. Or can the fields that calculate the dates on my form be linked so the calculated dates are linked into the table and updated on the table when I update the form?
Keep in mind I am new to access so please give very detailed and specific instructions on a solution for this issue.
