This query works, but is really slow. Is there a better way to write it? I have 2 tables, Card and Shipped. For all cards, I am trying to insert a record into the Shipped table if it they are not already there:
INSERT INTO SHIPPED (CardID, ShippedFrom, ShipDate, QtyShipped) SELECT Card.CardID,'Wpg',#1/1/2007#,0 FROM Card WHERE Card.CardID NOT IN ( SELECT Shipped.CardID FROM Card LEFT JOIN Shipped ON Card.CardID=Shipped.CardID WHERE SystemID= 10 AND ShippedFrom='Wpg' AND ShipDate=#1/1/2007# ) AND SystemID=10
i have created a query that solves for the difference of the debit and credit of the accounts receivables of a certain member or person..
SELECT DISTINCT ( (SELECT SUM(Amount) FROM (SELECT tblJournal.Amount, tblJournal.AccountAction FROM tblJournal WHERE JournalEntryNumber IN ( SELECT JournalEntryNumber FROM tblAccountsReceivableFromMembers WHERE MemberID = '2008-0001') AND AccountNumber = 120 ) WHERE AccountAction='Debit') - (SELECT SUM(Amount) FROM (SELECT tblJournal.Amount, tblJournal.AccountAction FROM tblJournal WHERE JournalEntryNumber IN ( SELECT JournalEntryNumber FROM tblAccountsReceivableFromMembers WHERE MemberID = '2008-0001') AND AccountNumber = 120 ) WHERE AccountAction='Credit') ) AS ['TotalAccountsReceivables'] FROM (SELECT tblJournal.Amount, tblJournal.AccountAction FROM tblJournal WHERE JournalEntryNumber IN ( SELECT JournalEntryNumber FROM tblAccountsReceivableFromMembers WHERE MemberID = '2008-0001') AND AccountNumber = 120 );
i need help on this so that there would only be one (WHERE MemberID = ?) to use. thanks so much! i hope to read your replies as soon as you can!
Since I am using Domain lookup function in this field, it takes considerable time for this field to get populated on the form when I move from one record to another.
Can anyone tell me the syntax for me to move this expression inside the Form's Query so this can execute faster?
Please note that I need this query to be updateable as I have some other form fields that needs to be edited.
I have a number of smaller queries that I want to join together to create a super query to display on a report. It works fine when I have a dozen queries joined, but as soon as I add any more, the query takes at least 10 minutes to run (although it eventually works). Is there a limit to the number of queries I can include in a query or is there some other underlying problem? NOTE : the smaller queries only contain half a dozen records with totals.
I have a query that calculates the 'amount of credit' depending on the draw down date (which is when they started circulating the loan docs b/w two parties). Anyways,, the question is how can I make two columns of 'amount of credit' instead of one. Because one of the team member is dealing with overseas loan, while others are working on domestic. I want to make a column that would seperate his works from other people's work. And, I am kind of confused, how will I do it on a report. I have tried cross-tab query and also simple. Nothing is working.
One column should carry the amount he has to give as a loan and the other should calculate the amount from domestic side.
I don't want to use list box as it is unprofessional and also, its looking bad. If I create two queries, how can I join them together. I tried that too, but didn't work.
This is a little complicated so I am not sure t can be done!!!
I have a query that lists 40,000 records with some being sent to label printing The query breaks the records down so that any record with a Qty greater then 17 will be sent to a second query for a different label to be attached.
I then end up with two Queries Query 1 is =<17 Query 2 is >17
Records less then or equal to 17 are sent for standard label printing.
Here is my problem:
Any record with a Qty field reading less the 3 or 4 I want them to print 3 or 4 of the same label. For example
Jane doe (Qty 6) this will print 1 Jane Doe label Joe Bloggs (Qty 3) this will print 3 Joe Bloggs labels
Am I trying to do something that just can’t be done!
When this is complete all information will be exported to a CSV file.
I am trying to query and summarise the contents of a table. I am unable to reach the results that I want. The table contains the following data:
AuditNo AuditTeam TypeOfTeam pName pAddress 234 CENTRAL SPEC TRUE FALSE 235 CENTRAL SPEC TRUE FALSE 236 WEST FSTL FALSE TRUE 237 EAST JUPT TRUE FALSE 238 EAST JUPT TRUE TRUE
I will like the results of the query to look something like this:
AuditTeam TypeOfTeam pName pAddress TRUE FALSE TRUE FALSE CENTRAL SPEC 2 0 0 2 WEST FSTL 0 1 1 0 EAST JUPT 2 0 1 1
In the results the columns pName and pAddress have subheaders of TRUE and FALSE. If this presents more problems then perhaps the subheaders can be removed and instead we will have pNameTrue and pNameFalse. Note there are 116 columns similar to pName and pAddress in the original table.
I don't know whether this is possible. It may require several queries to get to this. I am really stuck with this one so any tips will be very welcomed.
I want to build a form based on a query, but I don't seem to be structuring it properly. Please refer to attached picture to help explain what I'm trying to do. I have 4 essential fields in a table, tblInpatientLog. They are POSno, Primary, POS Code, and Length of Stay (calculated as difference between Admit Date and Discharge Date).
What I need to do is have the form display the POSno that are in the table as "21" and "61" ONLY. There are others, but I only need to see these two. I then need to split the form (visually) into two sections. On the right, I need to display the Primary field as ones that are in the table as "Healthy Plan" only and on the left, I need to display Primary as ones that are in the table as "Medicare" only.
Under these headings, I need to show the total number of days, under the Length of Stay field for each of the four hospitals, Hospital 1, Hospital 2, Hospital 3 and Hospital 4, which are identified in the table under the "POS Code" field as "16", "2", "5", and any number OTHER THAN 16, 2, or 5 (or, whatever is left), respectively.
The Length of Stay field is calculated as difference between Admit Date and Discharge Date fields in the form for each individual record. I need access to show me a SUM of all the number of days for all the records that are listed as "Healthy Plan" for the "Primary" field for Hospital 1, then for Hospital 2, then for Hospital 3 and then for Hospital 4.
Then I need Access to calculate the SUM of all the 4 calculate field results for the 4 hospitals and display in the "Total Hospital Days" field on the form. Finally, I need a count of the total number of records that meet the criteria of "POSno" = 21 and 61, "Primary" = Healthy Plan, "POS Code" = all.
I hope that makes some sort of sense for someone. I've tried to create a query, but it doesn't display any results. I've wondered if I need to create more than one query, and have tried various different options without success. Any alternate suggestions would be welcome at this point. Thanks in advance for anyone out there that might be able to help.
I'm totally new to access so please bare with me, what I want to do is create a query that takes the price from one column and takes 25% off it if there is 2 units in stock, 30% off if there is 3 units, 40% if there is 4 units in stock and 50% if ther is more than 5 units in stock, the results will then be created in a column called DiscountPrice and the percentage off will will be created in a column called SaleInfo. Is this possible?
Hi there - I've looked at quite a few posts and although some of them seem to start of heading in the right direction there doesn't appear to be anything that quite hits the spot for what I'm looking for.
I'm trying to create a query that creates the following (see spreadsheet as typing it out didn't help.)
First off let me say that I'm VERY new to Access. I've been put in charge of administering a large (to me) database and I've just started working with the insides of the database for about 3 weeks now.
The most urgent thing though is I need to make an "Honour Roll" report for the employees, that is, they get pass/fail inspections and I'm trying to create a query that returns all employees that have 10 or more passes IN A ROW, with at least 2 of those passes being a personal evaluation vs. an after the fact quality verification. There's already a query in the database that returns the employee ID, total passes, total fails. I've managed to add the field that includes type of inspection, but I can't seem to find a way to count the number of PE's in the field.
The second issue is getting the query to ignore all inspections prior to their last fail. I think I can work out moving the date of the inspection into the query, but then using it is another story. If it requires a script of some sort, then I'm hopelessly lost. The last programming I did was BASIC back in school... many years ago.
If I were to break it down into a statement, it would be something like this:
If (employee passes) >= 10 since last (employee fail) AND (inspection type="PE") >=2, then send (employee ID) and info to a report.
Any help with this is appreciated, I'm starting to get desperate!
I have 3 tables that i wish to pull information from:- TBLProspects LeadID (Primary Key) CompanyName RenewalDate Status
TBLNotes NoteDate Note Who LeadID
TBLQuotes QuotePremium Income Reason OnCoverPremium LeadID
I have created the query and at the moment, I am getting duplicated rows of "Note" because there are more than one "QuotePremium" for each CompanyName. Is there anyway of showing the CompanyName, the Note (just once) and then the 2 or 3 QuotePremiums records?
SELECT DISTINCT TBLNotes.NoteDate, TBLProspects.CompanyName, TBLProspects.RenewalDate, TBLNotes.Note, TBLProspects.Status, TBLQuotes.QuotePremium, TBLQuotes.Income, TBLQuotes.Reason, TBLQuotes.OnCoverPrem FROM (TBLProspects INNER JOIN TBLNotes ON TBLProspects.LeadID = TBLNotes.LeadID) INNER JOIN TBLQuotes ON TBLProspects.LeadID = TBLQuotes.LeadID;
The scenerario is the following: I have a table that keeps information for weeks about some files (e.g: if the file has been sent). Then I have another table with information for days in a month (the data for every day).
What I should do is: Check wheter the week has been sent, if it has been sent, I have to show the data for every day of that week in a report. Only for one month... and I can't figure out how to build that query...
I guess I should do something like: select week from weekTable where week=week and sent=TRUE set rcset=db.openrecordset(sql) if(not rcset.eof)then 'build in here the query to select the data I need from the month table end if
but i can't figure out how the loop would be for 1 month...
Some essential background first. I have a Balances table which records balances by date. I also have an Issues table where problems are logged. There is a one-to-many relationship between Balances and Issues (i.e. each Balance can have multiple Issues). I also have a Comments table where updates for each Issue are recorded. There is again a one-to-many relationship between Issues and Comments (i.e. each Issue can have multiple Comments)
There are two key date fields in the Issues table :FlagDate (the date an Issue was flagged by a user for investigation)
ResolveDate (the date said investigation was brought to a conclusion)
There is also a date field in the Comments table :UpdatedWhen (the date any given comment was added)
So the basic flow is that an Issue gets flagged (FlagDate), then various comments are added (multiple UpdatedWhen's) and finally the Issue gets resolved (ResolveDate)
I need to incorporate a trend graph which will show the counts ofNew (i.e. new issues flagged as of each day) Cleared (i.e. issues resolved each day)
Updated (i.e. issues not yet resolved but updated each day)
Unchanged (i.e. issues not yet resolved and not updated each day)
Outstanding (i.e. all unresolved issues as of each day)
This is the SQL I've put together to get that table of information on which to base my chart :
Code: SELECT [tblBalances].[BalanceDate] AS AsOfDate, (SELECT COUNT([tblIssues].[IssueID]) FROM [tblIssues] WHERE [tblIssues].[Flag] = True AND [tblIssues].[FlagDate] = [tblBalances].[BalanceDate]) AS New,
[Code] .....
The subqueries for 'New', 'Cleared' and 'Outstanding' work perfectly; the resultant dataset gives me one record for each date in the Balance table and correctly counts the number of issues falling into each of those buckets.
The problem I have is with the 'Updated' bucket. If a flagged issue happens to be updated twice on the same day (which is perfectly acceptable), it counts this twice as well. I don't want this as I just want to know how many issues were updated on any given day - not how many updates there were.
I tried using COUNT(DISTINCT) in the 'Updated' subquery but it gives me a syntax error - on further research, I don't think it's possible to use the DISTINCT keyword in a COUNT subquery (at least not easily)
I also tried grouping by IssueID within that 'Updated' subquery but it still gives me the duplicate count within the same IssueID (and returns nulls rather than zeroes for those days where no updates occured)
I think I need to add a subquery within the subquery () to only return the latest comment as of the date in question - something along the lines of :
Code: (SELECT TOP 1 [tblComments].[UpdatedWhen] FROM [tblComments] WHERE [tblComments].[IssueID] = [tblIssues].[IssueID] AND DateValue([tblComments].[UpdatedWhen]) <= [tblBalances].[BalanceDate] ORDER BY [tblComments].[UpdatedWhen] DESC) AS UpdatedWhen
But how to do this, nor if it is even feasible in Access to begin with.
I am brand new to Access/VB as of Thursday. I am an aspiring programmer still in college. My proficiencies are more to the c++/HTML side of things right now, but I am in dire need of some assistance. I was recently given a series of projects to determine if I am qualified for a particular job. I have completed them all but one. I am modifying an invoice form in Access. I have accomplished all but two of the tasks on this last project. I need to modify the invoice form so that whenever a payment amount is entered, the payment date box I created populates with that date. I know I'm really close to it, but I keep getting errors and the only thing populating the box is #Name?. I also need to open up the print/preview mode of the invoice report when I click the print button as opposed to the current form it opens up to print from right now. If anyone could offer any assistance/advice on anything...even a tutorial that might point me in the right direction that would be excellent. I have until Tuesday to figure this out =) Thanks a bunch in advance!
P.S. This is where I thought I needed to execute the update for the payment date since it is where the payment is entered. If this doesn't help at all then I apologize.
Private Sub I_Payment_Exit(Cancel As Integer) Me.I_Tax = NullToZero(Me.I_Subtotal * Me.I_TaxPerc) Me.I_Total = Me.I_Tax + NullToZero(Me.I_Subtotal) + NullToZero(Me.I_ShipChg) If Me.I_PaymentLock = True Then Else ' Me.I_Payment = Me.I_Total End If Me.I_Balance = Me.I_Total - NullToZero(Me.I_Payment) End Sub
This Forum is a God Send and everyone is so helpful. Ghudson's Audit Trail code saved me many hours of hair pulling :) Anyone out there who may be able to help with my Audit Trail Query?
Anyway, I've been developing a database for the last 2 weeks (night & day it seems!) to track "bugs" found in the code we (not me) develop, suggestions & new requirements and just when I thought I'd got it cracked they came up with the next requirement they'd forgotten to mention!! :eek:
I need a super snazzy search function. The top half of the screen is a form with various combo boxes of which some or all of the values can be selected i.e reference no. author, between dates and a keywords field (a bit like the search page at the beginning of this forum!). On clicking the 'Select' button all the records that meet the input criteria appear in the bottom of the screen in a data sheet view (Ref No. Description & Date). The user can the Double Click on the record they wish to view and they will be taken to the full record in the DataEntry Form.
I'm sure this isn't as complicated as it seems to me at the moment but I am so stumped I don't even know when to start. I've been writing databases for years using the wizards within Access but only recently started to experiment with code. I would really appreciate any assistance forum members can give me but as I am new to these complications please provide 'idiot proof' answers so I can understand what I am doing. :)
So I have an access application to keep track of the status of small programmable PCBs that we use.. every time one of these pcbs is relocated or reprogrammed, an entry is made into a table in my db (tblLocation) with a unique ID for that action, the ID of the PCB (SD), the location it is going to, the new owner, the firmware version it's been programmed with and the date. Hence, multiple (dated) entries for each board are possible.
what I need to try and do is retrieve the most recent set of data from this table for every board we own... so far I have come up with a method that uses 2 queries, but I would like to combine this into one SQL statement that I can pass to the access db from an excel front end using ADO...
here's my 2 queries: #1 (qryLastEntry) finds the date of the the most recent entry for each board:
SELECT DISTINCT tblLocation.SD, Max(tblLocation.Date) AS LatestDate FROM tblLocation GROUP BY tblLocation.SD;
#2 uses the result set from the above to retrieve only the most recent set of data for each board:
SELECT tblLocation.SD, tblLocation.Date, tblLocation.Owner, tblLocation.Location, tblLocation.Firmware FROM qryLastEntry INNER JOIN tblLocation ON (qryLastEntry.LatestDate = tblLocation.Date) AND (qryLastEntry.SD = tblLocation.SD) ORDER BY tblLocation.SD;
If there is a way to combine the 2 into one statement, that would be very useful, as although this method works just fine with access and using the built-in 'get external data' feature within excel, I'd like to be able to manipulate the SQL pragramatically (and also, I may want to migrate to mysql so a cross-platform solution would definitely be preferable in the long run). I've had a look at using subqueries to achieve this but with no luck so far..
I have an Analysis_Database which reads tables and performs calculations in a Project_Database. There are multiple Project_Databases which reside together in a folder. To run my queries and analysis I link my Analysis_Database to each of the other databases and run my reports.
Is there a way to set up these links in a table or something to eliminate the Tools/Utilities/Linked Table Manager...etc
Any siuggestions to make this process more streamlined would be appreciated. (I can not make any changes to the Project_Database).
Record_No <--- This is an autonumber field Est_COE --Date Field Type DATE_DOCR DATE_Followup Progress
I then have used multiple querries that just count records based off of Status:
Example of one of the querry's: SELECT Count(borrower.Record_Number) AS Close_Of_Escrow FROM borrower WHERE (((borrower.Est_COE) Between Date() And (Date()+7)) AND ((borrower.Progress)="active"));
Another Example of one other querry: SELECT Count(Borrower_Journal_Notes.ID) AS Followups_Not_Done FROM Borrower_Journal_Notes WHERE (((Borrower_Journal_Notes.Follow_Up_date)>Date()) AND ((Borrower_Journal_Notes.Followed_Up_Complete) Not Like -1));
Then I put both these querry's into a another querry so i can get the data put into a single form using one record source. (The master querry)
***this has already been posted on the report formus...i posted it here also due to its relation to forms***
**********************DO NOT GET MAD**************************
GOOD MORNING...
I currently have a form to launch a set of pre-desinged reports. The main function of the form is that it allows the user to sort or filter the data that appears in the report based on their selection (from combo boxes). I currenty have it set up so that the user can choose a specific client, facility, or region. The problem is the following:
THE FORM IS WAY TOOOO BUSY AND NEEDS TO BE SIMPLIFIED. EX:
CLIENT (combo box) Microsoft nortel dell ibm . . . btnLaunchRPT1 btnLaunchRPT2 btnLaunchRPT3
REGION (Combo box) North South East west north-west . . . btnLaunchRPT1 btnLaunchRPT2 btnLaunchRPT3
etc.
each btnLaunchRPT'n' has the following code DoCmd.OpenReport "rptECM Numbers", acViewPreview, , "[Region] = " & "'" & Me.Combo299 & "'"
but I change the [region] to whatever it is I am sorting by. Can I reduce this down to only having one set of btnLaunchRPT buttons that will detect what the user has selected and applies the proper sorts
Hi, hope someone has an idea of where to start on this.
Our company offers projects to multiple contractors and we have to enter when we spoke to them and their response. I put a textbox with default of Now(), thinking these would be recorded in real time and it would be no effort at all. But the offers are frequently entered days later, and keying in dozens of specific dates/times in the long format is very irritating for users.
Does anyone know of something similar to the DatePicker or calendar popups that will let you use arrows or comboboxes or something to quickly enter a time? We record date, hour & minute (no seconds) because it updates the contractor's order on the rotation list (i.e., next call is made to the contractor who has been waiting the longest for an offer). No ActiveX please, we're in secured network environment.
hi friends, i have tried had to connect sub type tabels (Saving, Checking, Loan... they have their own ids...) with super type (Account...it has account id...) on the condition of account_type (either "S","C" or "L") attribute in ACCOUNT entity. how to joint them??? with query or with expression?? i expect help from you.........please. ........thanks.
Hi gurusI'm looking for query to extract records from one table linked to another table based on different ranges. Now here goes.SELECT Invoice.dbInvoiceNum, Invoice.dbInvoiceDate, Invoice.dbFirstName+' '+Invoice.dbFamilyName) AS dbPatient, Invoice.dbdob, Invoice.dbServiceProvider, InvoiceItem.dbItemNum, Invoice.dbPayerName, Invoice.dbTotal, Invoice.dbTotal-Invoice.dbBalance AS dbPaid, InvoiceItem.dbServiceTax, TransactionLog.dbTransCodeFROM ((Invoice INNER JOIN InvoiceItem ON Invoice.dbInvoiceId = InvoiceItem.dbInvoiceId) INNER JOIN LEFT JOIN TransactionLog ON Invoice.dbInvoiceId = TransactionLog.dbInvoiceIdWHERE (dbInvoiceDate Between #4/1/2006# And #4/30/2006#) AND (TransactionLog.dbTransCode <> "CRDC") AND (Invoice.dbActualTotal > 0) AND (InvoiceItem.dbItemCode Between '57506' AND '58115') AND (InvoiceItem.dbItemCredit = False)ORDER BY Invoice.dbInvoiceDate, Invoice.dbInvoiceNum, TransactionLog.dbTransCode DESC;Now problem from above query is that I not only want invoices with items from the range(InvoiceItem.dbItemCode Between '57506' AND '58115')also want invoices with items in range of(InvoiceItem.dbItemCode Between '1' AND '5267') AND/OR (InvoiceItem.dbItemCode Between '57506' AND '58115')Invoices with items could either be just in range of '1' AND '5267' AND '57506' AND '58115'.I am not interested in invoices in range '1' AND '5267' without the range '57506' AND '58115'. For every invoice in the range of '57506' AND '58115' it will have a range '1' to '5267'.The above query just rips out the invoices with items in the range '57506' AND '58115' and showing duplicates and not the items also contained in range '1' to '5267'. Writing separate queries and extracting and looping takes far too long especially if total database dump was involved. It could take 20 mins to create report due to volume of records could be somewhat over 300K. I have hope and done before in one query that takes little time, however this seems little complicated.Any help would be much appreciated.
Hi, I hope that some one can help. I have column that ends with (1232) how can i extract only the 1232 for example,moved car to blablabla(1232) I want to create a new column and insert only what's in Parentheses. thanks in advance.
