Hi all, I haven't worked on Access in a couple of years, so VERY rusty.
I have to make a database for the mechanics, the equipment (inhouse stuff only), and all of the workorders done by them. That part is done already and I think its working properly. The next step is where I'm a little lost.
I now need to make 3-4 reports that will be run off of queries to get "Work Performed on Equipment X", "Work Performed by Mechanic X", and "Work Performed in Month X / Year X".
The people that would be accessing this information aren't the most computer literate, so I need to make it as simple as possible, especially since I'll be leaving the company within a year (I should be graduating within that timeframe).
I'm not sure if I should do a single query that can pull multiple information with dropdowns for Mechanics, Equipment, Month and Year (With each having the option of a single selection or ALL). This way, the user can pick and choose the info they need.
The other option would be using the switchboard, have a button to do each type of inquiry, then using a subform (similar to what OneRedTL4 made for iibobii and his movie database), but would still need 2 dropdowns for the by date inquiry (one for Month and one for Year (up to say 2012)).
I guess I'll stop here and wait to see what other information may be required from you folks or ideas to start with. I have to work on this in between my regular work, so unfortunately I may not be doing this with the greatest of haste.
I have a subform within a main from which switches by entry. The subform is a datasheet. I have two dropdowns in the datasheet and I want to limit one off of the other. I keep getting a prompt. The prompt is Forms!CLTS Subform!Environment. To my understanding it looks like it can't find Environment. Does anyone know the proper format.
This is my code for the second drop down in the subform. Also special note Environment is what I am referencing to and it also is in the subform.
Code:SELECT PackageName.PackageNameID, PackageName.PackageName, PackageName.EnvironmentIDFROM PackageNameWHERE (((PackageName.EnvironmentID)=[Forms]![CLTS Subform]![Environment]))ORDER BY PackageName.PackageName;
created a form with several Drop downs, S/N, Location, Manufacturer, Model, Model Number, and Owner.
All the drop downs are based off of forms, i queried the main inventory to have this info and a couple peaces of information. applied the relationships.
If i try to make a macro filtering the conditions, if i leave any of the fields blank nothing comes up in the search.
is there a VB code that i can use that wont include "null" fields or will this not work with 6 different filters applied all in one macro.
furthermore after the search i want the ability to print the search results using a report design.
I have built a Project/Resource database for our department, and I am having an issue with 3 computers in the group. First, all of our computers are setup the same (as far as I can tell), however, there are 3 users that have the same issue...when selecting an item from a dropdown, it should populate the dropdown below it with a new list, based on the first dropdown. It is only on certain forms, and only happens on these three computers. If the users log into any other computers, it works fine. I know it's not alot to go on, but does this sound familiar to anyone? I'm at a complete loss!
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
All retailers have at least one distributor BUT a retailer may or may not have ordered any products.
I have created my form but the query linked to the form is having some trouble. It is only selecting those records that have ordered products. For example, if I query a retailer name only and it does not have any ordered products, it will not display. Is there a problem with the table joins? The SQL for the query is displayed here:
I have a DB where you there's 5 tables all linked together by one project ID
tables below
Project , Staff, Asset, allowances, travel, mark up
What I can do is create a new project, then add records to each of the other tables on what different items I require,
i.e. I create a new project - called project one, in the project table I create a record stating, name, time scale, client and location, then I add different records to each of the other tables on what I require all linked to the same project ID. (probably not explained that too well)
Now I want to create a query that lists all the requirements one after the other this will make it easier to create reports and to calculate costing's.
At the moment I have made 5 different queries listing all the data, then have one report containing 5 sub reports to display the data, no this does work.
Right now I have a query that filters data based on unbound fields on a form using criteria in the format of "[Forms]![Form1]![Field1]" and it works fine.
I have multiple forms that all have the unbound fields (named the same on each form). Is there a way to have the query to filter by the data from the active form, so I can have one query that can be used with any form (instead of having a 1:1 query-to-form ratio)?
SELECT tbl_ImportedRepairs.InvoiceNumber FROM tbl_ImportedRepairs WHERE tbl_ImportedRepairs.TrinityBatch IS NULL GROUP BY tbl_ImportedRepairs.InvoiceNumber HAVING (((Sum(IIf([BlakeApproval]=False,1,0)))=0));
This query finds line items in a table and gives me the invoice number ONLY if all the line items have been approved.I would like to build on that and say:
Give me Invoice Number if ALL line items have been approved AND All The Updated Responsibility code are 1
I am thinking it might be like this
SELECT tbl_ImportedRepairs.InvoiceNumber FROM tbl_ImportedRepairs WHERE tbl_ImportedRepairs.TrinityBatch IS NULL GROUP BY tbl_ImportedRepairs.InvoiceNumber HAVING (((Sum(IIf([UpdatedResponsibilityCode]=1,1,0)))=0)) AND HAVING (((Sum(IIf([BlakeApproval]=False,1,0)))=0));
But of course that doesnt work and I am getting Syntax error (missing operator) in query expression '(((Sum(IIf(BlakeApproval=Flase,1,0)))=0)) AND'
I have multiple tables in my access db, the structure of the tables (4 in all) are exactly the same except that the billing dates are for year 2001 to 2004 i.e. separate table for each of the years.
I don't want to copy the information in these 4 tables into 1 table because I have over 2 million rows in each table. However, I want to be able to run 1 query that will provide information from all 4 tables. E.g. filtering for billing dates between 2001 to 2004 for a particular customer.
How do I join the tables together so that I can run a query on all 4 tables at one time.
I have a stock control db and when goods are recieved they could have a different references on them e.g. our internal ref, the purchase order ref etc.
I would like to run a query that firsts ask what criteria i want to use to run the query e.g use the int ref or the PO nr. I can set up a simple parameter query in one field but can not find a solution for the db asking what field i want to use before you put in the reference?
I was wondering if anyone knows how to do the following;
I have the number of years staff have been working for the company which is extracted from the startdate.
Based on the number of years service employees are entitiled to extra days leave, so I need to do the following:
IIF([YEAR]<5, 24) and IIF([YEAR] between 5 and 9, 25) and IIF([YEAR] between 10 and 14, 26) and IIF([YEAR] between 15 and 19, 27) and IIF([YEAR] between 20 and 24, 28) and IIF([YEAR] > 25 , 29)
The above syntax does not work...
HOw do I achieve the above? Any suggestion would be gratefully recieved. Ta :rolleyes:
Hi All. I currently have a query that runs a report. The "Project_Status" field of the query is decided from a combo (cboPS) that is on a form (frmReportManager). The user selects the status from the combo and clicks a button to run a report with that status. That is all OK.
What I would like is 4 tick boxes on the form with the 4 project Status (Ongoing, complete, invoiced and quote). The user would then choose the status types he would like in his report, not just the one type as in the combo. How do I go about referencing the 4 text boxes for the status types, to the one field in the query (Project_Status).
I have this code of a command button, which would allow me to generate the result of the SQL. I think the code is wrong... Can someone help? I guess something wrong with the bracket...
Indeed, I try to modify the SQL that works in a test query (as I want to know what went wrong with my code): the changes would be replace OR to a toggle option.
SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips WHERE (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[1CategoryMain])=[Which Category?])) OR (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[2CategoryMain])=[Which Category?])) ORDER BY NewsClips.IssueDate DESC;
Hi. I have a situation where I have 2 tables A (20K records) and B (2K records). Each table has a field 'Name'.
I'm trying to figure out a query that will return all the records in table A that have a LIKE match in table B, i.e a the name in table B is somewhere in the record in table A (pattern matching).
So, it looks as tough I would need to step through the records in table B, take the data in the 'Name' field, compare it with a Like '*<whatever>*' match on table A to return any records that contain that data from table B.
Then move onto the next record in table B and compare it again to all of table A and so on.
The result set would then display just the unique rows from table A that had a pattern match with one of the rows in table B.
I've built a pretty extensive database for work thanks largely to the folks on this board. But I'm stuck. I have a report to track inspections and it works fine, but I'd like to filter it down by AFSC (a coded expression the AF uses to denote career fields, i.e. 2A5x1x is Aircraft Maintenance.) The report's queries come from a couple of sources to retrieve the requirements for the report. For instance that AFSC has to start w/ "2A" and the labor code needs to start w/ "1" and not be "120". No problem. The problem I'm having is that the first 'x' (from the AFSCs below) is based on skill level (can be a 1, 3, 5, 7, or 9) and the last x is sometimes there and somtimes isn't.
For this particular report I've broken down the AFSCs down to three different formats. They are as follows:
The report is designed to show who is due an inspection and it works fine. But I need to try to make it a little more user friendly so that all the various workcenters can trim it down to view just their career fields. Right now it reads from a query.
Here's the SQL for it.
SELECT qryPEDueUnion.[Main Assessee], [Employee List Table].[Employee RANK], [Employee List Table].[Employee NAME], [Employee List Table].AFSC AS FilterAFSC, [Employee List Table].[Labor Code] AS FilterLaborCode, qryPEDueUnion.[Inspection Type], Last(qryPEDueUnion.Date) AS LastOfDate FROM qryPEDueUnion LEFT JOIN [Employee List Table] ON qryPEDueUnion.[Main Assessee] = [Employee List Table].EMP GROUP BY qryPEDueUnion.[Main Assessee], [Employee List Table].[Employee RANK], [Employee List Table].[Employee NAME], [Employee List Table].AFSC, [Employee List Table].[Labor Code], qryPEDueUnion.[Inspection Type], Left([AFSC],2), Left([Labor Code],1) HAVING ((([Employee List Table].[Labor Code])<>120) AND ((qryPEDueUnion.[Inspection Type])="PE") AND ((Last(qryPEDueUnion.Date)) Between DateAdd("m",-19,Now()) And DateAdd("m",-15,Now())) AND ((Left([AFSC],2))="2A") AND ((Left([Labor Code],1))=1)) ORDER BY Last(qryPEDueUnion.Date);
Let me know if you need more info. I'm new to posting on this board and haven't quite found out how to attach files....Plus this db is pretty big and I'd have to fool around w/ a copy of it first to make it small enough to attach.
i have an excel worksheet with 24 similar sheets : a,b,c,d, aso the sheets contains similar data: nr, firm, invoice, sum, payment date, etc i linked them to access creating tables : a,b,c,d, aso i must create a query based on them to sort the data in the tables ascending by the due payment date (query based on multiple tables)
i've done this for 2 tables:
SELECT * FROM a, b WHERE (((a.SCADENTA)<=[due payment date]) And ((b.SCADENTA)<=[due payment date]));
but it multiplies the data by 2 or many times in the resulting table. what is wrong?
I'm in the process of learning to use Access and I would appreciate any help that can be provided. The problem that I am facing is that I have data in three different columns, and this cannot be changed. The database that I am creating links to another database to retrieve a table. This table contains two columns of data that are essentially the same for my purposes. I have also created a new table in my database which houses new data and also this will be the location where new records are be stored. Ideally what I'd like to have is a single table with the information from the three sources stored in a single column that updates as the linked table is changed. Is there a way to do this with a query? or should I be looking into another method? Any help is appreciated. Thanks.
I'm working on a purchasing app in access. At this point i'm working on the reporting module. I want a user to be able to fill out a start date (text box), end date (text box), and select a code from a list, hit Run Query, and have it pull up a report listing the date that the selected code was used, between the start and end date, and display other info as well.
The problem i'm having is that i can get the date ranges to work, or the code to work, but not both of them. Here's what i have in my query:
I was just wondering if its possible, i'm sure it is....... I've got a query to pull out invoices for a certain Project it brings up all project invoices and puts it into a report which works fine...... what i want to do tho is perform the same query but after i type in project code in criteria [] i then want to type peoples name or ID's that i want to print as some will be sent at different time intervals and i don't want to have to sort through invoice's to then send? is it possible? any one got any examples?
My DB tracks courses taken by people. Most of these have an expiration date. I can make a query to show me who is overdue on class "x", "y" and I can make a query to show me who hasn't taken "x", "y".
Is there a way I can combine the two queries into one report?
Here is a some what complicated question dealing with queries.
I have several tables with data and every month I create a new table with similar data. I am trying to build a query to keep track of certain statistics with in the monthly tables. However I do not want to have to update query each month linking the tables together. What I really would like to do is have a table with the names of each of the tables, which I could up date easily, which links the query to each table to determine the statistics. If this is making any sense, I would appreciate the help.
I have 3 fields that I need to run a query on. Date_Image, IMAGE_SYSTEM and DATE_TO_BR. If there is no entry in either fields, it should be part of the query. If entry is in both Date_Image and Image_system, I do not need those results in the query. If Date_to_br field is empty, I must have an entry in the other 2 fields before this record is not displayed in the query. I hope I was able to explain this clear enough. I was trying to do this with the IS null and Is Not Null expressions, but I am not getting the results I want.
Resolved the date range issue, thanks, now I have a problem with the query results. I have a query based upon several other queries that have the StaffID and the linking factor. When I combine some of the queries it works fine, but with others, seemingly those that have no valves or data in the tables, the query brings back no results what so ever.
The queries are running a simple count of records so they have, StaffID input, date range and it counts the records for that staff member within the date range. The second query gives a sum of the count of the first query. But when I try to combine the queries so that I can produce a report for multiple work areas for audit, I hit problems, is there another, easy way for me to do this, maybe creating a report from invididual query results and only combining them for the report itself.
HELP! Can not attach actual db because of sensitive nature of the information contained, but would be willing to create a like db as sample.
