And another table country: id
that has the values:
1,4,5
Basically is there a query that can be written for me to check if ALL the countryids from the country table will return an artist, and if not can it let me know. For example obviously there is no countryid with 1 in the video table so Im not interested in that batch in the country table. I know I can just execute the query 3 times to see if a result is produced but what if the country table is massive (btw the country table is generated dynamically using xml). Any help on this would be very mch appreciated
I have made severa databses before but im trying to make this one perfect and im curious, when defining tables should you include atributes about an entity that are static?
Its kind of hard to explain what i mean but for example in a shoe shop a staff member gets commision on what they sell (ie 5%) so when producing the table should a coloum be reserved for commision or should this be left out as it will be calculated later on in a querie and if so should i state this in the design section of my database?
any opinions would be greatly received as i have been reading books on sql and ERD's for days and havent been able to obtain any answers
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.
Hi, I have a couple questions about a project I am trying to work on right now.
I am very very new to Access but am learning quickly. I just wanted to run this by everyone and see if any of you had any ideas.
The goal of my database is to track patients from our clinic. I have it set up as follows:
1 table containing basic patient info: Med record #, name, date of birth, age, gender, family history of cancer, etc etc etc.
1 table listing all the possible diagnosis of cancer that we treat in our clinic
1 table listing all the procedures that are performed by our surgeon
--------
Here is the issue. I can enter all the patient info with NO problem. All my lists populate nicely and I have dropdown boxes to make it easy to choose items, etc. I cannot, however, figure out exactly what I should do if a patient has multiple surgeries, multiple diagnosis, and even multiple testing types done on tissues from each of those surgeries.
How on earth can I set up a form so that I can enter all the basic patient info. Then enter a procedure and a diagnosis - then enter a second procedure and a second diagnosis (though the diagnosis would most likely be exactly the same as the first procedure). I don't want to have 3 records for each patient if we perform 3 procedures during one surgery visit. If the patient comes BACK for another surgery, that is a different matter.
Sorry for the long post but I appreciate ANY help anyone can provide.
I am not new to Access, but have only before created basic databases with simple structures and I am having problems with my latest project. The idea is to have two tables, “tblStudents” and “tblClasses”. I also want to have a form called “frmClasses” and this is proving a bit of a problem. I am trying to display the class data for each class record on the form (Time, date, details), but below that want a table containing all of the students which are registered to that class, which at the moment is just causing me to go round and round in endless circles.
Sometimes, the form just won’t load and after I change it, it will just put in duplicate records containing the same class data but a separate record for every student in that class.
I have a relationship set up in the database which links the field “ID” in students to the field “student_ID” in Classes. But I think that the problem may be there. I can post a copy of the database if you want.
im am making a database to keep track of the soldiers in my company. right now i have come across a problem and am having troubles with entering the phone numbers. i have three tables for this purpose;
soldier(SoldierID, SSN, LastName, FirstName.....) phone(PhoneID, Number, Type (cell or home)) soldier_phone(SoldierID, PhoneID)
in my main soldier form which uses the soldier tbl for its data i embeded a form to enter in phone numbers, which doesnt work. long story short, i can only get the phone table to be filled out, but the soldier_phone table never gets any values so the phone number never gets linked to the soldier.
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 have created a basic client contact database, and dependent upon whether the client is classed as say, as a decision maker, or as an influencer will affect when they should next be contacted. I have attempted an expression in my Date of Next Contact Field using my Classification field, and Date Last Contacted field. If there is no date in the date last contacted field, I just want the current date returned.
Please note I have never typed expressions in Access before, only Excel !! This probably explains why this isn't working!!!!. It says "the expression you entered contains invalid syntax (you may have entered an operand without an operator)". Can any expert tell me what I've done wrong??? Don't tell me, it's simple.....?! Here goes:
=IIf([Classification]="Decision Maker" And iif(IsNull[Date Last Contacted], Date(), iif([Classification]"Decision Maker" And iif(IsNotNull[Date Last Contacted], [Date Last Contacted]+30,” ”))))
When I can get this to work, I want to add to the expression for different classifications.
I have searched and tried previous examples to only fail in this process.
We have a database that is accessed by over 20 employees at one time and virtually every day we run into the same problem. Someone is trying to edit a record but can not do this due to another user being logged into that specific record. Not knowing who is in the record burns valuable working time trying to hunt down the individual and getting them to filter out of the record so it can be edited by the other user.
To eliminate this pointless search, I would like to create a simple box in the form that displays who is logged into that specific record (if any). That way we know exactly who is in that specific record and we can call them directly.
I have attempted the many examples in the searches of this forum but it may have to do with my lack of knowledge working with Modules or Expressions. Current User only displays your own user ID making it useless in this case.
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...
Hi, got a small problem. As part of a database I have to do for a booking system for a conference centre I have a form (frmSummary) that displays the results of a query (qryT101Book), the query itself displays all the bookings for a certain room from today onwards. This form only displays a summary of the information. I have placed a button on the form to call up the main booking form, (frmBooking) which allows the input of all the data for the booking. On ythis second form, frmBooking, I have placed a button to save the record and update the display on frmSummary. The code I have used for this is Forms![frmSummary].Requery and it works okay, the form is updated. However, tomake it easier to confirm the booking has occurred I would liketo have the cursor move to the last record, i.e. the new booking, on the frmSummary once the command button on frmBooking is clicked. If it was a one record per form situation i would use SetFocus but I'm unsure how to go about this one.
In one of my forms I have a date field. I have a macro button to send that date to appear on an Outlook task.
Is it possible for my date field to appear in different coloured text once the macro button has been pressed, so that I know it's been sent and don't send it twice?
Now I know that for most of you experienced users out there, you would probably just make my macro do a loop?? so that all dates from the database reach Outlook, but I'm so new to this that the above method seemed possibly easier?!
Any ideas? Oh, here is the code used for my macro - so if anyone can tell me how and where I add code to loop this, please let me know!!!
Private Sub cmdOutlookRem_Click() Dim OutlookApp As Outlook.Application Dim OutlookTask As Outlook.TaskItem Set OutlookApp = CreateObject("Outlook.Application") Set OutlookTask = OutlookApp.CreateItem(olTaskItem) With OutlookTask .Subject = "Contact " & Me!Forename & " " & Me!Surname & ", " & Me!CompanyName .Body = "Company Tel No: " & Me!CoTelNo & ", " & "Direct Line: " & Me!DirectLineinCo & ", " & "Mobile: " & Me!MobileNo .ReminderSet = True .ReminderTime = DateAdd("n", 2, Me!DateNextContact) 'Remind 2 minutes from now. .DueDate = DateAdd("n", 5, Me!DateNextContact) 'Due 5 minutes from now. .StartDate = DateAdd("n", 2, Me!DateNextContact) .ReminderPlaySound = True .ReminderSoundFile = "C:WindowsMediaDing.wav" 'Modify path. .Save End With End Sub
Each time a person enters a part number into the form they will have an option to pick and load other forms. Looking to have 1 form with a cmd button that when pressed will go out and retrieve an object or objects (spreadsheet, word document and etc) and then place in the OLC’s and rename and store this object on a drive with the name of the part number and form name I want the templates never to change or be updated. Only the objects store on the drive after the command button is pressed or the Ole object is double clicked can be edit.
I've tried hyperlinks, do not want to have operators do save as, also I would have to have all the forms entered for each part number prior to versus just using templates and having them be stored.
Example of save forms names c:mydocuments racking12341234-Template-1 c:mydocuments racking56785678-Template-2
Not even sure this can be accomplished. See attachment for a visual idea.
i wondered if anyone could help me with a formula i am trying to construct. i have a report which is based on a query where the user types in the ID of the project they want to view.
here is a screen shot of the report http://i132.photobucket.com/albums/q19/lost989/formula.jpg
i wondered whether it was possible to have a calcualtion to show how much was spent on different categories. so there would need to be a formula to calculate all those records named as timber thier indivdual cost times the quantity of each. i need this for each category of items.
I have written the following query for use with my Automatic Weather Station
10 Min Wind Direction: Val(IIf((([Date]="11/07/2006") And ([Time text]>=1600) Or ([Date]>"11/07/2006")),[CR10X AWS]![Field6],""))
Basically I had some data from 1300hrs on 7th July until 1600hrs 11 July that was wrong and I needed to 'null' the data reading - the above works fine showing a value of '0' for the incorrect data between these times / dates and all the correct values are displayed since then however, since 01 August the field nows shows 0 again - any ideas why ?
I have several queries in access that are linked to excel and when i update the excel worksheet the query information appears in a tabular format, which is fine. I also ahve a master table in excel which is supposed to update using the info imported from access. This table is based on fixed cell refernces but whenever i update excel with the access info, the majority of the excel fixed cell refernces change to random numbers? Any ideas?
My question is essentially about the saving of information into the underlying database. I have been searching the forums and havent had any luck in finding what I wanted. There were a number of similar threads but I couldnt find any replies which could really point me in the right direction.
1. I am trying to prevent the underlying data from being changed until I click the save command button. It cant seem to work since even before I click the save button, the record is already written.
I tried using Before update cancel = true but that prevents me from moving away from the control. Also, I tried using edit locked but that still records the changes once i made it on the form.
What is the most common way of people saving records upon confirmation? And how do you prevent automatic recording? Any advice would be very much apperciated.
I have done a bit of reading and research on the 'tag' property in forms and tried to set some code. Now I am all muddled as to how to do it and what comes first?
What I am trying to do is if the condition in one field 'time required' is yes then I would like a whole lot of bound controls (fields) not to appear so that the user cannot enter any information by mistake.
The code is below;
Dim ctl As Control
For Each ctl In Me.Controls
If Me.TimeRequired.Value = "-1" Then If Me.Tag = "Fermyesno" Then
