Hope this is the correct forum, and I am not breaking any rules of etiquette. :rolleyes:
I am after some guidance on how to start to tackle a problem, rather than any advice on the details of how to do it. That I want to work out for myself in order to try and improve my mastery of Access.
I have a team responsible for completing a number of scheduled reports.
These reports are scheduled for different frequecies ie :-
some are scheduled Weekly on a particular day,
some are scheduled Monthly on a particular date,
some are scheduled Monthly say on the 1st Monday of the month.
My goal, amongst others, is to be able to, from the switchboard,:-
a. Print a list of reports scheduled to run in the current work week.
b. Print a list of reports scheduled to run between any 2 inputted dates.
This is just part of what I want to acheive from this database, but as we all know Rome wasn't built in a day!! ;)
I have a very simple database for recording data collected from a questionnaire. The database comprises of
table1: QUESTIONNAIRE fields: QUESTIONNAIRE ID QUEST_1 ........ up to QUEST_75
table2: VALUE (1 to 6) ANSWER (strongly agree, agree etc)
All of the 75 question fields in table1 record the same information, and all look up to a table2 which stores the 6 values: agree (1), strongly agree (2), neither (3), disagree (4), strongly disagree (5) and spoilt (6). As the data is being entered into a from using option groups, the database stores the value rather than the text in each QUEST field.
Pretty straightforward I thought, but when I come to query the database I am getting into such a muddle I feel like my head is going to explode!!
The results I need to produce should look like this:
Basically all I want is a count of the number of answers for each question! Given that the answers for all the questions are the same I thought this would be a piece of cake, but alas no.
I thought I would simply be able to crosstab my results from the questionaire into a new table, which would have the six rows (agree, strongly agree etc) and the questions as fields. The values should be the count of "agree" etc. I can quite happly have one field and the counts of each answer but when I try an add any other fields it just duplicates the counts!! I have sat staring at my query and am at a complete blank as to how I resolve this seemly simplistic problem :confused: .
Please if anyone has any ideas why I am in such a muddle over something which seems so simple I would really appreciate their help!
This post is a bit long, but it is actually really simple. I have two tables(DEDPARM1 and DEDETAIL1). I ran a “CREATE TABLE” query using these two tables, to create a NEW table called “Biweekly_Temp_Table.”
DEDETAIL1 sample data SSN | Name | Type | Amount 555-99-8888 | Joe | FAE | 1000 555-99-8888 | Joe | FAR | 20
BIWEEKLY_TEMP_TABLE results SSN | Name | Type| faeAmount | farAmount 555-99-8888 | Joe | FAE | 1000 | Null 555-99-8888 | Joe | FAR | Null | 20 This result makes sense, b/c it is what I wanted.
Now here is the problem. I ran another query(qrySingleRecord) against the BIWEEKLY_TEMP_TABLE to combine the TWO records into ONE. It works, BUT the faeAmount will equal 2000 and the farAmount equals 40. Why is Access doing this? I know something called Cartesian Product will occur if I query against both DEDPARM1 and DEDETAIL1. However, in this case I am NOT querying against these two tables; the whole purpose of creating a new table(BIWEEKLY_TEMP_TABLE) is to try to prevent Cartesian Product. Please help.
Ive got a form, that has inputs to a table, and it also has 2 subforms that link to another table, the subforms are different forms but they link to the same table, this is bacause I need to be able to enter two different items off the table that may be different items, the reason Ive used subforms is because there is the need to have the part number for the item, as well as the weight, this will be a growing list as the Dbase expands. now I have the two subforms with a combobox for part number, and the weight field looks up because of the combo,
back on the main form I need a entry that adds up the 2 weight fields, and enters them on the main table that the main form writes data to, can someone help?
Ive been heatbutting my monitor for a week or two now, and my limited know how on Access is beginning to show :-)
I have made a form based on related tables. it requires me to fill out every field, which I don't want. I didn't make them required. Why does it do that?
Commission: IIf([Amt]<=10000,30), IIf(Int([Amt]/1000 Between 10000 And 50001)*3.5+3.75)
What I required to do is that when amount is greater than or equal to 10000 than Amt multiply by 30 simply. and when amount is between 10000to 50001 than Amt/1000 multiply by 3.5+3.75
means that when amount is 15000 than according to per 1000 it will be calculated.
please dear help me out in this I required it very very urgently.
I am fairly new to Access and may very well be trying to walk before I can run.
I have an Excel Spreadsheet that is currently used to produce a Nominal Roll that is listed by the following:
Surname Enlisted Month Enlisted Year
POP Month POP Year
I know how to transfer it across to Access.
I then need to be able to produce lists (The listings order needs to be Enlisted Month/Year then POP Month/Year) that can then be merged into tables 4 columns wide (where at the top of each table is a General Comment) and the names run down in alphabetical order then move across to the next column. (Basically for example I have 24 names that needs to be divided by 4)
hopefully that is as clear as mud, any help would be greatly appriciated
ive never done normalisation before, and read online a bit about it but cant for the life of me understand it. anyone willing to give me a little help. on converting the above to 1NF, 2NF and 3NF?
Hi. I want to have all my fields on my form to be required and so that the user cant close the form if they are empty (error message). In the table properties I have them set to Required an have tried putting in a validation rule of IS NOT NULL but it still dont work. any help appreciated. thanks
First of all sorry for the stupid question. I am opening a new business and will recieve a specializes CMS system using Access as the DB. Do I have to buy Access to run it?
How do I set the Required property for field that already exists via SQL. I'm thinking along the lines of: ALTER TABLE table1 ALTER COLUMN field1 text(50) NOT REQUIRED.
I'm having problems with the required field option in my table. I have a form that writes to the table and has a validation of the fields built in the code. If the validation is true, then it will allow the record to be saved.
However, I've also set those same fields in the table to be a required field. Whenever I enter data in those fields in the form and click the save command button, I get a message back telling me one of the required fields cannot be null. The thing is, it is not null. I entered data into the field through the form. Whenever I change the required field option to no in the table the problem goes away, but shows up on the very next field in the list that is set to yes.
Validation occurs within the form that writes to the table, but I still would like to have the security of knowing the fields are required in the table as well, just in case someone tries to be sneaky and go directly into the table to enter/modify data.
Is there something I'm missing? Is this a bug? I've actually set up another database the same way and don't have any problems with it. I'm not sure what's going on. Has anyone else had this problem?
I want an access database to manage my correspondence after my motorbike accident.
I am sort of unsure on tables and correct normalisation so here's my plan so far.
Table 1 Company address details
Table 2 Contact name details (I have a lot of different people from different departments in each company) so a contactID to table 1
Table 3 Media Type - letter, email, phone call etc
Table 4 Category - Legal, Financial etc
Also I want to link pdf images to each entry.
Does this sound okay or can tables 1 and 2 be merged? dunno if it would be easier to add names to an existing company rather than possibly adding the whole thing again.
After researching this issue I have not found a satisfactory solution to this issue.
I currently have 1 Access query that is the basis for my VBA code_ Sql statement. both Query statements work when debugged. However, I am getting this error on execution of the sql statement in my vba.
Dies here: '<<<<<<<<<<<<<<<<
Public Sub ConnectCMIS(spar As String)
Dim sConn As String Dim oConn As ADODB.Connection Dim lCnt As Long Dim sSql, strSQL As String Dim rstOra As ADODB.Recordset Dim rsAccess As New ADODB.Recordset Dim fld As ADODB.Field
Do While rsAccess.EOF = False rstOra.AddNew 'Then where you add the Oracle record instead of individual assignments you have On Error Resume Next For Each fld In rsAccess.Fields rstOra(fld.Name).Value = fld.Value Next rstOra.Update rsAccess.MoveNext Loop End If
strSQL = "UPDATE CMIS.UDV_RFS_SR SET PROCESSED_IND = 'S' WHERE job_group = '" & spar & "'" oConn.Execute strSQL, lCnt
DoCmd.RunSQL _ ("UPDATE TA_SR SET PROCESSED_IND = 'S' WHERE Job_Group='" & spar & "'")
rstOra.Close Set rstOra = Nothing oConn.Close Set oConn = Nothing Call MsgBox("Submittal to CMIS has been processed.", vbInformation, "Process Submittal Complete")
SubExit: On Error Resume Next If Not oConn Is Nothing Then Set oConn = Nothing End If rsAccess.Close Set rsAccess = Nothing Exit Sub
Commission: IIf([Amt]<=10000,30), IIf(Int([Amt]/1000 Between 10000 And 50001)*3.5+3.75)
What I required to do is that when amount is greater than or equal to 10000 than Amt multiply by 30 simply. and when amount is between 10000to 50001 than Amt/1000 multiply by 3.5+3.75
means that when amount is 15000 than according to per 1000 it will be calculated.
please dear help me out in this I required it very very urgently.
I need to see the records of a specific date very often. I have designed a simple query (Field : My date field; Criteria : Enter the Date) and it is giving info I want. However, sometimes it shows no records making me wonder where all the data had vanished. On checking up further, I discover that the date I specified was a Sunday, a holiday, when no data entry takes place. What I now want is that if the query returns no records, it should also give a message that "the date you specified could be a holiday. please check" so that I would not panic. Is it possible? Alternatively, the query can look up the day of the specified date and return the message. I shall be grateful for help for any of these query designs.
Hi, I found a couple of short threads on the forum discussing the above. None of them go sufficiently far enough into detal to help me solve my (relatively simple) problem.
My boss has asked me to produce both mean and mode calculations for the number of pallets we ship to our branches on the 30+ routes that we service.
So far I have managed to implement mean but mode seems to have me stumped.
I'm aware there actually isn't a mode function in Access as such but like mode, is there a way to calculate the most frequent occurrence of a number over a time period?
My query is already producing Min, Max, Sum (and now mean) results for the pallets we ship, can someone offer me help on this?
All the code that I have retrieved so far seems to be based on something similar to this:
select top 1 with ties your_column from your_table group by your_column order by count(*) desc
I have very little to no idea about how to implement this into my query. Any help would be appreciated.
Is there any way that you can have required fields in a form, but access will not look for the required fields until you try to exit the form. Basically; i have a form that users keep track of there work hours on specific parts. they leave the form minimized for most of the day. After they enter a record, they sometimes need to go back and edit a previous record, only they are on a new record and access will not let them go to a previous record unless they have the record fields entered.
I have a form that looks up data through a query. The form works but after it queries and displays the data I get this error "424 Object Required" What does this mean. Here is my code if need be
Hi. I want to have all my fields on my form to be required and so that the user cant close the form if they are empty (error message). In the table properties I have them set to Required an have tried putting in a validation rule of IS NOT NULL but it still dont work. any help appreciated. thanks
I am a user of Access 2007. I have desinged a access tabel that have these fields.
Gross amount Discount Net amout 25000 2000 23000
Then I design a form using form wizard.The form have desined easily and when I enter data in form fields the records were updated in the tabel.But when I use the Expression builder with this formula in Net Amount Text Box.[Gross Amount]- [Discount] and open the form and enter data in Gross amount and discount field, It shows result in Net amount field but not updated the Access table.This is the problem.
If any one help me, I will be higly thankful to him
hi. I am trying to write a code to sum the total cost of all jobs that are selected as 'yes' in a combo box.
Once the user selects 'yes' in a combo box, they enter in a cost for that specific job. Each client may have 10s of jobs. I need to write a code that will sum up all the 'yes' selected jobs for a client. I know it should be an 'if' statement but I can't seem to get it to work. I need this value for a report. How would I do this and where would i put the code.
I am relatively new to Access having created my first database a few months ago. I have hit a problem and I am not sure if I can solve this with queries or I need to move to Visual Basic.
I want to create a table containing the stock a product for each day for the next sixty days. I have the current stock in one table, a monthly forecast of sales in another and purchase orders in another.
I want to use the monthly forecast divided by how many days in that month and move forward day by day allowing for purchases coming in.
I was wondering if anyone can help with this code. I am sure it is something simple. It works fine until the last line the (x1down) line. I am not sure what I am missing there. I got that code by recording a macro in excel. It simulates the shift/end/down keystroke which will select all fields that are in the same condition (blank or containing data) as the cell you start at.
The error I get is runtime error '424' - object required
Anyway...hope you can help. Thanks.
Dim opensheet As Object
Set opensheet = GetObject("\Netstore rainingdocsRobDataopen.xls")
