Difficult To Explain Yet Simple Problem...!
Dec 1, 2005
Hi all
I have a financial database that gets downloaded transactions off the internet from our accounts. The problem is that the transaction payer/payee is not always unique and needs to be classified. This is easy done using If statements but I really want to enter parts of the string into a table and have a query return a category for this payer/payee. Example:
I want this: AUTO ONE CLARKSON CLARKSON WA AU006495
to be recognised as: car parts
or with this tranaction:
CLARKSON MINI MART CLARKSON553908
look for "mini mart" and return supermarket
This step will make classifying transactions a lot simpler and user friendly.
thanks
View Replies
ADVERTISEMENT
Aug 24, 2007
I am building a database that will tell the company what items to dispatch to the customer first. It’s a food company. So getting products out on time is important. I will try explain it. Say the food company makes a batch of 30 apple tarts. This collection of products has a set batch number with all the relevant information attached. For exampleBatch no 4457 - qty 30 - created 10/10/07 - use by 10/11/07 Batch no 4458 - qty 30 - created 11/10/07 - use by 11/11/07Now lets say customer (joe Bloggs) orders 35 apple tartsThe food company wants to take all the first batch (4457) and 5 from the second batch (4458) The will have an order sheet with the followingApple tart batch 4457 - 30Apple tart batch 4458 - 5 After the order is gathered it will be given an order number of say order 8897The company can now go back and look at order 8897 and see what was shipped in that order and what the batch numbers where for all products. Any help would be greatly appreciatedAlan
View 14 Replies
View Related
May 7, 2007
I think i'm making this more difficult than it is.
I have an 2 excel spreadsheets with sales numbers like this -
Zip CustomerType Sec Hes Fol
10001S0303.230
10002C-373461.391531.8
10577S-71.25287.560
11211C0702.220
11803L14365.27-45
12401S0280.990
12524W0178.320
One file is numbers I averaged over the past 3 months. The other file is actual numbers. Not all zip codes are in each file. I need to figure out the difference between the 2 and add the difference to this months numbers. So I created a query and found there were 94 records that matched based on zip cod and customer type. Then I found the difference in sales amounts. Now I'm stuck at figuring out which zip code and customer type don't match in the 2 files. So if zip code 10001 with customer type S isn't in the actual data for this month I need to add the zip code and customer type and have it be -303.23.
View 1 Replies
View Related
Jul 24, 2006
Hey,
I’ve posted on here a few times lately, as I’m creating a database at work. It is going to be used to keep various information about new persons applying for jobs, and I want it be viewed/sorted by either 'Job Grade' or 'Job Title' I keep doing a lot, and then getting stuck on certain things. I’m stuck again, but I think it’s a little more difficult this time…
What I would like to happen is have the database open up, and a screen appears with two choices – ‘View by Job Title’ and ‘View by Job Grade’. When you click one, a new screen appears and a list of some sort appears with all of the job grades / job titles (depending on which button is pressed) on that are currently in the database.
From here, you can choose a job grade / title and it will bring up the form I have created – but only with the records that have the chosen job grade / title.
So for example, there could be 20 records in the table. 5 with job title ‘Manager’, 5 with job title ‘Assistant’. Then there could be 5 jobs with grade ’1’ and 5 jobs with grade ‘2’. When you clicked on ‘View by Job Title’ and then choose ‘Manager’, the 5 records with the job title ‘Manager’ would come up.
I hope I’m making sense so far.
The data would all be stored in one main table, and I already have the form created, so it would be good if I could use this.
Is there any way to do all of the above?
If anyone could help at all, it would be very much appreciated.
Cheers
Dan
View 3 Replies
View Related
Apr 6, 2007
Can someone explain what the following code does.Thanks in advance
Set dbUser = DBEngine.Workspaces(0).Databases(0)
Set rsUser = dbUser.OpenRecordset("SELECT User.UserName FROM [User]WHERE (((User.UserName)='" + txtUserName + "'))")
View 1 Replies
View Related
Aug 21, 2006
I have a solution for my problem, I just want to see if there is an easier way. Here is my predicement:
From...........To..............c
0...............100............5
100............500............10
500...........1000...........15
1000.........3000............25
3000.........5000............30
5000........10000...........40
10000.......30000...........50
30000.......50000...........60
(This means: Between the amount of 0 and 100, the eventual amount equals to 5. Between 100 and 500, the evebtual amount is 10. etc. etc.)
Over the amount of 50,000 for every 5,000 the amount goes up, the eventual amount (column c) goes up 10, with the condition the eventual amount (column c) should not be over 500.
My current If-statement: Eventual Amount:
IIf([Amount]<100,5,IIf([Amount],500,10,IIf([Amount]<100,15 etc. etc.)))
Is there an easier way to do this... It's going to take mighty long to do it like I am currently doing it.
Thanks a lot
Regards
kruger101
View 6 Replies
View Related
Oct 17, 2006
Hi all,
I was recently asked to rewrite some stuff and i was wondering if the query below can be expressed with just ( SELECT, FROM, WHERE, GROUP BY ) Instead of JOIN.
Someone else wrote the query below and Joins always confuse me ...
Thanks in advance
SELECT Grpoffic.CODE, Evoter.precinct, Evoter.grp, IIf(evoter.style Is Not Null,evoter.style,evoter.[activator code]) AS ballot, Evoter.party, Evoter.[date voted] AS [voted date], Evoter.certnum AS [voter id], Evoter.name, Evoter.address, Evoter.[ev location] AS [Location voted], Vote_status.status, AB_CODE_reason.reason AS [absentee reason]
FROM Vote_status, AB_CODE_reason RIGHT JOIN (Evoter INNER JOIN Grpoffic ON (Evoter.precinct = Grpoffic.pct) AND (Evoter.grp = Grpoffic.grp)) ON AB_CODE_reason.code = Evoter.reason
ORDER BY Grpoffic.CODE, Evoter.precinct, Evoter.grp, Evoter.name;
View 1 Replies
View Related
Mar 6, 2008
Month Number: Mid([Date],4,2)
I also have the SQL that goes with the query:
SELECT Mid([Date],4,2) AS [Month Number], Count(tblOrders.Order_Number) AS CountOfOrders, Sum(tblBikes.Price) AS SumOfPrice, Format([Date],"mmmm") AS [Month Name]
FROM tblOrders INNER JOIN (tblBikes INNER JOIN [Order/Product] ON tblBikes.Model_Number = [Order/Product].Model_Number) ON tblOrders.Order_Number = [Order/Product].Order_Number
GROUP BY Mid([Date],4,2), Format([Date],"mmmm");
:confused:
View 6 Replies
View Related
Aug 11, 2005
Hi expert. could any one explain this code for me. I do not understand some parts of it. I know the output but not some parts of the code.Thanks
Code:Private Sub processButton_Click() Dim fso As New Scripting.FileSystemObject <------ Dim io As Scripting.TextStream <------ Dim db As DAO.Database Dim rst As DAO.Recordset Dim fld As DAO.Field '''Declreaing our variables Dim strBase As String Dim strInsert As String Dim strFields As String Dim strValues As String Dim strTemp As String Dim strFile As String Dim strName As String Set db = CurrentDb() Set rst = db.OpenRecordset(Me![ComboBox]) strBase = "INSERT INTO " & Me![ComboBox] & "({%1}) VALUES ({%2})" <------ strName = "c:" & Me!ComboBox & " Data.sql" With rst <------ While Not .EOF strValues = "" <------ If Len(strFields) = 0 Then For Each fld In .Fields If Len(strFields) > 0 Then strFields = strFields & "," & fld.Name & "" Else '''strFields = "[" & fld.Name & "]" strFields = "" & fld.Name & "" End If Next fld strInsert = Replace(strBase, "{%1}", strFields) End If For Each fld In .Fields If Len(strValues) > 0 Then strValues = strValues & "," End If If IsNull(fld.Value) Then strValues = strValues & "null" Else v = fld.Value Select Case fld.Type Case dbMemo, dbText, dbChar strValues = strValues & "'" & v & "'" Case dbDate strValues = strValues & "#" & v & "#" Case Else strValues = strValues & v End Select End If Next fld strTemp = Replace(strInsert, "{%2}", strValues) strFile = strFile & strTemp & vbNewLine .MoveNext Wend rst.Close End With If Len(strFile) > 0 Then Set io = fso.CreateTextFile(strName) io.Write strFile io.Close End If End Sub
View 3 Replies
View Related
Oct 11, 2006
I am trying to figure out how this code actually works. In the Field I see the following code:
IIf(Weekday([CollectionDate]-4)=1,[CollectionDate]-6,IIf(Weekday([CollectionDate]-4)=7,[CollectionDate]-5,[CollectionDate]-4))
In the Criteria I see: Like Date()
I know what the "LIke Date() does but what im trying to figure out what is the connection to the above....
Any Help would be greatly greatly appreciated!
View 1 Replies
View Related
Apr 12, 2006
I have a database for my company which I am making.
The products are hydraulic cranes and come in numerous models (or sizes), in turn these models come in numerous versions, 5 different ones in all, lets call them for simpleness 1-5. However, some cranes come in versions 1-3 only, others 4-5 only, some all versions and no-doubt some a random mix!
At present I have a table with the fields "Model" (Text), and seperate fields for each "Version" (ie. 1, 2, 3 etc), these are Yes/No tick boxes. Ticking a given box for any given model, means that only these options are available on a form. Ie When you select a "400" crane, you can for example only select Version numbers 1, 2 and 3 - those that are checked in this table.
What I need to do is when selecting each individual model on a particular order is to display 2 figures hydraulic oil flow-rate and also hydraulic pressure required from a reference table I assume. These figures are different for every given model/version combination.
The difficult part as I see is relating the yes/no check box from the table above to version number on the reference table.
Does this make any sense? My head hurts.
Any Help would be super!
View 2 Replies
View Related
Oct 17, 2005
How difficult is it to make a sychronisationfunction?
I have a meetings database. Sometimes the meetings are enterd localy and need to be sychronized with the main file. Is this hard to do? where do i start.
Thank you for your reply!
Joost
View 1 Replies
View Related
Dec 23, 2005
Hi Everybody. I've been nosing around here because I have this difficult problem with a database design. I modelled my program in UML so I have a class diagram. Now I want to create an access database out of it, but this is too hard for me.
It's about a school project for flowers. Every year, they make a cross plan. This cross plan contains crossings. Many crossings. And every crossing exist from 2 genotypes. A mother and a father. With this crossing several new genotypes are created. How on earth do I realize that in a database? A plant is male as well as female so you don't need to indicate which sex it is.
Further more I want a genotype to be judged on his characteristics by a user as much as he wants to.
Well...I hope someone can help and if you have questions about it don't hesitate to ask.
Thanks in advance!
Gr Billy
View 4 Replies
View Related
Mar 29, 2008
Hi all!, i would really appriciate it if you could help me out. I have this complex query... in one table i have dates and costs associated that show when fuel was put into the system
such as:
1/1/2008 -- $200
1/20/2008 - $100
1/25/2008 - $150
I then have another table that has data when fuel was taken out of the system
such as:
1/2/2008 -- ($50)
1/3/2008 -- ($20)
1/4/2008 -- ($10)
what i need is a query that is able to show a running tab on the fuel tank and how much fuel is in it...
so for example if it could spit out a result such as
1/1/2008 -- $200
1/2/2008 -- $150
1/3/2008 -- $130
1/4/2008 -- $120
If this is convoluted i can upload a little database..
Thanks! (Here is the current query i am using but its not working right.
SELECT DatePart("yyyy",[purchase_Date]) AS AYear, DatePart("m",[Purchase_Date]) AS AMonth, DatePart("d",[Purchase_Date]) AS ADay, DSum("gallons_purchased","purchase_fuel_tbl","DatePart('d', [purchase_Date])<=" & [ADay] & " And DatePart('m',
[purchase_Date])<=" & [AMonth] & " And DatePart('yyyy',
[purchase_Date])<=" & [AYear] & "") AS RunTot, fuel_use_tbl.fuel_date, fuel_use_tbl.external_ID, fuel_use_tbl.gallons, [RunTot]-[fuel_use_tbl].[gallons] AS cur_Value
FROM (tank_tbl RIGHT JOIN purchase_fuel_tbl ON tank_tbl.tank_ID = purchase_fuel_tbl.tank_ID) LEFT JOIN fuel_use_tbl ON tank_tbl.tank_ID = fuel_use_tbl.tank_ID
GROUP BY DatePart("yyyy",[purchase_Date]), DatePart("m",[Purchase_Date]), DatePart("d",[Purchase_Date]), fuel_use_tbl.fuel_date, fuel_use_tbl.external_ID, fuel_use_tbl.gallons
ORDER BY DatePart("yyyy",[purchase_Date]), DatePart("m",[Purchase_Date]), DatePart("d",[Purchase_Date]);
View 14 Replies
View Related
Apr 15, 2008
Hi all,
I have 2 tables, namely: Projects and WeekUren
Where [WeekUren] contains multiple references to unique records in [Projects].
[WeekUren] has the following collums which are important:
-ProjID
-Week
-Persoon
Where these three always form an unique combination. For instance:
1 | 1 | 1
1 | 2 | 1
1 | 3 | 1
1 | 1 | 2
1 | 2 | 2
2 | 1 | 2
Saying: Person1 will work week 1 till 3 on Project1, Person2 will work week 1 and 2 on Project1 and week1 on Project2.
Weeks are here relative to the project start date (a value of [Projects]) and always start with and increment with 1. So week1 of Project 1 and 2 don't neccesarily fall on the same dates, hence Person2 is still able to work both Projects.
Bear with me here.....
Now I need to select all duplicate records (where people are assigned to two (or more) projects in the same real week.
Real week is defined by:
Code:DatePart("ww",((WeekUren.Week-1)*7+(SELECT Projects.Start FROM Projects WHERE Projects.Id=WeekUren.ProjId)))
And the Query to convert [WeekUren] in to the relative form is:
Code:SELECT WeekUren.Id, DatePart("ww",((WeekUren.Week-1)*7+(SELECT Projects.Start FROM Projects WHERE Projects.Id=WeekUren.ProjId))), WeekUren.Uren, WeekUren.Persoon, WeekUren.ProjIdFROM WeekUren;
The query to print out duplicates is ofcourse:
Code:SELECT *FROM WeekUrenWHERE (((WeekUren.Week) In (SELECT [Week] FROM [WeekUren] As Tmp GROUP BY [Week],[Persoon] HAVING Count(*)>1 And [Persoon] = [WeekUren].[Persoon])))ORDER BY WeekUren.Week, WeekUren.Persoon;
Sofar so good, combination of the two however give me a syntax error. Hence my question here how to construct such a diabolical Query. Thanks!
Regards,
Jacco
View 4 Replies
View Related
Sep 5, 2006
I saw this code and was wondering how does it actually work?
I saw it in/connected to a textbox.
Private Sub savetime_BeforeUpdate(Cancel As Integer)
End Sub
View 1 Replies
View Related
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
May 11, 2006
I am not sure if I understand this...
I have MainTable, on which I base MainForm. I would like to have MainForm show only the records that have a null value in CertainField. If I write NullQuery to select only those records, can I redirect MainForm to NullQuery? Well, I know I can do that... but how does MainTable get updated with new records if MainForm is based on NullQuery????
Any help is greatly appreciated.
Tom
View 4 Replies
View Related
Jun 16, 2006
I have Two fields with DateWorked and EmployeeNumber. All I want to do is count the number of employees that worked on a specific date. In the employee numbers I have 4 dummy numbers that I don't want to count in my query (00001-00004), but will have entries almost each day. The rest are 5 digit numbers.
Some employee will work some days, but not others.
So I set up my query with:
EmployeeNumber
Table
Total: Count
Criteria: >4
I did that think that it would count all employee numbers great than four, but all it seems to do is give me the count of days that more than 4 employees worked.
What am I doing wrong? I feel like this is simple, but after some of the more complex things I've done over the past week, this seemingly "easy" thing has me stumped.
View 3 Replies
View Related
Jul 16, 2005
Let see if I can explain my goal. I've been fighting with this for some time with no resolve. In the included DB has a table that represents the fields that will be queried in the real DB. It shows records of one of many employees that were "tested" (shopped) on a date and whether it was a successfull shop or not. My goal is in three parts.
1)A query that goes through each employee and returns the records with the check box in SuccessYes checked (Yes) back to the last unchecked box, not including the unchecked box. In the example given the last (most recent) 5 records would be returned. If the shop of date 1/18/2005 was not checked, it would only return the last 2 records. Basically, I'm looking for a way to have a report that shows everyone's current successes in a row. It could be any number.
2)A query very similar but it returns only those employees that have the current successes in a row totalling 5 and where the field "Award5_Paid" is NOT checked (they have not been paid their reward). From there it should be easy to do one for the 10 in a row.
3)If I've got a form open (or...?) where I can place a command button, that when clicked, it appends the selected records with checks in the appropriate boxes for being paid. Example: If a set of employees come up in the query with 5 successfull shops in a row because the "Award5_Paid" field is unchecked, then I need to pay them. Click the button and the Award5_Paid field gets checked in THOSE RECORDS ONLY so that the next time I run the query, they won't show up and get paid twice. Issue? What's the coding or SQL or ??? that's run when clicked.
I hope that makes sense. I've been running around in circle trying to figure this one out. Maybe it can't be done, but if it can, I know some of you know how.
As always, thanks for the assistance.
-Jim
View 3 Replies
View Related
Mar 31, 2008
Alright, so my pride and joy and departmental savior database has been fully implemented and people are so pleased with it that they *all* want to use it at the same time. Initially, I made it custom for three peoples' UI preferences, but since it's such an intuitive design, other staff now feel comfortable using it instead of passing the work down the line to the one person who knows how to use that "newfangled thing".
A couple years ago, I used FMPro and allowing for multiple users at once was an annoyingly complicated process dealing with permissions and rights and accounts and passwords and yadda yadda. Is it any easier to set up in Access? Does anything special even need to be done to allow for multiple simultaneous users?
View 3 Replies
View Related
Sep 16, 2005
Hi, i know i have done this before but its been so long and i know its real simple so here goes:
Say i have a record in a table and i want to add records to that record in another table does simply creating a relationship between the two tables make sure the information is relevant to that record and how do i add more than 1 tables information to a form so i could show the record from the first table and say all of that persons orders from another table?
Thanks for your assistance
Alex
View 1 Replies
View Related
May 24, 2005
Hi,
This is a toughie (i think so anyway!). I'll attempt to explain!!!
I start with a CONTINUOUS form in my DB that shows all readings for a single given customer's connections. E.g. Customer A has 5 connections and each connection has say 3 readings. Thus this query which uses joins between the Customers, Connections, and Readings tables, would return a list of all readings for each connection, so in the above example: 1*5*3 = 15 records.
Not too tricky so far, but then what I want is for each connection to have just one line showing the latest reading (easily achieved by use of SQL Aggregate Max function on the date field coming from the readings table) TOGETHER WITH the last-but-one (next most recent) reading. So back to the example taking Customer A's connection 1, the row would be as follows:
Cust Conn CurReading PrevReading
A 1 750 500
Where the CurReading value (750) comes from a different record than PrevReading (500).
I've tried all sorts of ways (subqueries etc.) to achieve this without success. The main problem being that any sub query would require parameters from the current record's fields, which seems not to be possible. Can anyone help or is this simply not possible in MSAccess Forms. If it isn't possible anyone have any suggestions as to an alternative way?
Thanks
Darren
View 2 Replies
View Related
Dec 29, 2006
Ok I am right now making a simple Vendor/Product database to create a line sheet for some sales folks. I have 3 tables: Vendors, Products, and an associate entity Vendors_Products to relate the two. I have a form currently that draws the Vendor Name (primary key) from the Vendor table and the Product Name from the associate entity. This allows me to create new vendors and select current product types from a drop down box. The problem is that the drop down box is too long and it is tiresome when 1 vendor has 10 product types.
Can anyone tell me how to resolve this? I thought it would be better to have option buttons and display all available products. Then you could just click all of the option buttons that apply to that Vendor and it would create the relationships...is this possible?
View 1 Replies
View Related
Nov 22, 2005
Hi,
I have looked at some of the threads here and it is clear that many of you are working on a much higher level than me and with a high degree of familiarity with the programme.
I am hoping that someone here is able to give me some advice as I don't find the MS help files digestible.
The task I have is to join 2 databases and produce a table from which I can run a mailmerge.
I have managed to join the 2 databases and I used a customer ID as a common link. (my apologies if the terminology is incorrect)
I now have all the data I require in one table.
THE PROBLEMs I have multiple entries for some of my customers and would like to reduce this to single entries (which is understandable). Please tell me how to do this if you can, and keep it as simple as you can please.
In anticipation I thank you very much.
:-)
View 2 Replies
View Related
Jan 14, 2006
Hi there,
I'm making a report detailing competitors final finishing positions in a competition.
The columns are (1) Place, (2) Name and (3) Points
I have the data for their names and the points they have scored, but how do I make the table enter the record number (starting at 1) in the first field???
Thanks in advance
Col
EXAMPLE
PLACE NAME POINTS
1 John Smith 199
2 Ian Jones 90
View 1 Replies
View Related
