Having Access Solve For The Best Answer, Is It Possible

Aug 23, 2007

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

View Replies


ADVERTISEMENT

Did Anyone Ever Solve This One?

Aug 31, 2007

I am having the same issue.

Can't seem to find a solution to my problem here or in a few reference books I have on my shelf here at work.

I inherited several access databases from my predecessor a while back and although I’ve fixed most of the pre-existing problems and re-programmed most of the queries and reports at this point I’m stumped on one issue. One of the access files forces me to save every query, form or report I create no matter what. If I create a query because I want to do a quick one time query I can’t just close it and say no to saving when I’m done, I have to save it and then go back and delete it if I don’t need it anymore. I can’t track down any setting or code anywhere in this one access file that would cause this behavior to be different than all of the other dozen or so databases I inherited. This becomes a big issue in the case of if I accidentally do a major change I didn’t mean to that the undo command doesn’t correct, if I go to close it saves the query if it’s been saved before and makes my error permanent and I have to go dig out the nightly backup to undo the mistake.

Any thoughts on where to find or change this setting would be great as I’ve run out of ideas on my own.

View 1 Replies View Related

Can Someone Solve This Vba Program

Oct 10, 2006

Following program i have written in access. what it does is from the query i have created picks up the sizes of doors. calculates hit1 by qty then it needs to put the value into another table alongside the correct sizes. the problem im facing is how do i update the value into another table. heres the listing.

Option Compare Database
Option Explicit
Dim Db As Database
Dim Rs1 As Recordset
Dim Rs2 As Recordset
Dim lf, rh, drw1, drw2, drw3, drw4 As String
Dim q1, q2, q3, q4, q5, q6, qty As Integer
Dim hit1, hit2, hit3, hit4, hit5, hit6 As Integer



Private Sub Command70_Click()
Dim rs1fieldcount As Integer
Dim counter As Integer
''Assign a mdb
''+++++++++++++++
Set Db = CurrentDb
counter = 0
Set Rs1 = Db.OpenRecordset("custdoorsize")
Set Rs2 = Db.OpenRecordset("cusdoorsbase", dbOpenDynaset)

rs1fieldcount = Rs1.Fields.Count


If Rs2.RecordCount = 0 Then
MsgBox "No record found in query"

Else
this is where the value will come from
Do Until Rs2.EOF
If Rs2.Fields!left_door_size <> Empty Then
lf = Rs2.Fields!left_door_size
hit1 = 1
q1 = hit1 * Rs2.Fields!qty
End If

If Rs2.Fields!Right_door_size <> Empty Then
rh = Rs2.Fields!Right_door_size
hit2 = 1
q2 = hit2 * Rs2.Fields!qty
End If

If Rs2.Fields!draw1_size <> Empty Then
drw1 = Rs2.Fields!draw1_size
hit1 = 1
q3 = hit3 * Rs2.Fields!qty
End If

If Rs2.Fields!draw2_size <> Empty Then
drw2 = Rs2.Fields!draw2_size
hit4 = 1
q4 = hit4 * Rs2.Fields!qty
End If

If Rs2.Fields!draw3_size <> Empty Then
drw3 = Rs2.Fields!draw3_size
hit5 = 1
q5 = hit5 * Rs2.Fields!qty
End If

If Rs2.Fields!draw4_size <> Empty Then
drw4 = Rs2.Fields!draw4_size
hit6 = 1
q6 = hit6 * Rs2.Fields!qty
End If

Do While counter <> rs1fieldcount

This is where it finds the correct sizes where it will display the qty value

If Rs1.Fields(counter).Name = lf Then
Rs1.Edit
Rs1.Fields(counter).Value = q1

End If
If Rs1.Fields(counter).Name = rh Then
Rs1.Fields(counter).Value = q2
End If
If Rs1.Fields(counter).Name = drw1 Then
Rs1.Fields(counter).Value = q3
End If
If Rs1.Fields(counter).Name = drw2 Then
Rs1.Fields(counter).Value = q4
End If
If Rs1.Fields(counter).Name = drw3 Then
Rs1.Fields(counter).Value = q5
End If
If Rs1.Fields(counter).Name = drw4 Then
Rs1.Fields(counter).Value = q6
End If
counter = counter + 1
Loop

If Rs2.EOF Then
Set Rs1 = Nothing
Set Rs2 = Nothing
Set Db = Nothing
MsgBox "Finished ....."
Exit Sub
Else
Rs2.MoveNext

End If
Loop
End If
End Sub

