Comparing A Large Amount Of Information
Aug 30, 2007
Ok, so here's the deal.
I have over 5,000 customers in a simple database. For each customer, I'm only displaying the account number, name & class of trade. Each customer represents a record and there is also a sub-form below displaying each customers product template - so each customer has different items displayed and this changes as I switch from record to record or customer to customer. (sorry if this is too much info)
I want to take a class of trade (Bakery for example) and I want to say, a Bakery should purchase these items (I'll have a list of items for the class of trade Bakery.) I want to then take that list and compare it to what is already in all Bakery customer product templates. Lastly, I want to display all of the missing items. The items I think they should buy - but only the items that are not already in the customer's product template.
Obviously, using the final report generated, our Sales Staff would be more effective while hitting the beat (so to say.)
I have a little experience with Access and VBA - but I can't get my head around this one. Any help would be much appreciated...:cool:
View Replies
ADVERTISEMENT
Mar 27, 2013
Best way to display "notes: field that gets updated/added to multiple times, in a form. (Access 2007)
I've got a DB to track information about product changes. Each month we have a meeting and discuss problem products. I'm concerned with a notes/comments section. Currently it is a memo field in the source table that can be added to, so the notes are cumulative. But we want to auto-add a date to each new entry (in case the person inputting the data forgets, and so that notes can be sorted by order on a report).
There may be many comments on a certain product or there may be none. Sometimes the note may be very long, other notes on the same product may be very short. I created a new table with a one-to-many relationship to case ID (to link it to the main form), auto-populate a date field, and field for notes in order to link multiple notes to each case (and of course an autonumber PK).
I can put the subform in the original form, but then the note field will be the same size for every note, though notes are not all uniform in size/length, and it ends up taking up a LOT of room on the main form. So I created a button to click to open the subform, but it brings up all records for all cases, not just the currently selected case. Again, have the problem of one uniform field size for all notes, regardless of size/length.
Then I considered a report to view past history (field size can shrink or grow as needed) then a button on the report that opens a form for data entry/entry of new note. But again the report displays all records for all cases, not just the currently selected case.
View 2 Replies
View Related
Jun 16, 2015
I have 250 separate worksheets with a lot of data to put into Access. Problem is the data is 120,000 rows in each worksheet and a lot of duplicate date eg..DATE, NAME, TIME,are some of the column headings and there are multiple rows with same DATE or NAME. That is just how I received the data. I would like to transfer all records into Access as quickly and efficiently as possible.
View 4 Replies
View Related
Jun 9, 2013
My DVD library database I have a memo field which has the description of the movies.
In some cases, like my tv show records, I have a lot of data because of the episodes and episode descriptions.
I'm currently displaying this data using a split form with the datasheet on the left
I must use a small font in the memo field in order to display all data.
I could use a larger font and simply scroll but I'm looking for an alternative.
I've even thought of creating a hyperlink filed and point to the movie on IMDB's web site. But when I click the link nothing happens even though I copied and pasted the link into the field.
View 8 Replies
View Related
Jul 21, 2015
I have One table(and designed form from it) in which i have recording the daily transactions(it is like a daily book). Daily transactions took place like Purchase of items of Amount 45, sale=70, sold on account=100 etc.
What problem i am facing is: I would like to Add Cash In Hand amount and would like to show it on my form. When day end I would like to move remaining cash In Hand on separate column of that date. The next day i would like to take the previous Remaining Cash in Hand as Cash In Hand at Start for next date and so on.
View 2 Replies
View Related
Jul 17, 2014
I am trying to make a report in which I can see the amount of money that I have to pay weekly to an employee. To explain myself clearer, I have different rates for each employee. For example:
NameNormalPayRateVacationPayRateSickPayRateOvertimePayRateStandbyPayRate
John Smith$15$10$15$30$8
Ken Jonson$10$8$10$20$8
I am doing this in a query. So what I want to be able to do is, for example, take 4 hours that the employee worked at NormalPayRate and multiply it. But if tomorrow the employee worked at a different rate, for example 3 hours overtime, I want those 3 hours multiplied by OvertimePayRate.
The way I saw it on my Google researches, is that they have only one rate and the amount will be [SumOfHours]*[Rate]. But how can I tell my query to skip, for example, one rate because the employee didn't work at that rate that day. So I can have different amount for the different rates.
By the way, entering the information I can divide at what rate the employee worked x day. So if the employee work 6 hours at regular, and later on 4 hours at standby, I have those information separated in my table.
Results that I am getting: I can easily get the amount in money multiplying the whole hours by a rate, so my result in my report will be multiplied by the same rate.
I am using Access 2013 ...
View 2 Replies
View Related
Feb 3, 2005
I have a script file of FoxPro 2.6 which is use to read amount field and write it in words.
Can any body convert in VBA Script.
I manualy write amount in words.
Thankyou.
Saira
View 3 Replies
View Related
Jan 14, 2007
I have two datasets that I am using. They start off with similar information: sitename, siteprovince, sitecoordinates. They also have 5 more fields that have the same type of information. After that there are about 10 more fields with no overlap.
In the original dBase program they came from they were treated as one dataset.
The current structure I am using is Company, CompanyContact, Transaction, SiteDetails (the dataset I am asking about).
Is it better design to breakup the SiteDetails into SiteTypeA and SiteTypeB? I have everything working in one table, but I thought it might be more effecient to have two.
View 2 Replies
View Related
Jul 13, 2007
I have a split database, the front end is showing to be 20.5 megs in size but there are only 4 forms and about 6 querys. Can someone tell me how to find what is making it so large?
I did a compact and repair but it did not reduce the size??
Thanks..
Fen
View 14 Replies
View Related
Jul 17, 2007
I'd like to create a table with 240 fields. I know that the max is 255, however, I'm getting a message "property value too large" after I've created 114. Any ideas? All the number fields are byte size. Thanks!
View 10 Replies
View Related
Dec 19, 2005
I am currently operating queries in my A2K DB that are 35458 characters (~11 full sheets of A4 paper with 12pt).
It takes roughly ½ second for the form to load due to the heavy query that is also calling functions (calc'd fields) from within, but it works fine.
Are there any problems in this?
I think I heard somewhere that queries have a max length of about 2000 characters or so ... :o
View 1 Replies
View Related
Nov 14, 2005
I guess I have too many columns in my database and I'm getting the error message "Property value is too large" when trying to open the database table. When I was using Access 2000, I was still able to open the database but using Access 2003, the database will not open. Is there a way around this so I can open the table to fix it?
View 14 Replies
View Related
Nov 6, 2014
How to get a large .txt file into Access. I know it has too many columns so I selected about 30 columns that I don't need to be 'skipped'. However it is just giving me the error that my file has more than 255 columns - with the 30 selected for skip - it should have about 230 columns.
View 7 Replies
View Related
Sep 26, 2005
How many users can an access database support? I'm setting up an app accross 3 workstations but each has multiple users. There may be as many as 1000 records a day entered. Can an access database handle this or do I need to switch to sql server?
View 6 Replies
View Related
Nov 8, 2007
Hi
Is there any way i can increase the amount of columns in a table before importing data? I am importing data from a notepad doc that needs abt 300 columns but i can only get abt 100 columns.
Thanks for any help
View 8 Replies
View Related
Jun 2, 2005
Hi All.
I need to generate a query which will show me in Field1 Cost for the current year only and in Field2 Cost from a start date to the current. If is it possible how to do it?
Thanks.
View 1 Replies
View Related
Jun 3, 2005
I'm trying to produce a query that will bring up a list of all employees with their current salary amount. The table however, has all changes for an employee and reads as follows:
Table Name(NBRJOBS)
Employee ID-- Date of Change -- Salary Amt
1234 ----- 01/01/2003 -------- 500.00
1234 ----- 05/01/2003 ----- --- 600.00
1234 ----- 11/01/2003 ---- --- 700.00
2222 ----- 03/05/2004 ------- 200.00
2222 ----- 12/05/2004 ---- --- 300.00
2222 ----- 02/01/2005 ---- --- 300.00
It will be necessary to create a report to list out all employees and their current salaries. Any suggestions?
View 5 Replies
View Related
Dec 6, 2005
I have a table containing the following two fields, one with monthly dates (end of month plus year) and one with profits (per month). However, for some dates the records are missing. For example, for the 31-1-1994 there is no record (not in the date field, nor in the profits field).
How can i create a query that will only show me the records if 10 or more monthly subsequent profits are known, so meaning that in those 10 months no records are missing? So that only the timespans without the gaps (missing records) are shown.
So if the 31-1-1994 and the 30/6/1994 record are missing, then the 4 subsequent records in between those two dates should not be shown,, since the amount of records is not 10 or more. However, if the next missing date would be 30/6/1995, then all the 11 subsequent records between 30/6/1994 and 30/6/1995 should be shown. Since the number of records is bigger than the required 10.
Thanks for helping me out!
Luuk
View 4 Replies
View Related
Jul 14, 2007
I'm ok with Parameter Queries now, but I'm stuggling for the certeria to select say the top 20 records for a list. This would be based on a number, say amount spent.
Any help?
View 3 Replies
View Related
Nov 23, 2007
Dear all,in my table I have the following fields:- fldstartdate- fldstarttime- fldenddate- fldendtimeCan I calculate the time that is between those variables using an expression?fldenddate.fldendtime - fldstartdate.fldstarttime = "x days and x minutes"Kind regards,Hans B.
View 4 Replies
View Related
Feb 27, 2008
Hello All
if I have thirty records and have the end user select the first 8 or what ever amount they want, could be 3, 14, 9 etc, is this possible via a form ?
thanks
View 13 Replies
View Related
Mar 3, 2005
Make Table Name: tblnum
Fields:
1.num (for number)(Feed 1 to 99)
2.inword (for In words)(Feed One to Ninty Nine)
Make Module
Public Function inwords(amount As Variant) As String
Dim intlac As Integer
Dim intTh As Integer
Dim intHun As Integer
Dim intNum As Integer
Dim strLac As String
Dim strTh As String
Dim strHun As String
Dim strNum As String
Dim intlen As Integer
intlen = Len(amount)
Select Case intlen
Case 7
intlac = Left(amount, 2)
intTh = Mid(amount, 3, 2)
intHun = Mid(amount, 5, 1)
intNum = Right(amount, 2)
strLac = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intlac) & " Lac"
If intTh = 0 Then
strTh = ""
Else
strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"
End If
If intHun = 0 Then
strHun = ""
Else
strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"
End If
If intNum = 0 Then
strNum = ""
Else
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
End If
Case 6
intlac = Left(amount, 1)
intTh = Mid(amount, 2, 2)
intHun = Mid(amount, 4, 1)
intNum = Right(amount, 2)
strLac = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intlac) & " Lac"
If intTh = 0 Then
strTh = ""
Else
strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"
End If
If intHun = 0 Then
strHun = ""
Else
strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"
End If
If intNum = 0 Then
strNum = ""
Else
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
End If
Case 5
intTh = CInt(Left(amount, 2))
intHun = CInt(Mid(amount, 3, 1))
intNum = CInt(Right(amount, 2))
strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"
If intHun = 0 Then
strHun = ""
Else
strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"
End If
If intNum = 0 Then
strNum = ""
Else
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
End If
Case 4
intTh = CInt(Left(amount, 1))
intHun = CInt(Mid(amount, 2, 1))
intNum = CInt(Right(amount, 2))
strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"
If intHun = 0 Then
strHun = ""
Else
strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"
End If
If intNum = 0 Then
strNum = ""
Else
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
End If
Case 3
intHun = CInt(Left(amount, 1))
intNum = CInt(Right(amount, 2))
strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"
If intNum = 0 Then
strNum = ""
Else
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
End If
Case 2
intNum = CInt(Right(amount, 2))
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
Case 1
intNum = CInt(Right(amount, 2))
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
End Select
inwords = strLac & " " & strTh & " " & strHun & " " & strNum & " Only"
End Function
Result = inwords(Number TextBox)
View 2 Replies
View Related
Sep 2, 2005
What is the maximum amount of fields/columns a table can hold in access 2003?
And what do you do if you have a form and its record source is a table that has run out of fields/columns?
If I remember right in access 97 it was 256 or 257 fields/columns was the max allowed. And it seemed to me it was quite common to run out of fields.
I'm currently waiting for my access 2003 upgrade disk to arrive and I'm
anticipating issues that might occur.
Thanks--Any help is much appreciated.
View 3 Replies
View Related
Feb 8, 2008
Hi everybody....
I have a vba question....
I have a form that states how many entries a certain table can have related to that form.
For example:
Form1 - based directly on the table for diagnostic purposes
Cust ID - text - primary key
NumofEntries - number - integer
I want it to create X amount of records in form2 based upon the NumofEntries from form 1 based on table1
For example, I run form 1, and it asks for the custnum, I enter it, and then it asks for the number of entries allowed for the customer, and then I enter it. This number can be different each time. I want the 2nd form just to show that many fields for entry either by a running total (subtraction) in form view or by a for next loop to create X amount of records in the table.
Form1:
CustNum = 1011
NumofEntries = 3
CustNum = 2022
NumofEntries = 4
CustNum = 3033
NumofEntries = 2
Table2
Custnum = 1011, entrynum =1, entry data
Custnum = 1011, entrynum =2, entry data
Custnum = 1011, entrynum =3, entry data
Custnum = 2022, entrynum =1, entry data
Custnum = 2022, entrynum =2, entry data
Custnum = 2022, entrynum =3, entry data
Custnum = 2022, entrynum =4, entry data
Custnum = 3033, entrynum =1, entry data
Custnum = 3033, entrynum =2, entry data
the key is to have the entrynum to start at 1 each time, the rest I can handle.... I am at a loss right now, as I am down to one brain cell, and it's misfiring.....
Granted, tomorrow, when I wake up, I will prolly have a solution, but as always, I value all of your input and design suggestions. Yes, I know already that it's a one to many relationship from table 1 to table 2, I just want some alternate ways to do this. Thanks in advance for any insight you wish to give.
View 3 Replies
View Related
Jun 19, 2015
I have a qry which gives me this data:
date/code/hrs/amount
12/05/15 ABC 4 243.55
12/5/15 DEF 6 707.12
13/05/15 ABC 1 101.01
etc..
I'd like to sum the amount for each date. Is this possible?
I will be updating the table with new dates and want to eventually create a form where i set the dates and it does the query where I get the amount for the dates specified, e.g. 01/07/20 - 08/07/20
View 1 Replies
View Related
Jun 25, 2005
Hi
Many thanxs for replies, Like the idea of compacting.
We are traveling down the path of setting up a delete query.
This will hopefully delete the records but not the structure.
We are networked and a computer Warp2, writes data at Midnight to the sever, and then we use access on Windows NT to view the data.
We can then compact to maintain the database and not allow it to grow to 1.6 GB again.
Any ideas on the delete query thingy would be greatly appreciated.
:cool:
Many thanks for reading this post from a new starter.
View 2 Replies
View Related
