Difficulties With Getting The Right Responce From My Queries
Oct 27, 2005
Hello,
I am having difficulties with working out how to create a correct query from my database. :confused:
These are the two results I require:
What strength of whisky sells the best at Christmas (How do I do an 'In-Between' date condition regardless of the year? i.e. >= #01/12/____# AND <= #31/12____#)
Which companies have not purchased at least one bottle from each region?
However, I am not sure how I would go about getting these specific results from the database. I have done serveral previous questions based on the database (which can be seen in the .mdb file) however these two are proving troublesome. I have attached the database to this post if anyone who is at ease with queries would be kind enough to have a quick peeky at it.
Any help would be greatly appreciated !
Note. the database information is fictisious. :rolleyes:
I've been working on a database for the last month or so. It's been a slow process since I've been learning Access and VBA in the process.
But my supervisor wanted a copy of it as a progress check to send to his boss. So I sent an email with a copy of the database as an attachment.
THe email went through, but when my supervisor tried to open said email, a dialogue came up saying that Access couldn't open the file because it was "out of the intranet or on an insecure site" or something along those lines
I was just wondering what this meant and how I would be able to send my boss a copy of the database so that it can be reviewed and such. Would this require splitting it?
I'm having trouble creating my query I have a table representing the company's products. Ever once in a while the products get replaced by a newer product. Like below _____________________________ Product - Replaces - Replaced By a..................................b --------|----------|----------- b.................a...............e --------|----------|------------ c --------|----------|------------ d ------------------------------ e.................b
Can someone help me write a query so the result below will be shown in three fields.
Product - Replaced by level 1 - Replaced by Level 2-Replaced by level 3 ....a..................b.......................... . e......................and so on
Hi, I’ve been having some problems being able to do some things on my database, I was doing ok creating my system but now I’m a bit confused about what to do and I’ve been trying but getting no where. Firstly on my order form I need a total for the products which are in an subform and after the order is complete I need to be able to create an invoice and keep all the order information in the 'invoice' and 'invoice details' tables. I also need it to update the stock levels after the order is complete. Then I had created a tab in 'view customers' and I wanted to have it so that past customer orders could be viewed. Any help is appreciated; here is a link to my system: 'Gamez System' (http://www.savefile.com/files/5877934)
Hi! I have a problem with my form [EDITAR], the problem is that i did a form basing me on a table, but now i want to create a command that when i press it, it has to let me edit my data. i don't know how to explain me very well. i want to change my data but changing it with my form and save that information.
the other problem is that i want to create the same form for but only to add new data on the same table, i want to create a command that i will press if i want to add new records.... help i really need help:confused:
I have a table with 2 fields (pr_row, pr_col). All i want to do is: I want to update the table so that every 21 records, the pr_col field will increase from 1 to 21 and so on, and the pr_row field will remain with the same value (but increasing by 1 every 21 records) Explaining pr_row-pr_col =========== 1 - 1 1 - 2 1 - 3 . . . . 1 - 21 2 - 1 2 - 2 2 - 3 . . . What is the update query that i must execute?
Hey, I have an access database that imports a certain range of a certain sheet from 30 files all of which are in the same folder and I am trying to get that BOTTOM MOST peice of code to automate the process.
Now one of my many complications was that the excel files are workbook protected, and so it would give the 'cannot decrypt error' that’s why I have all that code regarding unprotecting it... but for some reason its not working
It gets to the line where it gives it the password and it tells me the password is unaccepted, and yes the password is correct...
It must have something to do with the do loop, because it works fine whenever I used it over one import file and thus without the loop... Like this for ex works perfectly fine
So Please tell me what is wrong with this bottom piece of code...
And secondly, I have a table which contains a list of names, whether they are active (checkbox) and their filenames each on a column of its own...
Can anyone show me how i can include into my code (assuming the password problem is fixed) that would allow me to place an if statement that checks whether the user is active or not... IF he is then it imports his file (using the filename column next to his name).... If he is not active then it just goes onto the next person without importing him/her
Right now I have it importing all the files in that folder
Code:Option Compare DatabasePublic xlapp As New Excel.ApplicationPublic Sub ImportAll() Dim strPath As String Dim strFileName As String strPath = "G:CBT" 'Set Path strFileName = Dir(strPath & "*.xls") 'Set first file Do On Error GoTo ErrTrp DoCmd.TransferSpreadsheet acImport, 8, "Test 2", strPath & strFileName, True, "Access_Upload!C13:L34" ErrTrp: If Err.Number = 3161 Then 'Encription error so unprotect workbook xlapp.Visible = False 'Open Excel xlapp.EnableEvents = False 'Disable Events (Macro's) xlapp.workbooks.Open strPath & strFileName 'Open File xlapp.ActiveWorkbook.Unprotect (blah) 'Unprotect 'Try and Import again DoCmd.TransferSpreadsheet acImport, 8, "Test 2", strPath & strFileName, True, "Access_Upload!C13:L34" xlapp.ActiveWorkbook.Save 'Save xlapp.EnableEvents = True 'Enable Events xlapp.ActiveWorkbook.Close 'Close File xlapp.Quit 'Quit Excel Else End If strFileName = Dir() 'look for next file If strFileName = "" Then 'no more files Exit Do End If LoopEnd Sub
Please let me know what I can do with this I am a novice at coding...Just tell me where to put the code
i stumbled upon this site via google while i was trying to find a solution to my problems! i'm a pretty proficient pl/sql writer and this is my first time trying to learn jet sql so a lot of the things i'm used to in pl/sql can't be done in jet sql!
1) i'm trying to create a summary record from RR_FT_COMPONENT to RR_FT_TOT_PAYOUT. this is basically summing some payout fields by grouping by payee_id and period_id. from my research i've seen that jet sql doesn't like sum or group by's for updates. is it the same for doing select statements for insert into?
when i run the query i get the following error: "ms access can't append all the records in the append query. MS access set 0 fields to null due to a type conversion failure"
all of the fields on both tables have the same type. is this a group by and sum problem? i ran the select statement separately and it worked fine.
SELECT max(rc.market), max(rc.region), max(rc.financial_center), rc.payee_id, max(rc.employee_name), max(rc.manager_flg), max(rc.lic_code), max(rc.job_code), rc.period_id, sum(rc.volume), sum(rc.cp_spread), sum(rc.ytd_spread), sum(cp_comm), sum(ytd_comm), max(prior_yr_flag) FROM RR_FT_COMPONENT AS RC GROUP BY rc.payee_id, rc.period_id;
2) in this next code i'm trying to sum spread information in the ft_txn_summary table for a given payee_id, market and period_id. i'm using dsum and have the appropriate joins to keys on the tables, yet this update sums ALL records in the ft_txn_summary table rather than summing the specific payees in the join. any idea on what's wrong or how to debug dsums?
code: UPDATE rr_ft_component AS rc SET rc.cp_spread = Dsum("txn_spread","ft_txn_summary","ft_txn_summary.payee_id= " & [rc.payee_id] AND "ft_txn_summary.market= " & [rc.market] AND "ft_txn_summary.period_id= " & [rc.period_id]) WHERE rc.component_name='Total Revenue';
I am a newbie at Access and am basically designing my first database. This database is for a short term project and is designed to track the attendance of employees at acompany. Here is a brief description of the tables and forms in question.
EmployeeInformation - this table stores records of each employee, their ID, DOB and SIN.
AttendanceProfile- this table stores records of prolonged absence periods for each employee. A given employee can have many attendance profiles, meaning that this table is linked to the EmployeeInformation table via a one-many relationship. This table will provide detailed information about the status and history of each Profile. So for example if an employee misses work for 12 days due to an appendicitis operation, the exact nature and dates of this absence would be listed as a new profile. The primary key for this table is an autonumber field called ProfileID
ActionLog - this table stores the actions taken by company employees (if any) in response to each AttendanceProfile. It is connected to the profiles table via a one-one relationship with ProfileID being the foreign key in this table. Another main field is the ProcedureNum field which will list the number of each procedure taken for a given ActionLog record. This table is needed to track what the company has done/is doing to track an employees prolonged absence.
Allow me to illustrate with an example. An employee, John Doe misses 15 days in June 2001 due to an illness in the family. An AttendanceProfile record is then made for this period illustrating the exact nature of Mr. Doe's absence as well as tracking what official documents he has submitted (eg medical note, official company documents). Another matching record containing the same ProfileID is also made in the ActionLog table describing what the company has done so far to track this absence. So if an HR employee calls Mr Doe's physician to verify this illness or sends a letter requiring further documentation, each of these steps is listed in this log for this absence profile.
Sorry for the long-winded explanation, but better now than to have to clarify myself later. Here is where I'm stuck. I would like to set up two data entry forms, to create new AttendanceProfile records and related ActionLog records. Obviously, these corresponding records are linked directly by the ProfileID field. After completing the AttendanceProfile form, I would like the user to be able to click a command button to open another form (I am avoiding a subform in this case because my attendanceProfile form is huge and I cannot conveniantly fit a subform on it) to open a new form where they can enter Procedure descriptions and dates on the ACtion log. For each new procedure the user enters, an autonumber fields automatically increments the procedureNumber for that specific ProfileID.
Finally, my question to any of you is, assuming that I have already designed the AttendanceProfile table and form, how shall I design the ActionLog table AND forms to accomplish this exact task.
Once again, sorry for the excessive detail. Your assistance is greatlys appreciated.
Im trying to work on an db in Access 2007 that was migrated from Access 2003 (in fact its been migrated several times starting from Access 97). It executes and runs with no problems in both versions. The problem is when trying to open some queries (not all) - Access is unable to open the query in design mode and gives me this error :
" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.
However, as I cant open it - I cant check it. Im pretty sure none of the fields have invalid characters (they do have spaces) and Im not sure how long is too long....
We currently manually run 5 different queries then copy and paste this data into 5 separate tabs on 1 workbook, I'm trying to automate some of this process if possible.
I am trying to use the 'transferspreadsheet' action within a macro to run a query and post it into a template excel file, using this code:
Trasfer Type Export Spreadsheet Type Excel 8-10 Table Name (query Name) FIle Name (FIle location) Has field names No Range Blank ---- This does seem to work and puts the data on a new tab on the specified workbook.
However I have a few questions:
1. Can you specify which query gets put onto which tab in excel? The tabs have different fixed names.
2. Can you specify which Cell the data gets pasted into to? As each tab has a set of headers and titles which need to remain.i.e would need to get query 1 to start in cell A4.
3. How would you expand the above out so that it runs all 5 queries, would you just add in multiple transfer spreadsheet actions in the same macro?
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code: TRANSFORM IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT SELECT PT_LEVEL.INF_YEAR, PT_LEVEL.INF_MONTH, PT_LEVEL.UNIT
I have a crosstab queries which uses the date query parameters. However, when I go to my Export command (code is below), it ask me to enter the date parameters (start date and end date) twice. What do I have to do so that the system will ask me to enter once only?
Code: On Error GoTo Err_cmdTest_Click 'Must 1st set a Reference to the Microsoft Office XX.X Object Library Dim dlgOpen As FileDialog Dim strExportPath As String Const conOBJECT_TO_EXPORT As String = "qryEXPORT"
I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).
My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.What I have is a join query (Which I have named Mech Final Equipment H63 Joined)
Using this SQL:
Code:
SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration FROM [Mech Final Equipment 3 Mth] GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment] UNION
[code]...
Which returns a table that looks like this:
Final Equipment, Duration
Ancillary Equipment, 225 Ancillary Equipment, 401 Ancillary Equipment, 1787 Brush Unit , 1252 Brush Unit , 2519 Brush Unit , 8004
And so on.What I need the table to look like is this
Final Equipment, 3 Mth, 6 Mth, Historical
Ancillary Equipment, 225, 401, 1787 Brush Unit, 1252, 2519, 8004
And so on, like a cross tab.I tried to do a crosstab query but I don't have enough fields.
I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.
Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:
Code: Function CohortQ(InputDate As Date) As Integer If InputDate = 0 Then CohortQ = 0 Exit Function End If
[Code] .....
But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows. If I put the function into the query,
I have two queries, one base upon the other. I would like to combine them (If Possible) into one query so I can embed them into a form or report. I have tried without success at finding the answer on the forum as well as searching the web.
The table lists employee numbers and dates they worked. I need a count of how many employees worked each year, based upon the paycheck date, not the actual date worked. Pay check dates are two weeks apart. An extreme example, is the first pay check date of 2010 was on 1/1/2010, but all the days worked were in 2009, this would have to be included in 2010 not 2009(See the query for further date calculation understanding). Anyway, the date calculations are not the issue here.
I only have one table, so if I am not mistaken, I can't use the WHERE (SELECT... JOIN) feature. I also was unsuccessful at using SQL DISTINCT.
I am running ACCESS 2010 Tables are ACCESS 2007.
OS is Windows 7 Ultimate.
I have included a same database with the queries. qryEmployeesAnually2 is the results I am trying to achieve.
I have a query which returns a calculated percent. I have ordered that in descending order, and now want to see the top 50. So (In Access 2010) I entered 50 into 'Return'. But it returns all of the records!
Is this because pct is a calculated field? How can I correct this? The SQL seems to be correct.
Code:
SELECT TOP 50 HeciFail1.POHECI, HeciFail1.POQTY, HeciFail1.FAILQTY, IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) AS PCT FROM HeciFail1 ORDER BY IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) DESC;
I am working on a report that has some special characteristics.
Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.
Each of these tables has a date that the Complaint, Complement, and Termination notice was received.
Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.
The equation used for that is : (complaints/vendors_in_group)*100
It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.
What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.
Questions: 1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.
2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.
3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.
Using Access 2010. Have been using a simple query to count similar items. All of a sudden I cannot get the sum of the count. I don't know if I have done something wrong or my program won't work correctly.
The DB is attached. The only query shown is an example of what I want to total, but I cannot get any total.
i I have two queries.. What i'm hoping is to combine the result into one query but not in one column only but instead the result of the second query should be beside the first query.. The result of the second query should be added as a new column.
First Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailReceived FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName;
Second Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailProcessed FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName, tbl_rEceived_eMail.ProcessedYN HAVING (((tbl_rEceived_eMail.ProcessedYN)="Y"));
I have 3 select queries which Im trying to output to a combo - Ive tried a UNION query but I get an error
ODBC-- call failed ODBC Driver SQLBase.....
Firstly is do the results need to match within a union query? I mean they have no relationship what so ever Im just trying to populate this combo with the same results.
Secondly is there a better way to do it? 2 of the select queries query a linked SQL table and the third is a local table. All of the select queries work on their own.
I have 5 queries that I am running. The first query has the date range parameters set in the field area that I need to run and each additional create table query is based off the results of the previous query.
1. Which is better to use to run all of the queries in one simple step? A macro or a form? I am exporting the final table to excel so that I can make some additional adjustments off of it.
2. How would I setup the date range parameters for the first query if I were to use a macro without going into the query itself and updating the date field? I tried setting up a macro to run the queries by using the OpenQuery action for each of the 5 queries, but I cannot figure out how to do the date range.
So I run cash flow for a business, and we export data from Oracle and insert it into an access database. I have to run about 25 queries, entering in the same parameters for each. We number each week of the year. So for say the first week in January, I would run the first query and it asks: Beginning Week, I enter in 1, then another paramter value asks me the ending week. I have to enter in these parameters for each of the 25 or so queries, and it becomes quite irritating. Each query has a number of columns, but I am only interested in obtaining the sum of one of the columns, titled Distribution amount. So I am looking for something that will run each of my specified queries, then spit out the total of the distribution column for each in a table like.
Query 1: Total Distribution Query 2: Total Distribution etc....
Is there anything that would allow me to do this, with entering in the week parameter once, say week 1 start, week 1 end. and it use those same parameters for each query?