please help

View 1 Replies View Related

Solve The Queries

May 8, 2006

Dear My Friends

I want to solve the follwing matter.

** Enter-Invoive Number
** Enter-Amount


If the amount 1000> - Show stamp duty as $1

If the amount 1001-2000 -Show stamp duty as $.2

If the amount 2001-3000 - Show stamp duty as $3

If the amount 3001-4000 -Show stamp duty as $4

Like wise upto 50,000

If the amount 50,000 < Show stamp duty as $50

I want to above results in access report with name of invoce number, amount and stamp duty .Please help as soon as possible

View 1 Replies View Related

Will A Subform Solve My Problem?

Aug 31, 2006

I have looked all over for an answer, and I think there may have been a few examples on this forum that may have helped, but I only have Access 97, so can't open the example databases to fully understand!

Bascially, in the database I am trying to set up, I have Museum items, for instance a photo, and the database users would like to have a list of the names associated with that photo. (obviously for some items there will be no people associated with it, and varying numbers of people for other items!).

I need some way of having the 'associated people' data displayed on the item information form (or somewhere handy). And I need it to have the flexibility to account for differing numbers of associated people. Would using a subform solve this? And if I use a subform, where will that data then be stored? In its own table??

View 2 Replies View Related

A Difficult To Solve Problem!!

Nov 14, 2006

Hello,

My problem is this:
I want to retrieve some data from the database, the data is as follows:

pourcentage de processus évalués dans l’année

my problem is that when I am writing a querry for this...it gives me an error because of the << ' >> mark in the word << l'année >>

my querry is this...
strSQL = "insert into temp_indicateur (id,description) values(" & rs!id & ",'" & rs!description & "')

I am getting these values from another recordset.

Can anyone please help??

View 2 Replies View Related

Query Needed To Solve Candidate Skills Database

Jul 30, 2007

Hi there,

The recruitment database I have designed for work is okay in the main except for where I need to filter candidates depending on their skill sets. What I need to be able to do is filter candidates that possess ALL skills selected in a multiselect listbox...

