Dynamic Query Generation
Dec 16, 2005
As much as I would like to tell my boss he's insane I'd also like to keep my job. I know this is possible, but it sounds very complicated. Basically, I need a way to generate queries on the fly. This is geared towards someone who doesn't know SQL of course. I know basically what he's looking for, comparing sales over some period of time. However, he might want annual numbers, quarterly numbers, percentages, overages/shortages, and any kind of sales related query you can think of. The only way I figure I can do that is to have a form build the SQL statement, save the SQL statement as a query, then have him open the query. Is there another way I can do this without necessarily saving a query first?
View Replies
ADVERTISEMENT
Jun 30, 2006
I have a form that shows a list of all of my records in my database. I want to be able to click a button called "Report" and have that print a report that has all the records I have filtered on my form. I have a report in the format that i want it in, however, currently it prints every record and not just what is shown on my form. (The form is dynamic and I want the Report to be dynamically based on the form) HELP PLEASE!
View 6 Replies
View Related
Dec 8, 2005
Hi
I have a column which is made up of two parts from the table as follows...
function area .... FINANCE
ID number ... 001, 002, 003 etc
combined together this will make my ID column which should look somehting likie this : FINANCE 001, MARKETING 002 etc
BUT
the problem is that now i need to import some existing data into the tables and it appears as though the existing data has repeating numbers for different function areas
e.g.
FINANCE 001
FINANCE 002
MARKETING 001
MARKETING 002
how would i get this imported into my current table structure?
View 4 Replies
View Related
Aug 7, 2006
I'm rather new to Access, and still learning. Right now, i have 2 tables:Employees (Firstname, Lastname, Dept, Start Date, Inactive (yes/no field), Inactive Date)MainData (tracks daily stats for each of the sales people in 3 depts)i have a form set up for each to input the required data. I also have 2 queries:Active Employees (filters out inactive employees; used for a dropdown menu of employees on the MainData form.MainDataTotals (totals the fields of MainData; Total Customers, Total Sold, etc)What I'd like to do is set up a form to generate a report of the overall totals of all the sales people, totals for the separate depts, and totals for the company. I have the form designed already, and it works, but i do not know how to set it up so i can filter by chosen date. I want to be able to choose which dates the report will cover off of a form. For example, a dropdown menu with the months listed, and have the report only show the totals for that month. I will only need monthly and yearly reports.
I have searched extensively for this, even on these forums, i cannot find the answer to my question or enough info to figure it out.
View 4 Replies
View Related
Sep 3, 2006
hi guys. i am currently working on an human resources information system. i am currently using ms access 2003 and the report form looks like this:
the function of this form, is once a button is clicked, the appropriate report for that button will be generated and will automatically be viewed in ms excel. the problem is that i don't know the VBA code that will perform this function. i read books about access, but unfortunately i can't find the exact answer to my question. so, if anyone could give me some idea on where i can find sample codes for this function, i would very much appreciate it. thanks in advance.
View 4 Replies
View Related
Sep 11, 2006
Hi guys, im new to all this so take it easy
Im trying to create a document register in Access 97 to create and track document numbers, for this i want to assign a 6 number unique id to each record starting at 000001 going up in order to 999999 which will form part of a unique docuement number ...i know this may be a really easy thing to do but can anyone tell me how?
Cheers
Dave
View 1 Replies
View Related
Sep 25, 2006
Hello,
I have this query:
SELECT UNIT.Code, Rnd() AS Expr1
FROM UNIT;
The problem is that in the Expr1 field all the numbers are the same. I would like to have random numbers that are different for each record (generate new random number for each record)
Thanks for your help
Cheers
View 2 Replies
View Related
Sep 3, 2006
hi guys. i am currently working on an human resources information system. i am currently using ms access 2003 and the report form looks like this:http://i114.photobucket.com/albums/n258/lushh_16/reports.jpgthe function of this form, is once a button is clicked, the appropriate report for that button will be generated and will automatically be viewed in ms excel. the problem is that i don't know the VBA code that will perform this function. i read books about access, but unfortunately i can't find the exact answer to my question. so, if anyone could give me some idea on where i can find sample codes for this function, i would very much appreciate it. thanks in advance.
View 4 Replies
View Related
Oct 4, 2006
Hi Guys
Dont quite know if this is the right place to post this but here goes any way
Im creating a database where customers can submit a question, it centres around a form where a user enters various information such as initials, telephone number, and various selections from from down boxes.
I have placed a button at the bottom that saves the record, what i want is when that button is pressed for a box to pop up giving the user a unique reference number which is made up of the information they have entered, such as
FIRSTINITIAL-LASTINITIAL-DATE-INCREMENTALNUMBER
I want this number then to be stored along with the record so it can be searched for by this number.
I have done searches for similar questions but cant find one that matches what i want to do
Thanks in advance
View 2 Replies
View Related
Jun 27, 2006
Hey,
Newbie here,
my frist error message appears as soon as I create a new database. As soon as a I create the name and it opens the new database a message pops up that says "Error Loading DLL" does anyone know what this is? and will it create a problem for me?
Secondly, I have a database that will be used for employee Reviews so the fields I have are as follows: Key, Date, Time, Department, Employee, Topic, Explanation, and Supervisor. I want to be able to generate a report for each employee, however I am not sure how to use the wizard to create this. If anyone knows of some tutorials or report aids I'd love to get my hands on them!
thanks!
~Jennifer
View 1 Replies
View Related
Aug 30, 2006
I have a multi-layered question regarding setting up an access database for fluid samples sent in by a customer.
Ideal Setup:
The database would prompt me for the Sample ID number which is a unique code we give to each sample. It would then prompt me for the customer name and if given an existing customer name it would pull up all the contact information for the customer. It would then prompt me for the machine ID# that the sample came from, from which it will pull up the data from 10 previous reports and place into the current report.
I would then input the current data for the sample after analyzing it. I then have an automated control to convert the report to a .pdf file and send it via outlook. It would also be nice if the report was uploaded onto a website that the customer could access, but this is a whole different issue.
Is this setup possible with Access 03 and if so is it even remotely possible for a newbie like me to create? Additionally, what would the time investment be for a project like this for a beginner? Are there any other more effecients ways or programs to accomplish this level of automation?
Thanks for any help you might lend!
View 1 Replies
View Related
Feb 7, 2007
I have a form where a user chooses different criteria from listboxes and then I run out and format the SQL statement.
This is the query that I am trying to replicate using VBA code. Note: the Location_ID, Ship_Day, and Final_Dest come from the values in the listboxes.
I am getting a syntax error and I cannot find it anywhere. Also, is there an easier way to do this?
SELECT [(Table) Denton Routing].LOCATION_ID
, [(Table) Location].NAME
, [(Table) Location].CITY
, [(Table) Location].STATE
, [(Table) Location].REGION
, [(Table) Denton Routing].UNIQUE_LANE_ID
, [(Table) Denton Routing].CARRIER_ID
, [(Table) Denton Routing].[SHIP DAY]
, [(Table) Denton Routing].[DELIVERY DAY]
, [(Table) Denton Routing].[TIME AT LOCATION]
, [(Table) Denton Routing].STOP_NUM
, [(Table) Denton Routing].NO_OFF_STOPS
FROM [(Table) Location] INNER JOIN [(Table) Denton Routing] ON [(Table) Location].[LOCATION ID] = [(Table) Denton Routing].LOCATION_ID
WHERE ((([(Table) Denton Routing].UNIQUE_LANE_ID) In (SELECT UNIQUE_LANE_ID
FROM [(Table) Denton Routing]
Where [(Table) Denton Routing].Location_ID = "13176AA"))
AND (([(Table) Denton Routing].[SHIP DAY])="MONDAY"
AND [(Table) Denton Routing].Final_Dest = "DENTON"));
Here's the code:
Private Sub cmdRunQuery_Click()
Dim Db As DAO.Database
Dim QD As QueryDef
Dim where As Variant
Set Db = CurrentDb()
' Delete the existing dynamic query; trap the error if the query does
' not exist.
'On Error Resume Next
'Db.QueryDefs.Delete ("Dynamic_Query")
'On Error GoTo 0
where = Null
where = "WHERE ((([(Table) Denton Routing].UNIQUE_LANE_ID) In (SELECT UNIQUE_LANE_ID FROM [(Table) Denton Routing] where"
where = where & " [(Table) Denton Routing].[Location_ID]= '" + Me![Text35] + "'"
where = where & " AND [(Table) Denton Routing].[Final_Dest]= '" + Me![List29] + "'"
where = where & " AND [(Table) Denton Routing].[Ship Day]= '" + Me![Combo46] + "'))"
MsgBox (where)
Set QD = Db.CreateQueryDef("Dynamic_Query", _
"Select SELECT [(Table) Denton Routing].LOCATION_ID, [(Table) Location].NAME, [(Table) Location].CITY, [(Table) Location].STATE, " & _
" [(Table) Location].REGION, [(Table) Denton Routing].UNIQUE_LANE_ID, [(Table) Denton Routing].CARRIER_ID, [(Table) Denton Routing].[SHIP DAY], " & _
" [(Table) Denton Routing].[DELIVERY DAY], [(Table) Denton Routing].[TIME AT LOCATION], [(Table) Denton Routing].STOP_NUM, " & _
" [(Table) Denton Routing].NO_OFF_STOPS FROM [(Table) Location] INNER JOIN [(Table) Denton Routing] ON [(Table) Location].[LOCATION ID] = " & _
" [(Table) Denton Routing].LOCATION_ID " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"
End Sub
View 5 Replies
View Related
Jun 14, 2006
Dear all,
I want to generate a different drop down box dependent upon the criteria of another selection on a form.
For example, when the user selects "fish" from the drop down, the query criteria lists only those animals whose animal type is fish. If the user selects "reptile" from the first drop down, the query criteria changes, so the next drop down box lists those animals whose animal type is reptile.
To summarise:
Choices in drop down 1:
Fish
Reptile
Choices in drop down 2:
If Drop down 1 = Fish
Cod
Haddock
Shark
If Drop down 1 = reptile
Snake
Lizard
Is this possible, and how do I go about structuring it.
Thanks,
Steve
View 1 Replies
View Related
Jun 14, 2006
Dear all,
I want to generate a different drop down box dependent upon the criteria of another selection on a form.
For example, when the user selects "fish" from the drop down, the query criteria lists only those animals whose animal type is fish. If the user selects "reptile" from the first drop down, the query criteria changes, so the next drop down box lists those animals whose animal type is reptile.
To summarise:
Choices in drop down 1:
Fish
Reptile
Choices in drop down 2:
If Drop down 1 = Fish
Cod
Haddock
Shark
If Drop down 1 = reptile
Snake
Lizard
Is this possible, and how do I go about structuring it.
Thanks,
Steve
View 1 Replies
View Related
Jun 14, 2006
Dear all,
I want to generate a different drop down box dependent upon the criteria of another selection on a form.
For example, when the user selects "fish" from the drop down, the query criteria lists only those animals whose animal type is fish. If the user selects "reptile" from the first drop down, the query criteria changes, so the next drop down box lists those animals whose animal type is reptile.
To summarise:
Choices in drop down 1:
Fish
Reptile
Choices in drop down 2:
If Drop down 1 = Fish
Cod
Haddock
Shark
If Drop down 1 = reptile
Snake
Lizard
Is this possible, and how do I go about structuring it.
Thanks,
Steve
View 1 Replies
View Related
Jun 14, 2006
Hi,
I have the following query:
SELECT *
FROM qryForCreateReportSold
WHERE (((qryForCreateReportSold.Fund) Like '*STP*') AND ((qryForCreateReportSold.[Sale Date])>=#7/1/2005# And (qryForCreateReportSold.[Sale Date])<=#6/14/2006#));
The user fills out a form and hits run query and the value STP gets passed into the query criteria.
My situation is that there are claaifications such as S/STP, E/STP etc and the problem is all these projects also show up whereas I want projects ONLY
under the STP classification.
I guess the problem is due to the fact of the *STP* which means pick all records that have an STP in them...
If I manually get into the query and remove the * * then the projects ONLY with STP show up.( which is exactly what I need)
Can some one suggest something to remedy the situation.
Thanks
View 4 Replies
View Related
Jul 19, 2005
After reading some of what people already had in this forum, I was still left a little confused. Say I have a form with three combo boxes and I want the user to be able to select something from all 3 or just one at a time. Which kind of query should I be using. Right now I have a select query, but it doesn't work right due to the Where Clause, which will work 1 way or the other, but not both(that I know of). Any advice would be appreciated. Thanks.
Bobby
View 2 Replies
View Related
Dec 7, 2005
My boss asked me to do something today that stumped me. He wanted me to make a query, where when you're selecting the parameters you could chose whether or not you wanted certain fields to be visible.
On top of that I need to be able to chose whether I want it to be shown on screen in a query, printed as a report, or exported to excel. This is what makes it tricky. I could easily do a bunch of If...then statements to generate the SQL query and only show the fields necessary, however the report and the query view both have the fields on them in design view. If I cut the fields out of the query at runtime, then I get the nasty "Input parameter" popup box.
So I've been bashing my head off the keyboard trying to figure out how I'm going to do this. One other theory I tried to put into use was having them left on the form/report, but if the checkboxes were not selected then it would set them to visible = false at runtime. Sounds good in theory, however Access decided to ignore this block of code and just show the fields regardless of what I told it to do.
So if anyone could provide me with some insight or suggestions, I would certainly appreciate it.
Thanks in advance!
View 1 Replies
View Related
Aug 24, 2006
Not sure if this is possible but I figured I'd ask.
I currently have a form/update query that allows me to change a persons last name in a record depending on the value I enter in an unbound text box on the form.
Is it possible to make this query dynamic so I change field names on the fly instead of it programmed for lastname. Example, I want to change firstname instead of lastname.
Would I need another query for firstnames?
View 3 Replies
View Related
Sep 13, 2007
Hello Access Expert
Is it possible to call a function as the criteria for a field in a query.
I have used a function that returns a boolean into the criteria field and it worked but when I create a string expression for the criteria field it doesn't seem to work.
For example I have created this simple function to generate a criteria
Public Function AcceptedDays() As Variant
Dim Days As String
Days = ""
If Forms![DrillDown]![Sunday] Then
Days = "1"
End If
If Forms![DrillDown]![Monday] Then
If Days = "" Then
Days = "2"
Else
Days = Days + " OR 2"
End If
End If
If Forms![DrillDown]![Tuesday] Then
If Days = "" Then
Days = "3"
Else
Days = Days + " OR 3"
End If
End If
If Forms![DrillDown]![Wednesday] Then
If Days = "" Then
Days = "4"
Else
Days = Days + " OR 4"
End If
End If
If Forms![DrillDown]![Thursday] Then
If Days = "" Then
Days = "5"
Else
Days = Days + " OR 5"
End If
End If
If Forms![DrillDown]![Friday] Then
If Days = "" Then
Days = "6"
Else
Days = Days + " OR 6"
End If
End If
If Forms![DrillDown]![Saturday] Then
If Days = "" Then
Days = "7"
Else
Days = Days + " OR 7"
End If
End If
AcceptedDays = Days
End Function
If however, I literarly type what the function produces into the query grid field the query generates the correct result. On the other hand if I send the result of the above function the query doesn't work. Any explanations ?
Thanks so much.
View 3 Replies
View Related
Dec 15, 2004
Hi all,
I’m trying to create a report with dynamics columns generated by a crosstab query. I have set 4 unbound text boxes for header and detail sections on the report. The column heading from the crosstab query could have 1(Column), 2 or 3 different values, which will be my heading in the report. I want to be able to show 1(Column) if it has a valued and hide the rest 2, and 3 heading. Now column 4 headers will be my Total heading as well for values, which will be coming from the sum of 1,2 and 3 if they happened to have values.
Can anyone help on this please, and let me know if I should bring some more information to you guys.
Thanks so much.
View 2 Replies
View Related
May 5, 2005
:confused:
Hi,
I would like how I can generate automatic Id in a table with following structure. billid number, billdate date& time.
I don't like to use AutoNumber built in feature.
Regards.
Soumen.
View 1 Replies
View Related
Aug 14, 2014
I have a database where my team will enter manual payment calculations into. Once entered, they will run and print the report for actual payment.
The report I have groups by payment type (see attached image of paymetn types) and then sub totals by group.
I need to somehow get these totals and use them to generate a gross payment. In the attached example, the gross would be the sum of worked hours + before tax allowance + after tax allowance. I'm not sure how I can do this in the group footer.
I have attached images.
View 1 Replies
View Related
Jul 29, 2013
I am trying to automate the generation of a reference number incrementally by 1.
In my main table (tblBooking) I have my primary key field autBookingID which is an autonumber.
In another table (tblBookingStops) I have the primary key as autBookingStopsID as the autonumber and then numBookingID linked to the above table (tblBooking). I have another field called txtGPSID so this is the number I would like to automate.
So for example:
tblBooking:
autBookingID: 1234
tblBookingStops:
[Code]....
I want to put the code that would populate the txtGPSID on a form in datasheet view and would like to put the code on the load event.
View 5 Replies
View Related
Nov 13, 2012
I want to input a number value in [field1], then i want [field2] to generate an auto number BUT i want it to increase based on [field1] THEN combine them into [field3] creating a unique SKU for every item I have.
EXAMPLE1: I will type in [field1]: "001234". [field2]will enter "000001" because it is the first occurrence of "001234" in [field1]. [Field3] will then have a value of "001234-00001"
EXAMPLE2: I will type in [field1]: "002468". [field2]will enter "000001" because it is the first occurrence of "002468" in [field1]. [Field3] will then have a value of "002468-00001"
EXAMPLE3: I will type in [field1]: "001234". [field2]will enter "000002" because it is the second occurrence of "001234" in [field1]. [Field3] will then have a value of "001234-00002"
View 4 Replies
View Related
Feb 11, 2007
Hi, I'm building a form where the user can choose various options to build up the query they want. It's going to be easier this way because the different combinations they might want would mean me writing well over 100 different queries. So I think I can build the sql statement in vb and then get a recordset back with the results, but the question is how to display it. I can't just assign the recordset to an existing form because the fields to be displayed will be different for different options. Ideally I'd like it to open just as if I had run:
DoCmd.OpenQuery <name of the query>
But it doesn't look like this is going to work for a query I've generated in the code, even if I use a querydef object (correct me if I'm wrong?). So does anyone know how I would go about doing this? (It would also be fine if the results appeared in a subform on the form I'm calling the query from, but I can't see how to do this either).
Any help gratefully appreciated.
Sim Bamford
View 2 Replies
View Related
