Compiling Multi Step Query Into Single Query
Dec 8, 2011
I'm new to access, and have discovered its easy to write large queries by breaking them up into smaller queries and having each subsequent query reference the one before it. My problem now is that I want to combine these queries into a single query, so as to make it easier for anyone who comes after me to edit/understand what was done. I can't seem to find a good explanation of how to do this, but it seems like there should be a way.
View Replies
ADVERTISEMENT
Jan 22, 2006
Hi All,
I have been struggling with what should be a very simple task, at least I would think so. Here is what I have. My customer provides me a rolling 8 week forecast. It looks like this.
Forecast Date|Part|Forecast Week|Quantity
1/1/2006|Part A|Week 1|100
1/1/2006|Part A|Week 2|200
1/1/2006|Part A|Week 3|150
1/1/2006|Part A|Week 4|300
1/1/2006|Part A|Week 5|50
1/1/2006|Part A|Week 6|120
1/1/2006|Part A|Week 7|100
1/1/2006|Part A|Week 8|400
1/8/2006|Part A|Week 2|300
1/8/2006|Part A|Week 3|150
1/8/2006|Part A|Week 4|700
1/8/2006|Part A|Week 5|550
1/8/2006|Part A|Week 6|420
1/8/2006|Part A|Week 7|200
1/8/2006|Part A|Week 8|800
1/8/2006|Part A|Week 9|100
So you can see that the first week is always the closest to the current date, and every week a new forecast comes out. The problem is, there are over 900 rows for each 8 week rolling forecast (lots of parts and ship to locations).
I think the best way to handle this is to use Access Database and query the data. But I can't seem to get the query right.
I would like to be able to compare the closest week's forecast with the actual quantity bought. Which means I need to line up the calendar week of the forecast given date with the calendar week of the forecast week. I have attached an example.
So you know, I have been using pivot tables for a year now, but the excel file is 50mb and I am out of rows...so access would be nice. Plus, once I can get this figured out, I will be able to link it to my consumption table and generate on the fly reports for management.
Thanks for your help!!!
Matt
View 1 Replies
View Related
Jul 26, 2006
I have a switchboard and when I click a certain button ...
it opens a prompt popup.
In the popup you enter the required "item" and you hit enter or "ok"
It then does a search for the specified "item" and brings up
that information.
At that time I would like to have a calculator
pop up for the user to use.
(newb) How in laymans terms do I get this to work ?
In my "forms" Design View I have:
Switchboard and Calculator
In my "Queries" I have query 1
In the tables section I have :
Sheet one (this is were the query is getting its search info from.
Switchboard items
Is there something I can add to my query to get it to open the calculator form ? Or is there some other way without using additional buttons to do this.
View 2 Replies
View Related
May 15, 2014
I am trying to create a form with a button attached to each record that would allow the user to click the button and it would automatically open outlook and fill in the TO:, SUBJECT: and BODY: fields. Here is the code that I currently have:
Code:
Private Sub Command33_Click()
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
[code]...
There are two issues I keep running into:
1. This code opens outlook and populates all of the fields but pastes the email incorrectly. Instead of pasting just the email (email@email.com) it pastes the html tags as well (email@email.com#mailto:email@email.com#) which means that the user would have to delete everything between the #'s in order to send the email every time.
2. I currently have the email BODY pulling from a table but this obviously limits what I can do. I would like to simply encode the BODY within the VBA code. The setup I am looking for is:
one paragraph
a blank line
a hyperlink to a website
a blank line
another paragraph
View 9 Replies
View Related
Sep 7, 2007
I have spent the last couple of days trying to figure out how to make this work.
I have three tables.
tblIntakeMain
[IntakeMainID]
tblIncidentDetails
[IncidentdeatailsID]
tblPersonnel
[PersonnelID]
On the main form I use subforms to link tblIncidentDetails and tblPersonnel to tblIntakeMain. Both subforms can, and do, have many entries. This all works fine. What is not working is the search form I am using.
I am using Gromits most excellent Search Form. The problem is when I create a query, qSearch, to bring together the three tables I get a multiple records which makes the searches very confusing and near useless. Is there anyway around this? Is there something I am missing? Is there another search method I could use that would work in a similar way as Gromits? Please help before the Prozac runs out and I lose my mind--what little it left.
View 5 Replies
View Related
Aug 25, 2005
hi.
in my db i have used in one scenario two separate forms to fill in one record in a respective table.
the user fills out the details in the first form for record 1, presses NEXT, and fills out the remaining details in the second form for record 1.
every time form 1 will be opened, it will need to be at the end of the records (to add a new one of course) so i have used this code in my form 1 open event DoCmd.GoToRecord , , acNewRec
this is fine. however, i have a dilema. when i place that code in form2 also, it skips one further place to the next record(record 2).. and if i dont put any code in form 2, it will always display the record that has just been added previously..
so basically what i need is something to tell form 2 to carry on adding details into the same record that form1 was just on..
how can this be done?
View 4 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
Jan 7, 2004
Hi,
I have a database originally was created for 1 person to use. Now, this database has to be shared by various people in the company over a network. Some are only allowed to view reports while others are allowed to make changes to it. How can I convert this database to a multi-users one, and maintaining its data integrity? I can possibly restrict 1 person to use the database at any one time. I know it's not going to be easy, but with the help of you guys I am sure it can be done.
View 2 Replies
View Related
Jul 20, 2005
I have a series of check boxes that once they are all checked, I need a field to say “Complete”. These are check boxes not option groups.
Thanks
View 2 Replies
View Related
Mar 1, 2006
Hi
I am new to VBA with access. Im wanting to get the result of a query called "qLastRotaDate" into a variable called "datLastRota". The query returns a single date, it is not possible to have more than one result for this query. I have tried different variations of : datLastRota = qLastRotaDate but cannot get any to work. I'm assuming this is very simple yet I cannot figure it out as am very new to VBA.
Any help would be greatly appreciated. Sorry if this has been dealt with in previous posts but I could not find any info by searching the post.
Thanks
Mikee
View 3 Replies
View Related
Oct 23, 2011
I have a table listing employee names and their birthdates. I have to create a single query that returns the names of the oldest employee and youngest employee. it's easy to return only their birthdays, but i can't figure out how to return the names (with or without their birthday). I can only use a single query.
View 2 Replies
View Related
Aug 23, 2005
Working on a sales guest registration database and need a some assistance with a query for my report. I have the following fields in the table:
Communities
RepName
Date1stVisit
MoveTime
I have created a query which works without a problem. I have also created a dialog form to use with a report that I've created. Everything works ok if I fill in all the boxes on my form with data but in some instances they want reports that are not filtered all the way down. On my form I have an unbound combo box for each of the fields above. I've tried several of the suggestions found here but cannot get it to work completely. The last thing I tried was the following criteria for each field I want to filter on:
like [forms]![repdialog]![communities]& "*" or is null
like [forms]![repdialog]![repname]& "*" or is null
>= [forms]![repdialog]![datebeg] and <= [forms]![repdialog]![dateend]
like [forms]![repdialog]![movetime]& "*" or is null
This sort of works but I must be missing something because it gives me too much data. For instance if a just wanted to fill in the REPNAME box on the form it should give me every record that has that name in it but I get more that that - I think because of the is null comments. Any one help me correct this?
Thanks,
Chester Campbell
View 14 Replies
View Related
Sep 28, 2007
I have a query that includes 4 different tables. It only shows info for the Sales Orders that have information in all four tables.
Is there a way to get it to show any information available? For instance, if it only has info in two of those tables, it would show that info and the fields for the other two tables would be blank?
View 5 Replies
View Related
Oct 1, 2007
I have a nine-table database about the people in a certain no-longer-existing East European village. So it revolves around a main table, called PersonLOG, of people who used to live there, each with a unique ID#.
There is another table that list facts about the people: PersonFACTS. This table has, among other fields, three fields for three types of sources. One is called SubmitterID; this is the code for a person who submitted a testimony page attesting to that persons' death in the Holocaust. There is also a text field that a particular "factoid" can be typed into. There may be many fact records about any individual. So there is a one-to-many rel. between the PersonLOG and PersonFACTS.
There is a 3rd table called MAIN, that lists the "vital" information about most of the individuals. It includes one yes/no field indicating whether they died in the Holocaust.
I am trying to create a query that would list only those Persons for whom there 1)is no Submitter and 2)Died in the Holocaust. This requires that all of the records in PersonFACTS pertaining to a given individual be compared, looking for the ABSENCE of a FACT record for that person that has a submitter attribution, and then comparing to "and"-ing that with the yes/no field described. I can't see a way to do that.
My apologies if this question is too complicated.
Thanks!:confused:
View 8 Replies
View Related
May 30, 2006
Hi I Like to know how to aquire the correct results:
Table:
ID - Personalinfo - location - records - date
102 - Dave Blogs - Bristol - Queen - 12.12.03
103 - Brian Smith - Plymouth - SKA - 12.11.03
102 - Dave Blogs - Bristol - Madona - 12.12.03
By selecting the ID I want to show all listings for that ID.
ie:
102
Dave Blogs
Bristol
Queen 12.12.03
Madona 12.12.03
Im going round in circles
View 4 Replies
View Related
Apr 3, 2013
How do I select the first column of a multi-column list box (called "List1") for a query.
A single column list box works fine.
Code:
SELECT Tble_Employee.Emp_No, [forms]![attendee_form]![list1] AS SelectedCourse
FROM Tble_Employee;
View 6 Replies
View Related
May 18, 2005
Hi,
I am trying to run an append query, but instead of appending the whole table, I would like to only append a single record.
I have an append query, and it works like a charm. I can also get it to work with prompting the user for a parameter (in my case LeaseId which is a primary key). When the user is prompted and enters the LeaseId it only appends the single matching record. Works like a charm.
However, I don't want to prompt the user for the paramater. I want the user to generate the LeaseId based on the selections in two combo boxes.
So far the I do get the right LeaseId, but I have no idea how to actually pass the LeaseId to the query. I've tried the following, but I know it's completely wrong.
stDocName = "approveLease"
DoCmd.OpenQuery stDocName, acNormal, acEdit, "[LeaseId]=" & Me.buildingCombo.Column(0)
Any ideas on how to actually accomplish this?
View 12 Replies
View Related
Dec 15, 2005
Hi,
I have a product table in Access, like this:
product_id | product_name | product_quantity
I need to be able to do an SQL update on this table to subtract 1 from the product_quantity field.
I could do this with two SQL queries (SELECT product_quantity, then subtract 1, then UPDATE table) but I'm sure there must be a way just to run one query which subtracts 1 from the field.
Does anyone know how to do this?
Any help would be fantastic, thanks!
View 12 Replies
View Related
Apr 27, 2006
Thanks for taking the time to look at my problem first of all. I have a form that shows records but I want a button next to each record that will append that one single record to another table that is built exactly the same, which is for historic records. Not the brightest on Access and I can only manage to append all records.
As an example, I have an employee record that have a task, when the task is complete I want to append the record to a historic table that I can look up later on. However, just that one employee and not all that are in the current table.
View 6 Replies
View Related
Nov 7, 2006
I have two identical databases in MS Access. I have created a query in the first mdb file, which retrieves the matching records in two tables.
My problem is that i want to execute the same query created in the database1, on database2, without creating the query in the database2.
I think it is possible, but the query i was trying was not working file.
select * from db1.[Query1] in 'C:DBDB2.mdb'
where db1 is the name of the first database,
db2 is the name of the second database and
Query1 is the name of the query in the db1.
Thanks in advance for the help.
View 1 Replies
View Related
Jun 4, 2007
I have 2 calculations in a single query.
1st - ProdCost: [TBLinvqty]*67
2nd - Gross: [TBLinvamt]-[ProdCost]-[TBLfrtamt]-[TBLocamt]
The query runs and works fine but it keeps prompting me for ProdCost but if I hit ok it runs fine.
How do I get it to quit prompting me for Prodcost
BTW, Prodcost is not a field in a table its only the 1st calculation in this query
View 8 Replies
View Related
Aug 15, 2006
Is it possible to display specific Query's in a single subform dependant on a combobox selection? If so how wud i go about doing this?
Any help is much appreciated. Thanx
View 4 Replies
View Related
Sep 12, 2013
I'm having a small problem where Access returns a "Query too complex" error when I try to run a Union query of the form:
Code:
SELECT CourseNumber, CourseTitle, [Date], TotalScore, "Two (2) zero visibility dives?" AS [Question], CountZeroVisibility AS [NegativeResponses]
FROM qryScore
UNION ALL
SELECT CourseNumber, CourseTitle, [Date], TotalScore, "One (1) night dive?" AS [Question], CountNightDive AS [NegativeResponses]
FROM qryScore
UNION ALL
...etc
With more than 13 unions.
View 13 Replies
View Related
Oct 12, 2015
Suppose I have a front end and backend (tables) database which I want to deploye. Currently Access tries to find the backend in the same absolute location (rather than relative position). This causes a problem as drive letters change and sometimes I would want it in a specific location on my computer and another place on deployed computer. Is there anyway to change source of linked tables after compiling to ACCDE
View 3 Replies
View Related
Feb 10, 2006
I have a query which calculates Lagtime: which is the difference between the audit date and the current date.
Lagtime: DateDiff("d",[Auditdate],[Date]) and the criteria is >30
this works fine and shows the reports that are over 30 days overdue.
Now I need: There are 2 fields (both are y/no) Minor and Major. Minor need to be answered >30 and Majors with in >7,
how would I write the expression show me Lagtime if over 30 days for a minor and 7 days for a major.
View 1 Replies
View Related
Sep 7, 2006
I have a database... 1 table...40 fields..
I currently have a query set up as a basic a LIKE search for each field and I have to do 40 each time..
Is there a way of being able to do 1 query and have it search all 40 fields?
So my field headings are
Part No
Description
barcode..
Album name
Singer/Group
and so on...
I since some of the albums are compliations I require to search all the fields...
Any ideas?
View 6 Replies
View Related