Table structure runs as: Candidates (many) linked to Job Role (one) (because candidates can only have one job title (in theory anyway)
Candidates linked to Skills table via junction table (candidate having many skills / one skill belonging to many candidates blah blah)...I'm sure this set up is as normalised as I can get (I aint no expert tho)...

My solution so far (suggested by another forum) was 3 listboxes on a form that runs like...
ListBox 1 = Job Role (Manager, Team Leader, Clerk, etc - set to SINGLE SELECT because an employee can only have ONE job title (supposed to anyway LOL)...
ListBox2= Skills (Payroll,Audit,Taxation, etc - SET TO MULTISELECT because employees can have more than one skill)...
ListBox3 = Candidates (populated by making selections in ListBoxes 1 and 2)

It all works well but is VERY slow as I was told to use make table, append queries and quite a bit of VBA to make the WHERE clauses as SQL statements, etc

I've searched high and low all over the net and have found things that come close but I'm just not adept enough to work it out (I started Access late in life). I feel I need some kind of subquery that first of all finds all candidates that e.g. have ALL 3 skills selected in Listbox 2 (creating a recordset of one row per skill meaning each candidate is listed in the recordset for as many skills selected and then filtering again with a count function that only displays candidates with a count of 3 skills - this subquery would then be used to populate Listbox 3 -

Sorry if I've overcomplicated this but it seems such a simple thing to and I'm getting a lot of pressure at work having being trying to solve this for weeks...

Any help putting me in the right direction or if you know of any similar example databases that would help me learn more would be much appreciated

Regards

dazza61

View 4 Replies View Related

I Figured Out The Problem, Now Please Help Me Solve It - Search/Email Results

Nov 30, 2004

Ok here is what was going on. I have an option group, I would select an option,
type in my search criteria into a text box, and click an event button. The program
would then search my database for that criteria and grab the emails of the individuals
meeting that criteria. A message box would then pop up, listed all the emails it had grabbed
I would click ok (my sendobject command is set to edit the email) and at this point instead of
opening the email to edit the program would crash.

After scratching my head for several days I moved on and have come back to this problem again
two weeks later, and within 15min I realized what was wrong. I guess it is good to walk away
and come back fresh sometimes.

What is happening is that the program does not account for entries in my database that do NOT
have an email.

Example, let us say we want to search a state...lets use the state of GA for example. If I have 5 entries
from GA with email addresses and one entry from GA without an email address, it will crash the program.

My only guess is that the way the program is setup is that it is grabing whatever data is in the email location.
It that location is blank, its grabing blank and crashing the program.

I need a way to discard the entries it searches that don't have email addresses or something...

Please help, this is the last item for this project.

Here is the current code:

Code:'Code by M. Walts'Important information! this code requires a reference to the Microsoft DAO object libraryOption Compare DatabaseOption ExplicitPrivate Sub cmdEmail_Click()'will hold the dynamic SQL queryDim strSQL As String'will hold the WHERE clause portion of our SQL queryDim strWHERE As String'will hold all the recipients of this messageDim strRecipients As String'the recordset we will use to get the emails of the records that match our criteriaDim rst As DAO.Recordset'if there is input in the search criteria, then we will run the query and send the e-mailIf txtSearch <> "" Then'if you have more buttons, just add mosr cases (the value of the radio button'= the Case number, so Value of the State radio button is 1, etc.)Select Case opgSearch.ValueCase 1strWHERE = "WHERE State = '" & txtSearch & "'"Case 2strWHERE = "WHERE PrayerSupport = '" & txtSearch & "'"Case 3strWHERE = "WHERE Denom = '" & txtSearch & "'"Case 4strWHERE = "WHERE PACTTrainer = '" & txtSearch & "'"Case 5strWHERE = "WHERE PACTPartner = '" & txtSearch & "'"Case 6strWHERE = "WHERE City = '" & txtSearch & "'"Case 7strWHERE = "WHERE Donor = '" & txtSearch & "'"Case 8strWHERE = "WHERE MailingList = '" & txtSearch & "'"Case 9strWHERE = "WHERE Conference = '" & txtSearch & "'" Case 10strWHERE = "WHERE YouthPastor = '" & txtSearch & "'"Case 11strWHERE = "WHERE PreviousCustomer = '" & txtSearch & "'"End SelectstrSQL = "SELECT EMail FROM tblUser " & strWHERE'run the query and get the results into the recordsetSet rst = CurrentDb.OpenRecordset(strSQL)'Loop through the recordset and add all the EMailsDo While Not rst.EOFstrRecipients = strRecipients & ";" & rst!EMailrst.MoveNextLoop'remove the first ; from the strRecipientsstrRecipients = Right(strRecipients, Len(strRecipients) - 1)MsgBox strRecipientsDoCmd.SendObject , , , , , strRecipients, "Email Subject", "Email Body", Truerst.CloseSet rst = NothingEnd IfEnd Sub'stops a ' entered in the field from breaking the queryPrivate Function SQLSafe(safeMe As String) As StringSQLSafe = Replace(safeMe, "'", "''")End Function

View 2 Replies View Related

How To: If Answer Is X Then Go To Y, If Not Go To Z

Apr 12, 2005

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:

View 12 Replies View Related

Answer

Oct 31, 2006

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

View 1 Replies View Related

Anyone Answer A Simple Q For Me???

Jan 15, 2006

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

View 4 Replies View Related

Answer From 2d Array

Jan 29, 2007

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

View 11 Replies View Related

Top 10 Ways To Not Get An Answer

Feb 3, 2007

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 Related

Can't Find The Answer - Can Anybody Help !!

Nov 12, 2007

Hi 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

View 7 Replies View Related

MTD, YTD, WTD (one Answer For Total)

Oct 13, 2005

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 2 Replies View Related

Please Answer Need Help Desperately

Apr 19, 2006

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

View 4 Replies View Related

Why So Many Repeats Of The Same Answer

Aug 30, 2004

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

View 1 Replies View Related

Please Help Me Answer This Question -

Aug 20, 2004

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.

View 9 Replies View Related

Help's Answer Wizard And Index Gone!

Jan 4, 2007

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

View 1 Replies View Related

Urgent Answer Welcomed!

Feb 16, 2007

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!

View 7 Replies View Related

A Simple Question (if You Know The Answer)

Oct 14, 2005

: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.

View 4 Replies View Related

Question And Answer Database

Jul 27, 2007

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

View 3 Replies View Related

Such A Simple Answer! I Wish I Knew It

Apr 20, 2005

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

View 5 Replies View Related

2 Subforms.. One Command... One Answer?

Aug 30, 2005

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

View 1 Replies View Related

Trying To Create A Question/Answer DB

Dec 19, 2006

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 Related

Creating A Question/answer Db

Feb 5, 2007

i 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 Related







Copyrights 2005-15 www.BigResource.com, All rights reserved