I have a database that keeps track of training hours for each employee. The Training Length is formated as Short Time. I just figured out that short time can only go up to 23:59:59. Some of my trainings will be over that. Does anyone know a way to get around other than splitting up my hours and Mins in the table?
View RepliesWhat would be the best way to store data in a database that could change but without effecting previous linked records?
Let me try & explain a little better.
I have a table that lists materials with the cost price & sell price of each item
When I use that info to create a record I will only store the id no that the material is related to then link the cost fields to display the prices without storing the data again.
The problem I face is that in the future if any of the costs are changed in the main table then all linked records will also show the new amounts.
what's the best way around this?
Do I simply store the actual costs in the records when they are created allowing only the current costs to be used ?
I want the program to input an entry for every item with a value ranging from 1-300, according to sales data, according to the associated style.
I have to sort large amounts of Data and format them into a way I can deal with it.
I get two inventory lists twice a week from 16 stores.
I sort this data in two ways.
I do this by comparing an inventory list to a list of 300 of the best selling styles.
Currently, I input one list of inventory data in excel and then organize the items manually and respectively to sales data list in columns. Top 25 styles, 50, 100, 200, 300.
When I am finished, I have a certain amount of items in the top 25 styles, a certain amount in top 50, and so on. I color code the columns and write down the quantities and percentage of each category in the list..
if this is still confusing:
I have two tables.
one has the style numbers of which several items are in the category of a style number.
and one is the list I want to sort the previous list by. It has 300 styles that sold the best. they are rated from 1 to 300.
I want to organize all my items in each list by the 300 style list.
I have to do this twice a week, for 16 stores, for two separate reports.
ongoing basis.
is there a way of having the program do the numbers for me?
I am entering the data in excel physically . Is it possible to do this another way?
I am trying to sort the sales data sheet INTO the columns I have set up AND according to the styles that each piece of data has in common.
Hi everyone,
What is the best solution to deal with different currencies in an Access Application? After creating a table that includes currencies codes I want to use them in the form to present and calculate prices.
After choosing the currency from a combobox, the price field must be presented in numeric values with the chosen currency code.
Thanks in advance,
Best Regards
I have a database that has the basic tables of products, accounts and orders.
I need to figure out how to deal with returns.
Do I create a separate table and link that to the orders table for returns?
I haven't made such a large database file so I was curious if anyone had any information on how to lead me in the right direction. Or if there were any examples. (I've looked around but haven't completely figured out the best way to handle this.)
Thanks!
This is for anyone who has made a form with a lot of check boxes and wants to make a report out of them thats decent.Hopefully this simple example file is enough to assist people.Keywords:Checkbox Checkboxes report check boxes box
View 1 Replies View RelatedI am using the following to extract date information from a text field as part of a query.
Narrr_Date:CDate(Left(Mid([si_narr_t],Instr([si_narr_t]," ")+1), Instr(Mid([si_narr_t],Instr([si_narr_t]," ")+1)," ")-1))
Due to users not putting the correct information in I will get the #Error in the results of the query.
I need to do a DateDiff on the results so I need to get rid of any values that come up with #Error.
Since I cannot ensure the date extracted by the query is correct, how do I deal with the error?
Setup
i have a form (frmCustomers) with a subform (sfrmJobs). As you might have guessed, it keeps track of customers and jobs. One customer may have many jobs. within sfrmJobs are about 300 or so controls for about 300 or so fields (mostly yes/no boxes) and at the moment contains two subforms of its own. customers have an autonumber CustomerID and jobs have a user-entered numeric JobID.
Goal
it has been brought to my attention that some jobs may just not work out and must be deemed as "cancelled." however, my client wishes to retain these records rather than delete them. in order to maintain the integrity of the reports, i would like to remove these records from the non-cancelled customer database and place them elsewhere, but still retain the ability to review them if need be (but NOT to modify them).
Problem
the job can be cancelled at any point down the line, meaning that a job could have nearly no info or nearly all the info, and everything in between. each job record has a ton of fields. i don't know how to cut and paste an entire record (except for doing it field by field, which could take forever in this instance) into a new table. in this case, it would be several records, since i have a list of materials pertinent to each job as well as a list of services necessary for each job - all of which would have to be removed and placed elsewhere.
Cry for Help
what is the best way to go about this? some ideas i had were creating a parallel table for each table used in recording all of the job information and just manually (well, via VBA and recordsets) copying, pasting, then deleting. another is creating a checkbox that when clicked disables every control for the cancelled job (but oy vey that's a lot of controls to code the disabling for) and then have it reflected in my reports that these are only from the NON-cancelled jobs. does anyone else have an idea?
help?
thanks in advance
-Jason
Hello all,
Ok, let's see if i can explain this so that anyone trying help can understand. I have a form that's unbound (Music) with a subform (Arist Info)that is bound. The subform also contains two other Subforms (bound). The subform (Artist Info) contains a few Macros set up to do
some general searches with some Option groups and also one cmb box with vb code that access wizard wrote up. They all work great, with no problems at all when I open up the subform all by itself. It's when Main form is open where i run into some glitches. In then option groups all tgl & radio buttons work with exception to the two named ALL. I get a ballon message stating There is no field named
'GenreGroup or AlphaGroup' in the current record. Genre and AlphaGroup are the names of the option groups. The cmb box dosen't even work at all. There ars some other problems also but i'll ask for help later if i can reslove this. LOL I've attached a bare bone sample db to look at. I thought I was doing great learning new things every day, till I ran into this one.
Thanks for your help
Rich M.
I have a database that was populated using many excel sheets and one of the fields [partDescription] uses the # character over and over to represent "lbs". I realize that this is a wild card for digits in VB. Is there a quick fix for finding and replacing this character?
View 2 Replies View RelatedHello...
I created a button that opens a link to a URL and copies information from a record to the clipboard to paste on this webpage.
The users are not using it correctly (they need to close the page each time they need to do the next record).
what i want to do is to close ALL IExplore windows via access if there are any open then open the URL again. Sounds pointless but we have to close it because of security.
Thanks.
I have an imported file coming from another department and the one of the field names I need to use has spaces. I have tried " " and [] but they don't work. What is the proper syntax for this?
origfield = rst.Fields("FVH PLAN")
Thanks.
I have a form that I designed that contains two subforms.The first subform is an entry subform so you can post the current client visits.The second subform I want to just display all prior client visits and make updates or deletions if needed.
It does not appear that I can make the first subform entry= yes additions allowed=YES in the properties.The second subform I want entry=No and addition=no..When I pull up the properties sheet for each of the subforms these options do not appear. They appear only on the main FORM, FORM property sheet. I thought I could create two new forms with the properties I desire and then copy them into the main form.
I have a database with student information that contains tables about their dissertation and graduation information. There is a field "academic year" noting their graduation year. I have a form for data entry that my data entry person likes to use in datasheet view. The form is based on a query that contains only current academic year records. When a new academic year arrives, I plan to create a new query for the form to feed from. i.e., "hiding" past academic year records on the form in datasheet view.
View 1 Replies View RelatedHi all,
I am in desperate need of some advice. Today I set to work building some math functions in my queries to calculate delays/turnaround times for work, etc.
I have found that where there are null values in my table - the calculation seems to break down.
Let me explain...
In it's simplest form (I have removed some fields and will deal with DATE only, not TIME which is a seperate field) I have the following -
Date_Received
Date_Started
Date_Pended
Date_Recommenced
Date_Completed
I have the following expressions in my query -
Turnaround: Date_Completed - Date_Received
TimePended: Date_Recommenced - Date_Pended
TimeInactive: Date_Started - Date_Received
Workrate: Turnaround - (TimePended + TimeInactive)
The query calculation will only work when all fields contain a value.
For instance, sometimes the work may be started, then completed. Skipping the 'Pended' and 'Recommenced' steps.
Can anyone tell me what I need to include in my maths for Access to treat a null value in the table as zero? Or any other approach.
Please help!!!!!
Thanks
Rob :eek:
I have a large amount of field data that was taken by a data logger. Specifically, The datalogger has five temperature probes embedded in soil, and takes a measurement automatically once every hour for each probe. So the table has a Time field, and then a Temp1, Temp2,... Temp5 field. I need to be able to average the temperature fields. This within itself is easy enough, I just tell the query to find (Temp1+Temp2+...+Temp5)/5. However, unfortunately, because it is field data is is very common for one (or more) of the ports to be malfunctioning at any one measurement time for a number of reasons.
If, for instance, Port one does not have a value, then the formula I entered will not work, as it can't add a null value to numbers, and otherwise It would be dividing by five whereas there were only four values to average.
I am trying to use the expression below to transpose strings of 4-6 numbers (140504) to a string of 8 numbers with "20" in front (20140504). Some of the [Receive_Date] fields are empty or are 0, and in those cases, I need the expression to return a null value.
New_Receive_Date: IIf([Receive_Date]=0,"",IIf([Receive_Date]="","",Format([Receive_Date_YMD],"000000")+20000000))
The Format(...) part of the expression works fine on its own, but I am new to dealing with null values in my expressions, and when I add the rest of the expression, all '#Error' values are returned.
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.
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
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 RelatedI 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
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 RelatedHow 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 RelatedHi
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.
Hi all, apologies for the long post but this is a long nightmare!
Using A2k on Win2k...Due to roll out a DB today, just made my final tweaks and decided to compact/backup as have done many times before. It's not a split DB but it is secure so I copied the mdw onto my desktop and accessed it using a special compact shortcut which points to the desktop copy DB not the one on the network. When I compacted though Access went about it's business for a while and then threw up the dreaded "Network connection may be Lost" error message (roughly translates as "your database is nicely corrupt now") which I have had in the past when I tried to compact on the network. I've never had this error before compacting locally but anyway I tried again and same error message. I tried opening some forms and stuff and sure enought the DB was corrupt. No probs I thought, I can just go back to the original and start the process again. I used the normal shorcut to open the original DB just to check everything was ok and the same error message appeared with same problems. Minor panic ensued and I thought i'd got the shortcuts mixed up or something... I hadn't, Both DB's were affected.
Next option. Create a new DB and import all the objects. I did that and re-set all the permissions and the "Network Connection Lost" message dissapeared but some really strange things were happening. Forms opened but the buttons on them would do nothing. Then the forms Close button didn't work and the database wouldn't close.
I forced my way out of the DB and re-started my machine. Couldn't even log in to windows. Now at another machine and can log in to windows but DB is still having same problems. My instict tells me that the problem is something to do with the workgroup file becuase how else could compacting a copy corrupt the original? The only common link is the mdw.
Should I re-create the mdw and then try opening/importing the DB objects again? Or is there any way I can un-secure the DB and then re-secure it later?
Any thoughts would be much appreciated.
Thanks, Tom
Hi,
I am currently using a large Access 2002 database in order to generate various reports.
My two main tables are despatches and returns from which they hold around 1,200,000 records and 100,000 records respectively.
The problem I have is that the reports use various expressions within various queries to generate a single result (percentages per channel etc.)
This is obviously very time consuming and it may take up to around 10 to 15 minutes to get a result from a chain of around 5 queries.
Can anyone suggest alternative methods to generate similar results in quicker time?
(Please note that the tables can not be downsized and records can not be archived)
Many thanks
:)