Tricky Query
Sep 7, 2006
I have an insurance policy database keeping track of insurance policy details for household and commercial shops and businesses.
Tables I have are tblPolicyHousehold, tblPolicyCommercialS (Shop), tblPolicyCommercialB (Business).
They each contain the following fields (the other fields they contain aren't relevant to this)... SumInsured, extCover1Desc, extCover1SumInsured, extCover2Desc, extCover2SumInsured, extCover2Desc, extCover2SumInsured
I want write a query to list all the policies with the building sum insured. Easy enough... Heres the catch.
In some of the extended cover fields it will say in the description...
"Building of 123 AnyStreet" so I want to get the value of that extended cover item where the description for it contains building...
So I want to get extCover1SumInsured where extCover1Desc LIKE "*Building*" and adding that value to the SumInsured field...
Any ideas?
View Replies
ADVERTISEMENT
Oct 2, 2006
I have one query that shows a NAME for each day.
Every day each NAME must have one and just one SITUATION.
So I'm trying to make a query that shows which have NO SITUAITON yet and other that shows which HAVE A SITUATION already.
View 4 Replies
View Related
Feb 19, 2008
Hi All,
I have attached a file which I need a help from, I need the subform to be populated based only on the Employee Code and the Dates where the employee logged-in a Job Order.
I dont have any code on the cmd button for I am lost on what will be the query statement to update the subform datasheet.
Thanks.
View 2 Replies
View Related
Jul 15, 2005
Hello all:
In table 1 I have some document #s
e.g.
320
321
322
170
171
172
151
152
In table 2 I have some document #s e.g.
170
171
151
I would like to create a query, that will look at the document #s starting with 17* and 15* in table 1 and look for it in table 2, if the document #s do not exist, I would like the query to spill out the following:
320
321
322
172
152
Is this possible?
Thanks so much in advance for helping!
View 3 Replies
View Related
Nov 19, 2004
I have a training database in which I want to make a text box that data only needs to be entered once for 50 records (not once for each record). But I need to include the information from that box in a form that is already getting its info from another table. When I try to do this with a second table I get this- #Name. And if I include the needed field in my query I'll have to update each record rather than just one time. I have a different database that does this, but I inherited it from someone else, who locked out design view, and I can't contact him to find out how he did it. PLEASE HELP!
Thanks,
Scott
View 5 Replies
View Related
Jul 26, 2005
I do not know if I'm shooting for the moon here (I haven't seen anything like this yet). I have a database that keeps track of different flights for screw conveyors. The part number on these flights are based on many fields in the database, with a couple characters that aren't. I am trying to make a validation rule that checks to make sure the feilds that are a part of the Part Number match up, or if there is a way to have it auto-fill everything except the 2 characters that have to be entered manually. I don't even know if what I'm saying makes sense (Story of my Life).
Thanks to anyone who can help, and those who wish they could.
-Brian
View 1 Replies
View Related
Jun 18, 2007
im creating an error reporting database.
for this the hardware/system items need entering
they will have a unique id
to assist identification i would like the unique id to start with a 3 letter code refering to the type of hardware/system/periferal followed by the number
e.g a printer will ahve the id "ptr 00000" and a computer will have the id "cpu 00000" with the 3 letters automaticaly comeing from a combobox field in the same table (form to the user)
i have allready been able to get autonumbers with 3 letters before when i created the tecnitian id they are "TEC-00000"
any help thanked. im an A2 student so i know all the code stuff.
thanks in advance
Jimmythemadman.
View 14 Replies
View Related
Nov 5, 2007
In Access 2003 we use an mdw file (unfortunately named system.mdw) that people are joined to (by using Workgroup Administrator) when they become a new user on the system. This works well except that it is time consuming to go through the process for every new user. I am aware that you can create a new mdw file using the user-level security wizard however I have extra problems. This was set up originally when access 97 was in use and the no of users was about 4, there are now 50 plus and growing. We have numerous databases that are all secured through the use of the same mdw file, however as people are only joining this mdw (by using the Workgroup Administrator), when we get someone with a little computer savvy they will figure out that by creating a generic system.mdw file they can escape all security levels that are in existence in the system.mdw file that we want them to use.
Is it possible to somehow get a database to associate itself with an mdw after the mdw has been extensively modified?
I am aware of short cuts that have the mdw file in them but they are easily bypassed. Furthermore as we have numerous probably 100 databases all pointing at the one mdw file is it possible to get them all associated with the same mdw file? I have looked through the forums and various others but have not found an answer that will help me. Can anyone help?
View 5 Replies
View Related
Jan 10, 2008
Hi all,
My boss has asked me if I could take a look at the following problem and whilst I've searched around and tinkered with it myself I'm not making too much progress!
Here is an example:
I have forecast data for working weekdays Mon-Fri. For the weekend I need to replicate data from Friday across the Sat/Sun rows. This is just outside my remit! The data is then exported to Excel where imbalance costs etc. are calculated.
Background: The result will be that management will be able to tell what value *weekend* work will have. At present I already have a set of results for all DF activity. If we take that figure and subtract THIS result we'll be able to see how much benefit working on the weekend has.
Something management are very keen on looking into :)
Any suggestions on where to start would be much appreciated.
Thanks.
View 3 Replies
View Related
Feb 3, 2008
Ok, I have a table that contains a number of yes/no fields depending on location.
There are two main parts of my db that use this, one is users (for their departments) and the second is changes (for secondary locations)
I want to have two fields in the table used to reference a set of locations. Meaning i can reference the table and get locations where userID = 3 or alternativly where ChangeNumber = 6
Can somebody help me in making this work. Im currently having issues with duplicate values etc
View 3 Replies
View Related
Apr 25, 2006
Hi all - I am not sure if this is even possible, but I know if it is someone here will know how to do it. I have a table with a list of dogs names. The dogs run in a team so I have another table with their run record. Currently, the form would have a text box where you select the dog's name and then another text box where you select the position in the team so it looks like this:
name1, position1
name2, position2
and so on. I would like to create the form where the text boxes are aligned to look like the team and the person would just have to select the dog's name that ran that position and the code behind would create a record that looks like the one above. For example, here is what the form would look like:
Position1 Position2
Position3 Position4
Position5 Position6
I would like the person to be able to select the dog's name in the text box labeled Position1 then move on to select the dog's name in Position2 and so on. I am imagining the record will still look like the one above. Meaning,
name1, position1
name2, position2, and so on.
Does anyone know if this is possible? Thanks so much for all your help. This forum has been so helpful in the past, I thought I would just put this out there.
Therese
View 5 Replies
View Related
Aug 23, 2005
Hi all,
I have a table that has a long date format for example 05/05/05 02:40:34 AM how can I add an 8 hours to the date and time. Please help if you can because I"m going nuts!!! thanks. I want it to look like 05/05/05 10:40:34 AM
View 11 Replies
View Related
Apr 14, 2006
I'm probably approaching this wrong, as I'm definitely out of my depth - maybe someone can offer some advice
I have 2 spreadsheets. One is a simple parts list with a code and some other info - I've loaded this into Access no problem.
The second sheet is used for ordering - it contains one text field with a company name at the top, followed by a list all in one column of all of the item codes, and another column with a description, and a third column with an x in against each item being ordered.
What I would like to do is set up a new table with the company name, and then each of the item codes which have been selected (there will always be a maximum of 10 ordered so I envisage a field per ordered item code).
I haven't got a clue how to do this - Can anyone help?
I can then link the two tables and print a report - I could probably do this bit.
Thanks for any advice
View 1 Replies
View Related
Feb 13, 2006
Hello Everyone :D :
I have a reporting interface providing users with a multitude of reporting options and capabilities. One aspect involves utilizing the ability to allow users to save their settings on the interface due to the number of options provided. One of the aspects revolves around working with listboxes.
Each listbox is pre-loaded every time with a list of items from a master table pertaining to the listbox. The user selects their desired choices, saves*, etc...Once loading a setting, I'm looking to have the listbox pre-loaded (easy) but then auto highlighting the items selected or rather pulled from the user settings table (each listbox has own table for user saving of options). So, if a user selects items Alpha, Beta, Delta, and Falcon from a list of 100 choices, then those 4 items would be saved under that user setting, within the code table (user specific). When they load the setting back onto the user interface, it should pull the 100 choices and auto highlight the 4 items. Hopefully I've explained that properly.
*A user is able to save, save as, load settings, unload settings, etc...from the reporting interface. When a user saves settings, these settings are saved within the user settings tables. Each table varies depending on the nature of the values within the reporting interface. Listboxes have their own tables connected to the primary settings table.
Any ideas anyone? :rolleyes: :D
View 4 Replies
View Related
Dec 16, 2004
I have what I think is a difficult problem to overcome...
I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.
Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!
If this is not possible, do you know how I can acheive this?
Thanks
S
View 1 Replies
View Related
Oct 19, 2004
What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?
I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.
So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.
Thanks in advance
Todd
View 1 Replies
View Related
Mar 24, 2013
I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
View 3 Replies
View Related
Mar 4, 2015
In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
View 1 Replies
View Related
Oct 28, 2005
Hello All,
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)
View 9 Replies
View Related
Nov 20, 2013
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
View 5 Replies
View Related
Apr 2, 2013
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
View 3 Replies
View Related
Aug 12, 2015
I have a query where these are the fields:
ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
View 4 Replies
View Related
Jul 15, 2014
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies
View Related
Jul 23, 2015
I have a form which will be used as the basis to print a label.
It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.
What I want to do is to update the form/query without updating the underlying tables to the query.
View 14 Replies
View Related
May 31, 2006
:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;
-----------
qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
Thanks for you time! :)
View 1 Replies
View Related
Mar 8, 2005
I'm building a report for annual software license renewals. The report data source is a query that combines the customer information, their computer information, and the licenses purchased for that computer. I am having no trouble with the form displaying the customer info page, then a page with the computer info at the top and a list of licenses purchased for that computer underneath.
That would be great, if that's what my boss wanted. However, she wants the whole list of available licenses displayed on each page, in the event someone want to purchase additional licenses with this year's renewal.
I'm trying to figure out how I can set up a query/report grouping to do that. I've tried making a new query, relating the qryLicense!licenseID to the qryPurchase!purchLicense and setting the relationship option to show all records from qryLicense and only those related from qryPurchase. I added the qryPurchase!purchCPU field to my query, hidden it, and set the criteria to “=1” (the computer ID of one of my dummy computer records). I also have a Sum of the qryPurchase!purchQty field included in the new query that I want to display the total number of that particular license purchased (and 0 if there are no corresponding records). All fields except for the quantity field are set to Group By.
What I’m getting from this is simply a list of the licenses purchased for that computer, not the complete list of licenses available showing the quantity purchased where applicable.
Can someone see where I’m going wrong?
Slaughter
slaughter at mizzou dot edu
View 9 Replies
View Related
