Please Help. I am tring to get the Month to date, year to date and week to date total in a query. I added a parameter for a date range. Instead of giving me the mtd, wtd, ytd, I get the whole months daily total. Can someone tell me what query caculation do I need to just get one number instead of daily numbers?
View RepliesI am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
Hi,
1) I am pretty newbie to this access programming, do forgive me if my questions sounds stupid.
2) Basically I create an application in access capturing or production information for my company. now the top management suddenly wanted whats their main concern:- Total Daily/Monthly, Quarterly, Annual Sales (By Model If possible)
3) I start with daily (Lets don't be too overly ambitious).
4) I try to let user select dates from my calender control and reflect daily sales (in Total & By Model break down) insert into my form.
5) Understand someone told me from my previous post in Calender control I can achieve it either through forms or queries, which is a better way. (in terms of flexibility to change for program maintenance/ scalibility) wise ?
PS: Please forgive my ignorance :o:(
Thanks (In advance) & God Bless.
I have a basic Access Form that contains a small number of questions.
Question 4 is a Yes / No combo box
If the user enters Yes, I want the focus to go to Question 5
If the user enters No, I want the focus to jump to Question 4.1
Completing question 4.1 will take the focus to question 5
I'll be hornswaggled if I can figure out how to do this.
I would love someone to help me out!
:confused:
I have a receipts table with columns- Customer ID, Receipt #, Amount Paid, Date and in another table- customer Id, First name, Last name, Contact # ,Total Cost. How can I get the balance owing by a customer who have made several payments.
No question is stupid
Hi all,
Just put some buttons together, and I notice when I click on them I get a box appear on the button itself. Strangely, some of the boxes are solid likes and some are dashed.
Just wondered what they are, and how to change/get rid of them.
See sample below
Thanks
Col
Hi, totally new on MS access. Is it possible to solve this following task in Access. (using it in Excel today)
Ex.
2d table?
Prod 1 Prod 2 Prod 3
Prod 1 0 10 20
Prod 2 10 0 30
Prod 3 20 30 0
Question 1: What did you just create?
Answer: Prod 1
Question 2: What will you create?
Answer: Prod 3
Then Access will show 30.
Probably explaning it bad but hope you understand and can answer me if this is possible to solve in Access....
The mission is to create a database with setup times between diffrent products, and make it possible to ask a question to get the answer.
/Martin
I've been here for two months and answered over 400 questions. I'm seeing a pattern here, and I have decided to post the best ways to make sure you are never answered.10) Blood, sweat, and tears are for losers. Spend no time figuring things out on your own.9) Announce almost ceremoniously that you have no VBA experience.8) Fail to use the search functions of this forum.7) Try to put every example you're given into a macro.6) Believe that this is the only source for answers, because Access Help provides nothing. Nothing, I tell you!5) Have us write your homework for you and then complain when you don't understand it.4) Post in German.3) Have a belief that your particular problem is the most important issue ever, and post in every sub-forum possible. Then bump it.2) Be vague, and then be belligerent.1) Have no appreciation for the people that are answering your questions in the first place. I know this sounds cold and mean and otherwise uncaring, but we are here to answer questions, not give you fish, as it were. This is a place to say, "I've done most of the work, but I need help over this hump," not "I have this crazy project/idea and I want you to write it for me." I've written over a dozen example DBs for people, and I know that I know what I'm doing. But, myself along with perhaps the majority of others that answer the majority of the questions, I'm asking on their behalf to please take the time to figure some of this out. If you can't do VBA, you are using 25% maybe of Access. If you can't understand your own DB, then asking us to fix it is a solution, but not a learning opportunity for you. If you can't understand what I'm getting at, it's that so many posts are the same. Again, I'm not trying to be an ass, but if you want to be a decent Access programmer and a programmer in general, then please, take the time to learn it. There are plenty of books (look at the bottom of this forum) and there are plenty of resources. I don't think any of us are teachers by trade, but the best teacher for something like programming is always trial and error. Programming is a language, a passion, a "something you figure out" if you will. If you're spoon-fed the entire time, you don't learn. I will continue to answer as I always do and I apologize if anyone was offended, but jeez, make sure you've tried everything offline before asking for help. And don't forget to search first, both here and Google.Sorry for the long read. :)
View 14 Replies View RelatedHi Guys
I currently have a database and the powers at be want me to enable a button that will count how many times in the database a particular month is used. So for example how many times did we visit a particular place in September/october etc.
Is there a way of doing this that i can't see, I have tried searching the forum but to no avail.
my thanks in advance
Anthony
I have created a basic database but now need it to perform some difficult tasks (Well difficult to me) an havent a clue how to do this.
I need to be able to search the database for a vehicle type for a specific dat to see whether it is available or not?
and i need to be able to produc a list of vehicle & drivers that are not booked by entering a date?
Has anybody got any ideas on how to do this because its racking my brains
Thanks
Why so many repeats of the same answer
Thank you MWalt for your reply about finding the stock in hand. It works, sort of. It gives the answer but I end up with each item being repeated about 141 times. So instead of ending up with a list of about 140 items, I have a list of 20000+ because each item is repeated. I'll include the code of the first query that you suggested called qryNotItem...where do I go from here.
This part works, but when I do the second part I run into an error.
SELECT [Equipment Inventory].EquipmentID, [Equipment Inventory].[OT Equipment type], [Loan details].LoanOccID, [Loan details].[Return date]
FROM [Equipment Inventory] INNER JOIN [Loan details] ON [Equipment Inventory].EquipmentID = [Loan details].EquipmentID
WHERE ((([Loan details].[Return date]) Is Null));
Thanks
Hello, I am new here. I am practicing with a Certiport CD
to take a MOS exam and I am having a hard time with one
question on the practice test. The question seems to be asking
me to match output items to input items. There are four items
listed in middle column - they are: datatable, mailing list, report,
and form. I am supposed to (I think), move items from the list
that I think are "input" items in a left-sided column labeled
input, and put the items that I think match the items in the
input column, in a right sided column labeled "output". The middle
column would be left empty and the side columns should have
items that match. For instance, I have been putting "form" in the
input column, and to match it, I put mailing list in the output
column (you can get mailing lists from forms right??) No matter what
I do, it says the answer is wrong. Am I doing something wrong? I
tried all the combinations I can think of and nothing works. I called
the company that made the CD and they said they could not help me.
Could I be matching up the four items incorrectly. Could the question
be worded improperly? Anyone who can help me, please do. Help! thank you.
Hi,
I don't know why, but my Microsoft Access 2000 Help's Answer Wizard and Index Tabs are completely empty! These pages are completely blank, with no dialog boxes or anything. How can I fix this?
Thanks,
Jean-Guy
Hello,
I have a question. Is that possible to find a number of days between two dates BUT if those two days are from different record? (if we assume that dates are in ascending order) (uk date format)
For example:
StartDateEndDate
Record 1 01/02/0728/02/07
Record 218/03/0725/03/07
And now find number of days between EndDate from Record 1 and StartDate from Record 2??
Any ideas??
Thanks!
I am fairly new to access and am curious if this is possible. I have a x number of procedures, I will use 4 as an example, with those 4 procedures I have a reference table that has certain devices for those 4 procedures that need to be compared against the device charges. ie
Procedure
1A
2B
3C
4D
Procedure Device Reference
1A has AAA,BBB,CCC,DDD
2B has AAA,EEE,FFF,GGG
3C has AAA,HHH,III,JJJ
4D has FFF,KKK,LLL,MMM
Device Charges has
AAA,III.LLL.ZZZ
Is there maybe a case statement that I can use to have access try to solve for the best combination of the devices with the procedure to give me the most matches possible and not match say AAA with 3C and LLL with 4D giving 2 out of 4 matches, instead of the result AAA with 1A,III with 3C, and LLL with 4D, and flagging ZZZ as not a match or it matches 3 out of 4
Im not sure if this is the best place for this but any help is greatly appreciated and if more information is needed please let me know thank you for your time in advance
:confused: Hi, I'm pretty new to this VBA stuff and do not know the correct foramt to access a table.
I have a form based on table1 which has a large number of records each with a unique primary key.
Very occasionally I want to add a new record and want to create a primary key within a specific range.
I don't want to use autonumber, so I have a single control record in a table (tblParams) with the LastRecordNo stored in it.
I want to programatically access this record along the lines of:
Private Sub Form_Current()
Dim NewID as Integer
If isnull(me.ID) then
NewID = tblparams.LastRecordNo.Value
me.ID = NewID + 1
tblparams.LastRecordNo.value = me.ID
End If
End Sub
Obviously this does not work, what should be the correct context please.
Hi
I am trying to create a question and answer database that will hold agent scores for future reference.
My idea is that I will have the following tables:
•Team
•Marker
•Agent
•Scorecard
•Question & Score
I want to have a Scorecard form which the marker completes based on the agents performance. The key requirement of this database ability to change the questions periodically and if necessary amend the score applicable to each question. This is why I think it’s best to have a Question and Score table so the questions can be updated and the changes will be reflected on any new scorecards that are created.
I have created the database structure which I think is correct and normalised to the best of my ability. The scorecard table grabs questions and applicable scores from the Question & Score table the marker completes the answer field based on the agent’s performance. So, on each Scorecard that is created the current questions and scores are displayed.
So here’s my question: Should the Scorecard table hold questions and scores, or should it just hold answers? I am thinking questions and scores. I know this is against the rules of normalisation as this data is already held in the Question & Score table however what if I change my questions then want to look at a historical scorecard (completed when the question were different). The questions and scores will have updated to the current set making that scorecard inaccurate.
Thanks
I have a form that requires date, picker id, no of pallets, and a unique identifier (which is date + no of pallets). On my form, there is an unbound text box (displaydate) that takes the date field and converts it into a number, format([date], "mmddyy") so that 04/15/05 looks like 041505. And lets say the no of pallets would be 1961.
In the unique identifier field, my code is:
me.identifier.value = str(me.displaydate.value & me.nopallets.value) which should give me 0415051961. First problem is it doesnt put in the first 0 in the date, it puts 415051961. And I also wanted it to put in an - between display date and no of pallets to look like 041505-1961.
I tried the code several different ways such as str(me.displaydate.value & "-" & me.nopallets.value) which gave me an error.
So could you give me some suggestions? Thanks Noreene
Hiya,
I have a form that uses two subforms. The first subform allows users to enter timecard details and the other subform shows all the hours worked. I have a refresh button on the form so that when users click the button the timecard details from subform one are automatically shown on subform two and the user can then enter more details. However i want my refresh button to only work if a user has enter details in the first subform otherwise i get a load of blank entries in my second subform. Let's say that if hours dont equal zero then the form will refresh else nothing happens. I would be able to do this normally its just that the hours field is in the subform.
Please help.
Andy
I am trying to create a small DB in Access for Events, questions (plural) asked at that Event and the responses (plural) to the questions. I have created the table for the Events and Questions but when it comes to getting the responses to relate to the correct Event and Question I can't get it to work correctly. Let me know if you have any ideas.....My head hurts from hitting it against a brick wall.
View 1 Replies View Relatedi am tryin to make a db where i input the questions but some1 else can anser them, sort of like a test. I was just wondering how to do this, what to use either tables or forms?
View 5 Replies View RelatedI have a group of customers in a database (aprox 600) and a small group (<10) of people who service those customers.
I am able to run a report for each service person which lists all the necessary customer info for any particular day and gives me a total on each report of the number of customers being serviced.
What I want to do is generate 1 report that lists each service person and the total customers for that person. I would then like to have the total customers being serviced also on that report.
I think part of my problem stems from not knowing enough about access when I started. All my customer info is in 1 table. Several of my service people are handling 2 groups of customers (distinction between groups is geographical). I over came that issue by combining my customer groups when I created my queries for each service person.
I feel like I'm too far in to vevamp the whole system as I have forms created to input data and print reports with the help of macros.
I did figure out how to create a report that has the customer totals, but the report shows those totals by geographic area and not necessarily by service person. The commands I used to get this are in header area of the report. Because of this it seems that I can't get a customer total.
Any suggestions would be helpful. Thanks!
I am struggling with an answer table for multiple type answers. Some questions have yes/no answers, some have option group answers (agree, neutral, disagree), and some require text input.
I have reviewed posts on normalization on survey design. In it's simplest form I would have 3 tables.
tblQuestions
QuestionID
QuestionText
tblAnswerCodes
AnswerID
AnswerText
tblResponses
PersonID
date
QuestionID
AnswerID
If all answers required an option group value then I would use the structure above.
If all the answers were yes/no I would eliminate tbl AnswerCodes and simply have an answer field with data type yes/no in tblResponses.
But if an answer requires a user text comment, I would need either a memo field or a 255 length text field. This is where I get confused. It seems inefficient to use a memo or text field for all answers when most of the questions require a yes/no answer. I feel the answer is somewhere in setting up another table with answer types, but I am struggling with this.
On a further note I am struggling with the response data entry form. For an option group answer I could setup an append query for each new survey response. But how about for multiple answer (check all that apply). I guess I need a form based on a query linking tblQuestions to tbl AnswerCodes. This would be a subform on the master form for tblResponses. I keep getting all the answers for all questions. But handling multiple question types on one continuous form seems awkward.
Thanks.
Text field
"CM#000001"
How do i write a where criteria to exclude all records that start with "CM#"
like "CM#*" does not work
like 'CM#' & "*" does not work
thanks
sportsguy
I saw that nobody read my first post so I figure I'll post my question here. I am trying to learn VB and am curious about how to make command buttons that will do the next/previous thing in VB and not using a macro. Any advice?
View 4 Replies View RelatedI have a union query. I am pulling in data from two (almost identical) SQL views in two different databases.
My MS_Access application is linked to the two views
The issue is A.GroupId. In view finance_WILPMonthEndReview the field is set to NVARCHAR255 in view iq4bisfinance_3DMonthEndReview the field is set to NCHAR255.
This is my union query.
SELECT A.Dataareaid,A.a_year, A.a_month, A.GroupID, A.dimension, A.accountnum,A.companygroupaccount,A.account_type,
C.groupiddesc,D.costcelldescription, B.PL1assignment, Round([A.SumOfActual_amt],2) AS ActAmt, Round([A.SumOfBudget_amt],2) AS BgtAmt
[Code] .....
When I link my Excel pivot table to the query I get two lines