I have written a simple query for address label printing purposes, but I do not want to print labels to identical postal addresses.This sounds such a simple requirement but my efforts to write a simple 'criteria' in the Address1 Field have been unsuccessful.
View RepliesHi All,
I am rather new to this and I must admit I am getting everything completely wrapped around my head!
I am developing a database that stores technical support requests received and their solutions. I have three main tables :
Problem - stores all the details of the problems received
Client - stores the address details of the client which is then associated with the 'Problem' table
Company - stores the name of the company which is then associated with the 'Client' table. This is a separate table as different clients can work for the same company.
At present, I have a 'problem data entry' form in which I enter all the details of the problem. At the top there is a section where I can do a search for a particular client and company and the details appear in the respective text boxs on the 'problem data entry' form. If the client does not exist, I have a button that I use to open another form which allows the entry of the new client's details ('New Client'). The user can choose which company this client works for by using a combobox. If the company is not already in the database, I have another button for opening a 'New Company' form!
Its all a little convoluted and it does not work particularly well to be honest. When I add a new company, I need to move forward a record and then back in the 'New Company' form in order that the new company information is saved correctly.
The company selection combobox in the form 'New Client' is then updated with the new company name BUT I need to actually open the combobox and select the company name myself for it to work properly! Once again I also need to move forward one record and then back again in order to properly save all the information before I can close the form.
What would be nice is to do a search for the the company in the combobox on the 'New Client' form, if the company is not found, it automatically adds the company to the database whilst updating all the necessary links. Hence getting rid of the 'New Company' form. I would also like to get rid of the problem of having to move forwards a record and then back again.
Any ideas? I find my method to be rather poor, there must be a simpler way of doing this!
Many Thanks,
Lee
I'll first of all explain the purpose of the query. I've built a database to record the admission details of patients admitted to hospital. Amongst other things, the database captures date/time of admission and date/time of discharge.
The query I am building needs to show patients with multiple admissions, and in particular, the number of days that have elapsed since they were last discharged. Within my query is the following subquery that I had written to show this aspect of it:
(SELECT TOP 1 Dupe.[Discharge Date]
FROM [Inpatient Database] AS Dupe
WHERE Dupe.[CHI Number] = [Inpatient Database].[CHI Number]
AND Dupe.[Discharge Date] <= [Inpatient Database].[Admission Date]) AS [Previous Discharge],
Note: "CHI Number" is a unique reference number assigned to every patient.
I then display in a column [Admission Date] - [Previous Discharge].
This works fine where a patient only has one previous admission. However, where a patient has multiple admission, the subquery always returns that patient's first discharge date instead of their most recent discharge date (because I have used the "TOP 1" predicate). It seems that it should be straightforward enough, however I can't seem to work around it. Anything I try results in errors, and so I keep defaulting back to my "TOP 1" solution.
i.e. What I want it to show is:
Name.......Admission Date...Discharge Date.... Previous Discharge... Days Between Admissions
Joe Bloggs ..01/01/2014 .......10/01/2014
Joe Bloggs ..15/01/2014 .......20/01/2014 .......10/01/2014 ..............5
Joe Bloggs ..27/01/2014 .......01/02/2014 ........20/01/2014 .............7
However, what it actually shows is:
Name ......Admission Date ...Discharge Date ...Previous Discharge ...Days Between Admissions
Joe Bloggs ..01/01/2014 ......10/01/2014
Joe Bloggs ..15/01/2014 ......20/01/2014 ........10/01/2014 .............5
Joe Bloggs ..27/01/2014 ......01/02/2014 ........10/01/2014 .............17
One set of users enter sales orders that are pretty simple in nature... Order Number, Item, Quantity, Ship To... etc. They occasionally run across the need to make multiple entries that are identical, because a customer wants the order delivered on a regular time schedule or they want invoiced in increments, etc.
So, they have a need to be able to enter as many as 50 orders, all identical except for the Order Number (and delivery date), all at once. None of these fields is a key field or an autonumber. Order Number is manually entered, though it is indexed - no duplicates. I'd like to create the ability within a form for them to put in a list of Order Numbers, plus the values for the other required entries, and have them populate the source table by applying all the entries to each of the individual Order Numbers.
Any ideas would be greatly appreciated!!!
Tom
I have a database that was created by a colleague. They have created a form for adding client records onto the database.This is done via a button that, when pressed, checks mandatory fields for correct entries and then saves the record.However, when we do this, it is creating two identical records on the database.
She cannot see what she has done wrong in the code, and I know next to nothing about coding,
Code:
Private Sub CmdAdd_Click()
Dim dbAddClient As DAO.Database
Dim rstAddClient As DAO.Recordset
Set dbAddClient = CurrentDb
Set rstAddClient = dbAddClient.OpenRecordset("ClientInfo")
[code]...
I have an access 2003 database which holds data for lorry loads of timber delivered to different places at different prices and by different hauliers who get paid different ammounts.
I have tables that hold the prices for both haulage (sorted by the delivery location and haulier) and product price (sorted by the delivery location and the haulier.
I have two almost identical queries which give the haulage price for a particular load and the product price for a particular load.
Only the haulage prices seem to be returned - i have checked the settings and relationships in each of the tables invlovled and they appear to be that same.
Why would one query return the value and the other not???
I have a question regarding making a query with joint data that are not identical, as I'm not sure whether it's possible.
I have to look at certain serum levels at te start of a new medication regimen, and serum levels after 5 years.
I have one database with:
Patient number
Start date medication
Start date medication + 5 years
And one database with:Patient number
Date of blood test
Serum level molecule A
Serum level molecule B
What I want to do is make a query where the patient numbers are joined. The problem is that the "start date of the medication" and the "start date + 5 years" are not identical to the dates of the blood tests. E.g.: I have a patient that started medication on 01/March/2006, but he has had blood tests done every 3 months from 28/November/2003 till now, and not on 01/March/2006.
I would need the results of the blood tests (of molecule A and B) at the start of the medication and after 5 years, but if the test date differs by a day, Access already sees they're not identical.
I would like to have access select a blood test date that is as close to the medication start date as possible, within a time frame of e.g. +3 and -3 months. Is this at all possible?
I have multiple databases (one for each department). All 4 databases have an Agents table (tblAgents). Now I am creating another database that uses all of the agents on site. What I want to do is link all the agents tables and combine the values in one query.
Fields are:
UserID (PK)
AgentName
BadgeNumber (Employee Number)
Campaign (Department)
Coach (Supervisor)
HireDate
Status (Active / Leave of absense / Terminated)
The tables are linked to my new database. So logically, I'd think my next step is to query the tables. This is where I'm stuck. How do I say:
Agent = tblAgentsDepartment1.AgentName and also tblAgentsDepartment2.AgentName and also tblAgentsDepartment3.AgentName and also tblAgentsDepartment4.AgentName?
I'll have to do this for each field involved...
I'm trying to create a database for a construction company, as an experiment more than anything, and my problem is this:
I need to create a set of identical records in the table "buildings" based on the information entered into a text box by a user on the form for registering a new project. I want each record to be identical except for the "plot number" field, which I want to start at one and increase to the number of buildings defined in the text box.
"Projects" is one table and "Plots" is in the other.
In my transaction table, I would like to find out the latest transaction dates of each family models. It sounds a simple Max function can get the desired result. However model number consists of the first 6 characters for the family model and the rest for the versions (variants). The Max function fails to work in this scenario. Is there any other way to group the model numbers once the 1st 6 characters are identical (the rest is ignored)?
View 3 Replies View RelatedI have two identical Access tables, for example tblDataYesterday and tblDataToday. The data from tblDataToday has updated data changes in it that are different from tblDataYesterday. I have a maximum of 100 fields to review. I want to compare the two tables and display in a query the differences between the data from both tables.
View 1 Replies View RelatedI am somewhat lost and would really appreciate your help!
I am using a database to classify newspaper articles. There are several users - and they need to be trained to the classification rules.
Waht I would like to do have is a way to let different users classify the same sources. However, the "trainee-user" should not be able to see the data already in the database. Also I would like to flag all "trainee-user" records to compare them to the original results.
My questions are thus:
How Do I hide records from a user and yet allow him to use the database (and see the stuff he entered himself)
How can I save information on a user in a table?
Thanks a lot in advance!!
BM.
I have a table with name, club members details ID etc. This is linked by a 1 to many link (ID) to a table containing details of membership subscription payments. One entry/row per membership period. This second table has DatePaid, Paid (Yes/No), Period. Period contains 2013-14, 2014-15 etc.
I can do a query for those that have Paid (Yes) but when I try one for those that have not Paid (No) or <>Yes I get no result. I only enter members when they have paid. I need a query to display those who have not paid for the 2014-15 period.
I can do it a long winded way copying 2 lists into Excel. One all members. One those who have paid. Then remove duplicates and those paid in 2013-14 leaves those not paid in 2014-15.
The programmer who we had used is not available and I would like to see the Unfiltered Details of the field
Code was : =DSum("Deposit_Amount","c_Deposit_Slip_Lines","Can celed=False and c_Deposit_Slip_Header_ID=" & [ID])
I want to see the Deposit amount unfiltered
i.e.: Deposit amount $1,234.56
I have been managing a database system for a homeless shelter in my volunteer role. There is a certain feature that would improve accuracy of the inputted records, as well as freeing up time for staff. That is - auto filling a user's Gender and Nationality when inputting a record for the drop-in centre.
Currently, when a forename and surname is inputted in the "DropIn_Input" form, it is checked against the stored 'service users' table and checks for the name combination. If the name combination is not present then the forename and surname fields change to 'New'.
What I would like is to keep this, but if the service user name is stored in the 'Service User's table then I would like their Gender and Nationality to be automatically filled into the fields on the 'DropIn_Input' from the relevant information in the 'Service Users' table.
I have a report based on a query with 3 fields (Tested, RMA, OpenRMA). The query searches all the tables in my database and gives me a count of the equipment tested, RMA reported and open rma.
The report has two fields (Equipment, NumberTested) with a total below and also a total of the RMAs and OpenRMAs. I'm trying to hide RMAs and OpenRMAs from appearing in the body and only showing below the total. All three totals are showing correctly, but I am still seeing RMA and Open RMA with a null in the count in the body.
So in the report's Equipment.control I wanted to put
Iif([NumberTested] is null, null, [Equipment])
Meaning if the numbertested field is null the equipment field will be blank, if it is not just leave what is in the equipment field there, but it returns a #Error in the printout. It seems to handle the two null records correctly. Trying Equipment.Value also returned the same error.
I am trying to retrieve data for a particular record.
When Project field matches a certain project number I want it to pick the record with the latest date in the date field field to select certain data fields(Owner & Rating) from that record.
Below is my attempt. However the problem is that displays all records with that project number and not just the record with the latest date.
Code:
SELECT [Combined PRB Roadmap].ProjectNumber, Max([Combined PRB Roadmap].DateField) AS MaxOfFDateField, [Combined PRB Roadmap].Owner, [Combined PRB Roadmap].Rating
FROM [Combined PRB Roadmap]
GROUP BY [Combined PRB Roadmap].ProjectNumber, [Combined PRB Roadmap].Owner, [Combined PRB Roadmap].Rating
HAVING ((([Combined PRB Roadmap].ProjectNumber)="NR-4237"));
I have a form where the user puts in a email address, eg test @ myserver.net
So the link opens a new mail it has to be prefixed by mailto:
How can I automatically replace the address the user enters with the format mailto:test@myserver.net
Is it possible to once you have run a query be able to somehow double click on the results in order to either go to original record or see more details? Similar to "show details" in excel?
I have made some queries that are working well however they are specific in nature and once found display a list of results. I have left a field that uniquely identify them and are after a quick way to view the details of the results from the query rather than take the ID# and manually search the original table.
Hope you can help!
I have an access table which lists a customers address, however, if the customer has a temporary address it will also list that on a separate line -
CustID Address Type
1234 5 The Street P
1234 12 The Street T
2345 13 The Road P
3456 12 The Avenue P
Where P = Permanent and T = Temporary
I want to set up a query to show the permanent address where there is only one address, but where the customer has a temporary address also, I want to list the temporary one instead.
Thanks
I am using Access 2007. I have a text field that contains multiple e-mail addresses. The field is generated from a excel file that I download from a website the company uses. I would like to create a query or VBA code to change the e-mail address to just the employee name. The problem is the filed can have multiple e-mail address separated by a comma. I have been changing them manually using "Search - Replace", but that is getting tedious as the database is getting used more often.
View 4 Replies View RelatedAttached you will find an example with the problem.
The query is only searching identical data between both tables.
I want it to show results from all users, with all the devices each user has.
What am I doing wrong with the query?
I have a form bound to a table which stores contact info for a person. I have two controls that are email_address and website_address. I set them both as hyperlinks.
So now when the user clicks on the email_address Outlook opens a new message with an email_address in the To field. (I had to use a function found on this forum to replace the "htttp" with "mailto" on AfterUpdate event in order for this to work properly.) However, now trying to add some other features to my DB I ran into problems listed below. I wonder if I should have rather left that control as Text instead of Hyperlink, and use the DoCmd.SendObject on doubleclick event instead of using the above solution.
How people usually store email addresses and enable emailing on click?
The problems I ran into:
1) My main form is in popup mode and therefore right click on the hyperlink does not give an option to edit the hyperlink
2) I copied a function from this forum which gathers a list of emails and sends one message to the emaillist. However, my email list instead of generating as:
email1@a.com; email2@b.com
it generates as:
email1@a.com#mailto:email1@a.com#; email2@b.com#email2@b.com#
I guess I could extract the email using vba (not sure how), but I still don't know how to solve issue number 1. So maybe it is better to siwtch the field to plain text and forget the hyperlinks?
Thanks,
Mariusz
PS. What procedure on double click would open a default browser, since I also have to deal with web addresses and thought of turning them into text controls?
I am trying to find a way to extract an email from a large text file that is an output from our email system. I would like to be able to extract the email address using a query or collection of queries. I have been able to extract all of the text that contains the @ symbol. From their I created a query expression:
Mid([field1],InStrRev([field1]," ")) that captures some but not everything I need.
So I have the following query which works perfectly (and will be use to create a report) but when the query runs to create the report I also want it to mail merge some of the items into word to be used on address labels.
Code:
SELECT Dunmow.Date, Dunmow.Undeliverable, Dunmow.Duplicate, Dunmow.[OTM with XXXXXX], Dunmow.[Landlords Salutation], Dunmow.[LL Address 1], Dunmow.[LL Address 2], Dunmow.[LL Address 3], Dunmow.[LL Address 4], Dunmow.[LL Address 5], Dunmow.[LL Postcode], Dunmow.[Letter 1], Dunmow.[Letter 2], Dunmow.[Letter 3], Dunmow.[Letter 4], Dunmow.[Letter 5], Dunmow.[Letter 6], Dunmow.[Letter 7], Dunmow.[Letter 8]
FROM Dunmow
WHERE (((Dunmow.Date)=DateAdd("d",-7,Date())) AND ((Dunmow.Undeliverable)=False) AND ((Dunmow.Duplicate)=False) AND ((Dunmow.[OTM with XXXXXX])=False) AND ((Dunmow.[Letter 1])=False));
So thats my query, What I want it to do is to take the following
[Landlords Salutation]
[LL Address 1]
[LL Address 2]
[LL Address 3]
[LL Address 4]
[LL Address 5]
[LL Postcode]
and use it for a merge item to create the labels required.
I have a standard word document/label document I wish to paste this into.
The query is there to query data, not to edit data or manipulate data in anyway. However, I am disgusted by the appearance of the date picker when I pick a field that is a date field.
Is there a way to hide/remove these date pickers from the query sheet?
Within the table itself I turned the "Show date picker" property to "Never" but that didn't make a difference.