Hello all:
Every week I download new information into a table. The download has all the information, but the table will spill out any information from the download that is already existing in the table.
How do I print a query or report right after the download, that will provide a list of the new information just downloaded.
Thanks for all your help!
I am trying to create a report that will show me any record that does NOT have a specific field met. I have a basic query set up to display the information desired, but need it to refine the search to just the records missing a specific part in a different table. The missing information does not have to be shown in the report.
View 7 Replies View RelatedI have a form with a list box that shows a number of reports that are ultimatly built from queries.
Depending on what information I have put in, some of the reports will not show any information, which is fine. Is there a why that I can tell the report to open only if there is information present or maybe change the colour of the name of the report in the list box.
This sounds a bit complicated but hopefully I have explained enough for someone to help, thanks in advance.
I have a report I am generating with the design wizard. It uses 1 Query and all the information I want and order is correct when its finished. However, there is more information then what I want to display (I choose it to sum up everything, but I want to get rid of the thing that says 'summary of blah : 3 records) and I want to move the sum label to the far right and change it to total.
However I am finding out that any attempt to modifying the report in any way is changing my information. When its correct and working the report is 24 pages, each client has 3 sub types that come up and shows totals. However, if I make something invisible, move a label, change the text in a label or ANYTHING, then my report is completly screwed up, it drops to 3 pages, and loses all but 5 clients and only 1 sub type appears.
Why is modifying the report doing this?
Edit: its access 2003
Hi all - I'm new here and am very glad to find this forum for some help!
I've been using Access for several years and I do the same type of reports over and over and all of a sudden I'm having trouble.
I typically modify existing queries - changing the name of the department in order to pull up their info - and then I format a report and modify that same report over and over with the "Properties" key where I pull in the new query info and then use SAVE AS to save the report with that department's information.
For the first time ever it will not let me SAVE AS with the new info. Sometimes it just does nothing at all - appearing to save but then the report isn't there - sometimes giving me a pop up box that says it couldn't find the search key - I don't even know what that is!
I can SAVE AS with queries, but not reports. HELP!
Thanks!
I have a patient information database which uses a filtered form to show records for only one patient at a time. All the records in the database are linked together using one number, the patient's unique identifier.
I have designed a report which draws information from separate tables and generates a complete summary of information. This report depends on having the ID number of the patient to generate the report.
Originally, I had envisioned having a command button on the data entry form, which would then automatically take the ID number from the form, and generate the report. Though good in theory, it never worked out in practice, as I could never figure out how to make it happen.
Is there a way to design a macro or some other thing that would take the ID number from the field on the form, and use it to generate the report? Currently, I have it configured to prompt me for the ID number. When I enter it manually, the report is generated perfectly.
Thank you in advance,
I need to split out information in a report and I'm not sure how to do it.
The report is based on the following query:
Code:
SELECT tblHazardClass.HazardClass, Product.ProductName, Product.Package, Product.Size,
tblStoreProducts.Cases, Product.Units, Product.ReportUnits, tblStoreInformation.StoreName,
(([tblStoreProducts].[MaxUnits]*[Product].[Size])/[Product].[ConversionRate]) AS QOH, tblStoreProducts.StoreKey
FROM tblStoreInformation INNER JOIN (tblHazardClass INNER JOIN (Product INNER JOIN tblStoreProducts ON Product.UPC = tblStoreProducts.UPC) ON tblHazardClass.HazardKey = Product.HazardKey) ON tblStoreInformation.StoreKey = tblStoreProducts.StoreKey
WHERE (((tblHazardClass.HazardClass)<>"NON-HAZARDOUS"))
ORDER BY tblHazardClass.HazardClass;
My issue is that the products can have a PhysicalState of 'L' (liquid), 'S' (solid) or 'G' (gas). As of right now, only one of the HazardClass entries has multiple physical states (L or S).
My report details each product in the hazard class and totals the amount of that hazard class. Obviously, one cannot add gallons to pounds and come up with an answer that has any meaning whatsoever. How to have the report total the S and the L within a hazard class separately.
Do I do this within the query, or within the report itself? I've already considered making 2 separate Hazard Classes for the one in question (Corrosive). However, since these Hazard Classes are official classifications per the International Fire Code, that's not really an option.
I am trying to get some totals on a report, but the totals are from a table that the form is not related to as such.I am trying to get a textbox to show how many records in a query match the given criteria and show me the number of records that match. I was thinking DCount, but not sure if it will work?
I have a query called 'VehiclesAllocatedToday' which is already filtering records to show only those allocated today. On a report that shows Unfit Vehicle (and is related to a different query), I would like a text box that shows me how many records there are where the text "Type One" is in the TypeRequired field.
Hi there
This im sure is an easyone to most of you but for me I cant find away around my problem!!
I have a form I use to enter infomation and I have also made a report that I am trying to get the infomation from the form onto the report.
I would like to have a command button on the form that once I click it, it then prints out the report.
How do I get the information from a form to a report as when I have tried doing this myself it only allows me to use information from a query or table.
Any suggestions would be great for this newbie!!!
Thank you
I have a Table with Emp_ID and Details of my Employees. I have created a query and set parameter [Enter Emp_ID]. When I pull up a report, I get a pop-up and it asks me for the Emp_ID. When I input same, I get the information I need. Everything is good so far...
My Question... I have designed a report when user will need to enter Emp_ID and click on a button and they will get the report "EMPReport" without having the pop-up window asking for EmpID.
I have been using the below code but I get the pop-up again and I need to enter the Emp_ID again to view the report
See below Code:
Code:
Private Sub Image11_Click()
If IsNull(Me.Emp_ID) Or Me.Emp_ID = "" Then
MsgBox "You must enter an Emp ID.", vbOKOnly, "Required Data"
Me.Emp_ID.SetFocus
Exit Sub
End If
DoCmd.OpenReport "Rpt_HR1", acViewPreview, , "[Emp_ID]= " & "" & Me!Emp_ID & ""
End Sub
I have a popup form that prompts for a value and has a button defined with an OnClick,OpenReport with a WHERE clause. The value the user enters is placed in the WHERE clause.
The report is generated using a Query and the WHERE clause.
I was very surprised -- it works!
Now I'd like to add a chart to the report that represents the information on the report. How can I do this without calling the query again? Can I somehow reference the information that's in the report?
I have a template report (R_CurrentJobs) with fields from database.
Fields: FirstName, LastName, Email, JoB Number, TechID, TechName, JobLocation etc...
The query Q_CurrentJobs is a list of jobs to send to the tech for today. (These are open jobs queried).
I need to run this query on the report (R_CurrentJObs) and send the report to each tech based on their individual jobs. The jobs are connected to each tech by its TechID.
The report list the job information for each tech (JOb number, Location details etc.)
When I click the button to send the email. I want to email each tech individually based on the Q_CurrentJobs query using R_CurrentJobs. (DoCmd.SendObject)
We have a huge report in our company that includes all our customers, all the visits done by our sales reps, all the products they have and if a product was out of stock in the visit or not.
I want to create a querry that exports a table with 3 columns:
1-->customer
2-->date of last visit
3-->average of out of stock products on the LAST visit
I tried to make a pivot table, managed to grab the last visit (setting the date to maximum) but the average of out of stock products were aggregated totally and not for the last day.
How to handle this in MS access 2010?
I have a question related with a report i`m using to print labels. I am not familiar with Access and this is quite a challenge to do it alone.
In the attached file there is a report called "MICRA", when started it asks that you want to select (default is 1 and special select in this case is 11) and next it asks "SPS", the value entered there is printed in the bottom right corner of the report.
My question is is it possible if in the SPS field is entered a special value (for example "MASS") to print 30 labels of each selected label with text in this fiels = "val.1"; 15 with text in the field = "val.2"; 10 with "val.3" and 10 with "val.4" and after that to print next label with same rules...
And if not entered "MASS" to print just 4 copies of every label.
The database I have created is to produce product description sheets. From the switchboard, the user clicks a button that opens a form on which they can enter about 5 bits of information , then select the product they wish to produce the product sheet for and when they click a button, the product sheet is produced. The product sheet is a report with a subreport. The information entered on the form is included with more information from the database on the main report and the subreport is based on a union query which combines information from 2 tables to list the items included in this product. (the information the user inputs into the form is information that changes depending on the situation and is not stored in the database.
Now comes the next step in this process. Just as the information input into the form is temporary information, there is also one bit of information that needs to be input on a temporary bases for the items that are listed on the subreport. Each of the items on this subreport needs an assignment of AAA or BBB that needs to appear in the subreport on the line with that particular item. There can be one item in an product, or there can be many and the listing of AAA or BBB can be different from one item to the next or they could all be the same. The assignment needs to be made on an item basis.
My thoughts on this were to have a subform appear once the user selects the product on the form which would list all of the items and allow them to select either AAA or BBB in a column on this form. Then when the click to open the report, those assignments would carry through to the report.I have been able to create a continuous form displaying all of the items based on the union query but that's as far as I can get. Not even sure if that is the way to go.
Is there a way to reference the AAA and BBB input on the subform I described so that it displays in the report.
I have a question about summing information on a report. I am developing a report to see have percentage of time Staff work within a certain area. On the data base the time percentages entered as ranges e.g. 75-100%, or 11-25%. We needed to figure out the total percentage of time so I created a report to add the total time. I used a text box which it titled FTE_Total if embedded iif functions to display the total time. Here is the function I used:
=IIf([FTE_Percentage]="76-100%",1,IIf([FTE_Percentage]="51-75%",0.75,IIf([FTE_Percentage]
="26-50%",0.5,IIf([FTE_Percentage]="11-25%",0.25,IIf([FTE_Percentage]="1-10%",0.1,0))))).
This worked perfectly however I cannot get the FTE_Total fields to sum. I tried the following function in the group footer: =Sum([FTE_Total]) but everytime I try to look at the report in report view I get a message asking for the FTE_Total Parameter.
we have a huge report in our company that includes all our customers, all the visits done by our sales reps, all the products they have and if a product was out of stock in the visit or not.
I want to create a querry that exports a table with 3 columns:
1-->customer
2-->date of last visit
3-->average of out of stock products on the LAST visit
I tried to make a pivot table, managed to grab the last visit (setting the date to maximum) but the average of out of stock products were aggregated totally and not for the last day.
how to handle this in MS access 2010?
I have been looking all over for the answer to this and can't find the slightest thing about it.
I need to know how I can query information about what tables are in a MS Access database. Lets say for example I can't access the database with the MS Access application and I want to write a web query in asp to list the tables in the database. For example, I may not know what tables are in there and need to find out.
I know there is a way with mySQL using the "show tables" command but it doen't appear to work with Access
I also came accross some code with OpenSchema(adSchemaTables) that is suppose to do the trick, but it either doesn't work or my shared-hosting provider doen't have it set up to work.
If anyone could PLEASE help me out with the T-SQL query code to get table info I would be greatly thankful!
Thanks in advance,
Dan
dan@innovativead.com
Hi all
I have a query that is getting data fron 2 linked tables. The relationship between these 2 tables is 1 to many. The first table(One side) contains Due Dates for services. I have asked the query to return services for the current date which is working fine. The second table(Many side) has additional work required for each vehicle. The work can be either New, On Hold, In Progress or Completed. I only want work that has not been completed to be returned if the vehicle is due for a service today. I tried having the Work Status field in the query and criteria set to <> Completed. This returned all open work including vehicles not due for service. Is there anyway this can be achieved. If I take the criteria out of the Work Status field I get the information I am looking for but it includes Completed work and I do not need to know this.
Any and all advice appreciated.
Regards
Craig
I have 3 separate pages that display information from 3 separate queries, all from the same table.Queries 1 is on page 1Queries 2 is on page 2Queries 3 is on page 3My problem is I can't edit the information and I cant figure out how to make it editable. From what I read it should be. Can anybody help me?
View 3 Replies View RelatedIn one table i Have component group Id and the description as fields then another table has the sub groupings for each Component Group Id
Example
Component Group ID = 1 which is the primary key
Description = Keyboards
Component
Sub datasheet has Group ID = 1 foreign key
Descriptive Options = USB, Wireless, PS2, Serial
I have joined both of these in a query but what i want is the query when i link it to the combo box on the form i have to only show the Group ID once and not repeat it for as many times as each subdatasheet has records.
Example: When i click External Device I dont want to see the word Keyboard being repeated 4 times (which is because there are four subrecords/types of keyboards) Please Advise.
I have created a query showing locations of a company's facilities, and product groups associated with that facility. Unfortunately the the products groups are located in one column and, if the facility produces more than one product, the product group is divided by a |, as shown below:
CABLE TRAY SECTION [05CT]|STEEL CONDUIT AND ELECTRICAL METALLIC TUBING SECTION [05RN]
In my query, I only want to show the product code, which I resolved by creating another table and associating the code with the product group. However, this query will exclude those plants that produce multiple products. How can I show just the product codes for all the facilities, including those with multiple product groups?
In otherwords, I would like the above example to look like this: 05CT|05RN (or something similar to this).
This is the code that I used to build a query. But some of the data that I thought I removed is still showing up. Any guidance on why? As always all feedback is welcome.
SELECT final.[SSN P ], final.[SSN S ], final.[SP'S SEC Y97], final.[SP'S SEC Y98], final.[SP'S SEC Y99], final.[SP'S SEC Y00], final.NCCD, final.[TC-530], final.[ DOB ], final.[ DOD ], final.[ PRIMARY NAME (ENMOD) ], final.[ PRIMARY NAME CONT'D (ENMOD) ], final.[ STREET (ENMOD) ], final.[ CITY (ENMOD) ], final.ST, final.[ZIP 1], final.ZIP2, final.ZP3, final.ZP3, final.CNLY, final.C, final.YRLR, final.[ PRIOR YEAR NAME (ENMOD) ], final.PNLY, final.P, final.[XREF SSN ], final.[INOLEX XREF CD V], final.[ INOLEX XREF TIN V ], final.[INOLEX XREF CD I ], final.[ INOLEX XREF TIN I], final.[ PRIMARY NAME (IRPTR) ], final.[ PRIMARY NAME CONT'D (IRPTR) ], final.[ STREET (IRPTR) ], final.[ CITY (IRPTR) ], final.ST1, final.[ZIP 11], final.ZIP21, final.ZP31, final.TXPD, final.[#DOCS], final.[#_SUM], final.[WAGES ], final.TX_WTHLD, final.ALLC_TPS, final.INTEREST, final.MTGINTPD, final.POINTSPD, final.PRYRRFND, final.SAV_BOND, final.DIVIDEND, final.PENS_ANN, final.[IRA_CTB ], final.NONEMPCM, final.CPTLGAIN, final.RL_ES_SL, final.GRSSDIST, final.TXBL_AMT, final.FICATXWH, final.TFICAWGS, final.MEDPYMNT, final.TFICATIP, final.FICAMISC, final.DFRDCOMP, final.UNEMPCOM, final.[RENTS ], final.ROYALTES, final.MEDCREWH, final.MEDCR_WG, final.[OR_K1 ], final.GRWINING, final.STDLNAMT, final.[ORD_DIV ], final.ROIRACTB, final.SMPL_CTB, final.[FMV ], final.CD_S_SMP, final.STK_BOND, final.[ADV_EIC ], final.FISH_INC, final.OTHERINC, final.SUB_PMTS, final.GOLD_PAR, final.CROP_INS, final.TXSTTUIT, final.TX_GRANT, final.[AG_SUBS ], final.INTFORFT, final.ORISSDSC, final.BARTERNG, final.PROFLOSS, final.AMTDBTCN, final.PATRONAG, final.MSAGRDIS, final.[TX-PRD], final.[LFRZ-RFRZ], final.MODULE_BALANCE, final.[TC-150], final.TC150_DT, final.[TC150_DLN ], final.RETRCDDT, final.[TC-290], final.[TC-291], final.[TC-300], final.DC, final.[TC-301], final.DC1, final.[TC-420], final.[TC-421], final.DC2, final.[TC-424], final.PRJ, final.[TC-425], final.PRJ1, final.[TC-460], final.TC460_DT, final.[TC-462], final.TC462_DT, final.[COLL-ASG], final.CC, final.[TC-540], final.[TC-590], final.CC1, final.[TC-591], final.CC2, final.[TC-594], final.CC3, final.[TC-598], final.[TC598 DT], final.[TC-599], final.CC4, final.[TC-610], final.[TC610 DT], final.[ TC610 AMT ], final.[TC-611], final.[TC611 DT], final.[ TC611 AMT ], final.[TC-612], final.[TC612 DT], final.[ TC612 AMT ], final.[TC-670], final.[TC670 DT], final.[TC670 AMT ], final.[TC-671], final.[TC671 DT], final.[TC671 AMT ], final.[TC672 DT], final.[TC-672], final.[TC672 AMT ], final.[TC-673], final.[TC673 DT], final.[TC673 AMT ], final.[TC-678], final.[TC678 DT], final.[TC678 AMT ], final.[TC-679], final.[TC679 DT], final.[TC679 AMT ], final.[TC-922], final.[PROCESS CDS], final.[TC-960], final.[TC-961], final.[TC-962], final.[TC-976], final.[TC-977], final.TAXPRD, final.ADJRSN
FROM final
WHERE (((final.[TC-530])<>"TC-530") AND ((final.[ DOB ])>1929) AND ((final.[ DOD ]) Is Not Null) AND ((final.[TC-150])<>"TC-150") AND ((final.[TC-290])<>"TC-290") AND ((final.[TC-291])<>"TC-291") AND ((final.[TC-300])<>"TC-300") AND ((final.[TC-301])<>"TC-301") AND ((final.[TC-420])<>"TC-420" And (final.[TC-420])<>"TC-420") AND ((final.[TC-421])<>"TC-421" And (final.[TC-421])<>"TC-421") AND ((final.[TC-424])<>"TC-424" And (final.[TC-424])<>"TC-424") AND ((final.[TC-540])<>"TC-540") AND ((final.[TC-590])<>"TC-590") AND ((final.[TC-591])<>"TC-591") AND ((final.[TC-594])<>"TC-594") AND ((final.[TC-599])<>"TC-599") AND ((final.[TC-976])<>"TC-976") AND ((final.[TC-977])<>"TC-977")) OR (((final.[ DOB ])<1987));
Reg
Hi there, this is probably a very simple problem for people with experience of Access, however i have been having problems with being able to tranfer information from a query to a table.
I have made a query which displays a number of different items with their prices and who bought them (ie. booking ID)
I have then made a query which asks for the booking ID, then once you have input that it searches the items and adds up the price for all the items which that booking ID has.
both these queries work fine. The problem i am having is that i want to transfer this total price into a table. I have been able to do this by manually typing into the sql code the price, however when i try to get the query to collect the price itselfs, it gives the error
"operation must use an updateable query"
btw i am making the query as a update query,
sorry if this post is a tad long and messy,
thx in advance for any help
I have a table of events that includes fields for ages, a Start Age and a End Age.
I want to make a query that pulls up how many programs have been provided to a given age group. How can I make it so that the query will understand to include records that have the given age group in between the Start Age and End Age fields? I.e. if I set the query to 3rd Grade, records that start at 2nd Grade and end with 4th Grade are included in the results as well.
I am using Access 2010. I have a database that on a form uses a multiselect listbox. That part works just fine. The list box is for selecting additional people to email. Now I have had no luck with returning just the email address that are in a hidden column (the persons actual name is seen and "selected"). The names come from a separate table and is used as a forgien key. On that same table are the indivuals email addresses. What I did was loop through to get all of the ID numbers I am getting from the list box (the ID numbers are stored in the table that the form is based on).
Once I have all of the ID Numbers I thought that maybe there was a way to retrieve all of the email address associated with the ID Numbers. This is what I have so far. I know that AllQuery returns the first email address from the list box. I just have no idea if the query is returning more than one record, or if it is how to then go to the next record. I have tried a few things with little to no success.
Code:
Dim ListItem As Variant
Dim AllItems As String
Dim AllQuery As String
For Each ListItem In Me.EmailAdditionEgineers.ItemsSelected
AllItems = AllItems & Me.EmailAdditionEgineers.ItemData(ListItem) & " or "
Next ListItem
AllItems = Left(AllItems, Len(AllItems) - 3)
AllQuery = DLookup("EmailAddress", "AdditionalEmailRequestQuery", "[ID] = " & AllItems) & ";"