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?
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?
View 1 Replies View RelatedI am trying to create a simple Search form in Access where a user can select a desired record and query multiple tables using the inputs.
I would like them to be able to query Retailers, Distributors and Products.
The 6 tables are linked as follows:
Although some of these tables are not included in the query, they are required to ensure relationships.
Retailers -- Uses (RetailerID,DistributorID) -- Distributors
Retailers -- Orders (RetailerID,ProductID) -- Products
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:
Code:
Can a Append Query move all my data stored in multiple tables to another database with a identical table structure?
Because as I try to work the query, I keep getting prompted to "Select a table" I want to append to, and I don't want to append to just one table...
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.
Hello Forum,
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)?
Thanks in advance!
Rehnahvah
I have the query
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'
Hello all:
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.
Thanks for your input.
Odun
Hello all:
I need your urgent help with the following:
I am setting up a query in access. I have about 4 criteria for one of the fields called Center, I want the query to do the following:
Return all rows if:
Center is Like 6101*
Center is Like 61HKS56800
Ignore row if Center is Like 6101D*
Ignore row if Center is like 6101SALM01
I put in the following and I am not getting the correct results:
Like "6101*" Or Like "61HKS56800*" And Not Like "6101D*" And Not Like "6101SALM01"
Thanks,
Odun
Hi
Apreciate your help on this.
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?
many thanks
scott
Hello
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 tried using And and Or, but with no luck.
Czn anyone help?
Many Thanks.
Frank.
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...
strSQL = " SELECT NewsClips.RecordNumber, 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] " & strNewsSource & _
strNewsSourceCondition & "NewsClips.[1CategoryMain] " & str1MainCate & ")" _
str2MainCateCondition & "(" "NewsClips.[NewsSource]" & strNewsSource & _
strNewsSourceCOndition & "NewsClips.[2CategoryMain] " & str2MainCate & ")" ";"
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;
Your help will be greatly appericated.
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.
Hope I'm making myself clear, apologies if not.
Any ideas?
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:
2A5x1x (i.e. 2A531B or 2A571)
2A5x3x (i.e. 2A533A, 2A55B, 2A57C)
2A6(or 7)x1(or 2,3,4,5,or 6)
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.
Would the append option be appropriate?
Hi all,
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:
http://content.imagesocket.com/images/gl_codes2ff.JPG
I'm really lost here. Any advice would be appreciated.
Hi Folks,
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?
Thanks in Advance
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.
Thanks!
Mike
Hi,
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.
Hi all,
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.
Thanks for any assistance!
Hey all,
I think this is a simple fix but I am very rusty on access so I can't figure it out, even after a long haul on search engines!
Problem:
I have a single form in my db. This form has a series of drop down menus, check boxes etc etc allowing the user to select as much information they want to query against!
My end goal is to be able to submit this form, a report is opened and relevant records are shown depending on what options the user has selected.
I have attempted to use a query to acheive this, by simply using:
[Forms]![frm_stats]![cbo_location] Like [Forms]![frm_stats]![cbo_location] Is Null....which I was told would work, i.e. expecting the query to miss the form fields that were not selected....But no, the query only returns recrds based on my first combo box on my form and ignores the rest.
Wondering if anyone has any thoughts? - I don't really want to go down the route of using vba and checking each field in turn!!!!
Thanks a bunch.
I have 12 db that I would like to ectract labor hours from, then put all data on a report. Is this possible? Please explain how too.
Thanks
My second post. Heres a link to an image of the database below
http://casp.gamecommunity.co.uk/database.gif
In the search section I want to be able to search multiple fields. The date from and to data will come from the same field, Coverage is a drop box and Organisation and keywords could be anything.
I know I will have to grab the text from the text boxes (curently named Textdateto, TextDateFrom, TextOrg, TextKey, and TextCoverage), (the fields are called Dt, Organisation, Keyword and Coverage) use a select query via the on click property of the search button. But I am bit lost on how to put this into practice.
Does that make sense?
Casp
Hi all,
Another month, a new database, more questions... a quick thankyou to all those who helped with my last database, much appreciated!
I am trying to set up a query run from 12 tables, each table is supposed to be the same but we have had a number of people changing things when they shouldnt have been. Each table is a month of posts in our employee headcount, each post has a ID Key which shuold stay the same throughout, some of these IDs have been deleted when they shouldnt have been, some more have been added when, again, they shouldnt have been. Each post ID has a set FTE number (dictates the hours they do a week), some of these have changed, again when they shuoldnt have, some have changed when they shuold have and we have a record of these ones.
I need to be able to check which post IDs have been deleted, what month (or table) they were first deleted from. What post IDs have been added and what month/table these were first added. I guess i use the first table as the reference point and also that maybe layered queries (compare tables 1st month/second month and then do a query on those results against 3rd month and so forth but not sure how this would pick up changes from the first month...) but not sure quite how to do this and whether this is the right way to do things. The next thing to do will be to check the FTEs but will wait until i sort this query out first.
thanks in advance
Shapman