I have a very strange request from a client who wants to do something simple that they can't because of poor database design (not mine!) . I can't think of an elegant solution so I'm wondering if anyone on here can?
They have multiple tables containing address information, for example they have one containing information about certain buildings. They can use the softcopy of the database to retrieve information about the building but for backup they now need a hardcopy.
Now part of the building information includes the alternative names for buildings (for example "City Museum" and "Art Muesum" might be different names for teh same building). The hard copy needs to have a line entry for both names (proper and alternative) and this is where the difficulty lies.
Using a simple example the table might be:
colour fruit
red apple
orange
yellow banana
The output I would need in the report is therefore:
Red (Apple)
Apple (Red)
Orange ()
Yellow (banana)
Banana (Yellow)
Now I may be being dense but I can't see how I can do this! All help very gratefully recieved :)
Hi all, Been having this problem for a long time. Everytime my function is being called, a new tuple gets inserted into Table "Stats" containing the below mentioned data.
sSQL = "SELECT * FROM Stats WHERE UserID = '"&sUserID&"'" rs.Open sSQL,,,adCmdTable 'Save the data rs.AddNew rs("JaID") = lIdJa rs("JsID") = lIdJs rs("LanID") = lIdLan rs("ResID") = lIdRes rs("WsID") = lIdWs rs("Date") = Date rs("Time") = Time rs("IP") = sIP rs("UserID") = sUserID rs.Update
Each time a person click on my site, one tuple will be inserted into the Table "Stats". Time which is one of the column of the Table "Stats" is inserted into the table each time the function is called The puzzling thing is how is it possible for the time to be the same for row 5,6 & 7. This happens very often and i have totally no explantion for it. I tried changing the time format, date format, however i always get this. Please advise. Thanks a lot
I built my wife a database to use for her work that has worked fine for a good while now. Just yesterday, one of the make table queries began to act peculiar. Suddenly, whenever you try to run it it gives the following error:
"The Microsoft Jet database engine has stopped the process because you and another user are attempting to change the same data at the same time."
This is on a computer at our home so obviously no one else is trying to access it. I have checked and there are no other instances of the database running (I've even rebooted to be sure). I have checked when it was shut down and didn't see a locking file associated with the database. It is a single make table query feeding directly from another table.
As I said, it was working fine for a good while. I'm out of ideas so can anyone help????:confused:
This to me seems absolutely bizzare! It happens in both Access 97 and Access 2003 so I know its not a version issue.
I have a Access database with a link table to a spreadsheet. I have written a query which displays data from this link table which filters out blank rows, the spreadsheet header etc and just leaves me with the data I want to import which first time I ran it was perfect and exactly what I wanted. However, it turns out that this query only works intermittently. If I run it subsequently I get a "numeric field overflow" error message. But if I go into the query design and resave it without making any changes it will then run again!!! Running it thereafter will bring back up the error message until I resave it again!!! Why is this? Any ideas anyone? I dont want to have to keep resaving it to run it.
Actually thinking about it unless I could of course resave it using code???
I'll try to make this as simple as possible, but please ask for clarification if I'm unclear:
*I have one pass-through query to Informix that pulls all available class schedule information. *I have another local query that uses information in a local table to restrict that query to a particular year and semester, chosen by the user. *I have a function TimeConv that takes the numeric value used in the Informix db to store times (e.g., "1300") and converts it into a real time value (e.g., "1:00:00 PM").
The problem is this: While the TimeConv function by itself always works in the local query, if I try to perform any comparisons on it, I get the error message "Invalid Use of Null". There are no null records in the restricted data. If I put the semester and year restriction directly into the pass-through query instead of the local query, the comparison operations work just fine. (But it's important that the user be able to change this without editing the SQL directly.) Only when that restriction is in the local query do I get the error message.
I've tried several ways of filtering out Null values from both queries, and it doesn't seem to make any difference. If I Nz() the fields before passing them to the TimeConv function, I instead get the error "Invalid procedure or argument call", I believe because it insists on reading "0000" as "0", which is too few digits for the function to work.
I get the same results if I try to run it through yet a third (local) query.
Relevant code and SQL below. I'm really stumped on this one. Thanks in advance for any suggestions!
WHERE t1.cat="UG93" and t1.crs_no=t2.crs_no and t1.cat=t2.cat and t1.crs_no=t8.crs_no and t1.cat=t8.cat and t1.yr=t8.yr and t1.sess=t8.sess and t1.sec_no=t8.sec_no and t8.mtg_no=t3.mtg_no and t1.crs_no=t4.crs_no and t1.cat=t4.cat and t1.yr=t4.yr and t1.sess=t4.sess and t1.sec_no=t4.sec_no and t1.fac_id=t5.id and t2.crs_no NOT IN ("JFESSA", "FRESSA", "SOESSA", "JRESSA", "MAESSA", "MAORAL", "DEPO", "DEPO2", "SRESSA", "SRORAL") and t2.dept=t6.dept and t7.prog=t2.prog and t7.sess=t1.sess and t7.yr=t1.yr and t5.id=t9.id; If I add this criteria, functionally equivalent to the first WHERE statement in the local query below, it all works fine: AND t1.yr=2005 AND t1.sess="SU"
Local Query (qryCatalog): SELECT qpj1.days, qpj1.room, qpj1.crs_no, qpj1.bldg, qpj1.mtg_no, qpj1.sec_no, qpj1.beg_date, qpj1.end_date, qpj1.beg_tm, qpj1.end_tm, qpj1.sex, qpj1.abbr_name, DateDiff('h',TimeConv([beg_tm]),TimeConv([end_tm])) AS NoLongLab
FROM qryPassJoin1 AS qpj1, tblSettings
WHERE (((qpj1.beg_tm)>0) AND ((CInt([yr]))=CInt([tblSettings].[catyear])) AND ((qpj1.sess)=[tblsettings].[catsess]));
The comparison criterion I would like to add here is: AND ((DateDiff('h',TimeConv([beg_tm]),TimeConv([end_tm])))<=2)
Function TimeConv: Public Function timeconv(numtime As Variant) As Date timeconv = CDate(Left([numtime], (Len([numtime]) - 2)) & ":" & Right([numtime], 2)) End Function
I have a form that opened perfectly before from a click event with a standard link criteria lookup function, and...it's suddenly stopped working!
Before the form opens I get a small query box asking: Forms!support!fault ref
In other words it's asking me to enter the value it should've got from the link criteria lookup. Whatever I enter, the form then loads with the correct record.
Is there a known Access corruption issue, or does anyone have any ideas?
This is driving me crazy! I have some code that has a list of items in a listview. When I right click it shows a pop-up right click menu with a macro. Then I select an item in the list that says Rename. After ensuring a single element is selected the StartLabelEdit method is called on my list view. Then I can begin editing. Normally this works fine. However, if I first double click a list item. Then I enter my double click list view event handler and from there I open a form. Later if I close that form and then try right-clicking and selecting rename. This time after StartLabelEdit is called, the BeforeLabelEdit event is never called. The whole selected line blinks momentarily and then wherever my cursor is at that moment every item between it and the originally selected item becomes selected. If I move my mouse around the selection changes to be between the originally selected item and the one my mouse is over as it moves. It's as though I had clicked the left mouse button down but didn't let go of it and then started moving the mouse around. If I left click and release again it seems to get out of this mode, but I am still not able to ever manually invoke the StartLabelEdit method after this point. If I close the form and re-open it, it works fine. (I've tried calling refresh on the list view but this had no effect.)
Here's the really weird part. If instead of double-clicking to open a form, I right click and select a menu option to call the exact samed double click event method. Everything works fine as you would expect. Then I tried another test. I made the double click method call another method to open the form. When I did this and tried the same alternate way of opening a form, it did not work and the same original error occurred again. This weird behavior is bizarre and I have no real idea how to troubleshoot it any further. :mad:
Has anyone ever seen or heard of something like this where StartLabelEdit goes haywire? Please help me if you can.
I'm working with MS access only in very basic things. Rigth now I need to look some information in a Table that was created in MS Access 2000.I don't know if this is a problem with MS Access 2003, because i still have some ones. 1) The Zoom tool is not accessible. 2) The order of the items, change all the time and I need to sort them every time. If someone could help me in this matter, I'll appreciate so much
Hello all. This is my first post on here after reading many of the other threads as I try - often in vain - to pick up access.
I am in the process of building a couple of databases, the main one being one that is an amalgamation of 4 other/older mdb's. All tables/queries etc were simply imported from these older defunct mdb's and then are archived off every month so they have 3months data and no more in them. The only thing is when it comes to problem solving and/or new queries, it is impossible to tell which tables/queries relate to which sections of the Db.
So my question to you is can I rename tables and queries in any way so that any queries that are using these tables are updated at the sametime, and if so how is it done? I have asked my colleague who is working with me to develope my understanding of access and he is at a loss. We are in the process of trying the old fashioned way ..... getting a book from the libarary ... but thought I'd try this new fangled internet thingy first :D . The thought of having to trawl through around 100 tables and queries to rename or even re write the entire query/formulae is so daunting, I simply dont have that amount of time. Im using Access 97 and would greatly appreciate any help on this. Thanx
I have a query which selects a complete list of companynames and producttypes, and another query which selects a few companynames and producttypes. What would be the query to select the companynames and producttypes from the first query that do not include the companynames and producttypes from the second query?
I have been asked to look at setting up and access database to do the following
To keep a record of pupils and 9 tasks the pupils have to do and also the total amount of time it took a pupil to do this task They have a total of 25 Hours to do all tasks but this is spread out over the year.
What I need is someone to tell me the best table layout to do this
The pupil record has to have the following First Name Surname Form Total Hours taken for all 9 Objectives A description of what the pupil did to achive each Objectives record if that task is complete Also Date Task was Complete
I have done a test database with just one table in it and inside that table had all of the above but apart from name, form and total hours I repeated the rest nine times. This just does not seem right
What I want is to pull up the pupil name and then select Task say from a Drop down box this would then insert a new field if it did not allready exist in pupil recored and then you can fill in the task details of course if the field/s all ready existed then to open that up to allow you to update this
It would then need to update the total Hours field in the pupil record with the hours it took to do that task/objective.
I have basic Access knowledge but i cannot think of best way to achive this can anyone help please
Hi All, Can anyone help me count postcode instances. I have a query that looks at my customers table (tbl_Customer_Details) post code field (PostCode), at the moment I have got it to strip out the right side of the postcode leaving me the left district side eg. HG12 8EN becomes HG12. I would then like to count how many times each postcode instance occurs so I can create a report on the result so I can track which district the customers are coming from. I hope this explains the problem. Any help would be much appreciated as I promised the Boss this for tomorrow....:confused:
this is my SQL so far, it strips out the left of the postcode:
SELECT tbl_Customer_Details.PostCode, Left([Postcode],4) AS Code FROM tbl_Customer_Details GROUP BY tbl_Customer_Details.PostCode;
I have a Access 2000 db, been working fine for 6 months+ and suddenly today, two out of three forms give "not a valid password" error on trying to open them.
There has never been a password set on the db, nor on the forms - and the same user who used it successfully yesterday now gets this error, along with any other users.
What has happened and how do i fix it? All ideas welcome!!
Each time a person enters a part number into the form they will have an option to pick and load other forms. Looking to have 1 form with a cmd button that when pressed will go out and retrieve an object or objects (spreadsheet, word document and etc) and then place in the OLC’s and rename and store this object on a drive with the name of the part number and form name I want the templates never to change or be updated. Only the objects store on the drive after the command button is pressed or the Ole object is double clicked can be edit.
I've tried hyperlinks, do not want to have operators do save as, also I would have to have all the forms entered for each part number prior to versus just using templates and having them be stored.
Example of save forms names c:mydocuments racking12341234-Template-1 c:mydocuments racking56785678-Template-2
Not even sure this can be accomplished. See attachment for a visual idea.
I will explain this situation using a scenario. Let's assumed that i'm working on a library system where i need a loan form that will only show books that are available for loan. In this case, this form has a main form that shows library member's details and its subform shows the details of the books that are loan by library member. In this subform, one of the fields (ISBN no) displays its values using a combo box. Once a value is selected from this combo box, values in other fields found in the subform will be shown too. These values are based on a table that contain books info, where only the value (ISBN no) in the combo box comes from a query. This query will only show books that are available for loan.
If a library member wants to borrow 'booktitle1' then this book will not be available to be loan by other members and assumed that there is only one 'booktitle1'.
I tried to update the field manually by changing the status field every time the book is loan out. I hope to solve this problem in an effective way.
I am new with access and i have difficulty to explain it in a much better way. Sorry, if my description cause any sort of confusion.
These are the tables.
book_info (table1) ------------------------------- bookTitle ---------- text ISBN(pkey) ------- text authorName ------ text category ---------- text dateReceived ---- date publisher ---------- text status -------------- text
user_info (table2) -------------------------------- name --------------- text userID(pKey) ------ text address ------------ text tel ------------------ number hp ------------------ number occupation -------- text DOB --------------- date
loan_info (table3) ------------------------------ userID(fKey) ------ text ISBN(fkey) -------- text dateReturned ---- date dateBorrowed ---- date dateDue ---------- date remarks ----------- text
I am thinking of adding a Request form to my database so that if the user wants to add a code to the system, they could fill out the form click send and i would then be able to retrieve all the requests via a report.
I have designed the form to something like what i am after...
i have a few issue's here:
A)
i have set up an option wizard so that user can select if this is a new code or a failure mode request
if the user selects "New Event Code" i want the the text box and label for Eventcode Description to become visible and all others to become inactive.
i will explain what happens if the user chooses "Failure Modes" in a minute.
B) I have no idea what is the best method to sort the following section, if you look at my form at the bottom (Highlighted in red) i need this to be a continuous option as the user may wish to add more than one Failure mode to an Event Code.
C) going back to my options if the user select "Failure Modes" i need the bottom section to become active.
D) If that was not bad enough i have no idea on how to store this in a table.
Definitions:
Event Code ---> Top Line Code
Failure Mode --> Is the Event code description but broken down further
I have noticed diagrams showing Microsoft SQL Server accepting SQL statements sent by “SQL Requesters” over connections. I would like to know if Microsoft Access can be used in that fashion?
Put another way, can Access be configured to accept an SQL statement that another program (e.g. a VBA program in a VBA enabled 3rd party app) creates?
Currently, my VBA program instantiates an Access database object and then manipulates it (I just add a record to one of the tables), then closes and destroys it. This solution seems fragile (it stops working—I can explain more if needed). I would like to know if the technique inferred by my question would be more reliable.
i need to design and build a access database for school where staff for a business could request holidays with there holidays then appearing on sum sort of calendar which can be viewed.
does nebody know where i can find an example to help me with my desing?
I have two tables; "Section_Failure_Mode" and "Risk_Assessment". They are linked by a common field called section_failure_mode_id. I would like to write a query where you can delete every record in "Risk_Assessment" where the checkbox (called Selected) is not selected (i.e. equal to 0) in the table "Section_Failure_Mode". However, I do not want any records to be deleted from "Section_Failure_Mode", only from "Risk_Assessment".
I have put a link to the screen cap of the query I have tried below. Is this the correct way to do it?
I finally got TreeView to work, by downloading the example database from this (http://www.access-programmers.co.uk/forums/showthread.php?t=80805&highlight=treeview) thread. I haven't had as much luck with ListView, though. Does anyone have a database they could post that has a working listview? I'd greatly appreciate it!!
I'm Italian and I started playing (I don't work on it) with access to create an Airline Simulator for MS Flight Simulator. My "program" read data imported in an Access table by a secondary program, and work on it. :cool:
One or Two of my Beta testers found this problems:
1) function "date()" is not recognized in Access 2003 and 2007. in 2003 is read as "date"; in access 2007 is read "date(". The one with Access 2007 resolved the problem reinstalling the Office Suite 3 times. The third was working. nothing else changed. :mad:
2) Access request the OFFOWC.DLL, in an Access2003 version. But only one user... and that .dll is from Access 2007!! :confused:
I wrote the database in Access 2007 and in Access 2003, the file is obviosly Access 2003 format.
I attach it here, for someone that wanna try if he has the same problem. PS due to size of the file, it's a zip containing a .rar file, that contains the .mdb (zip only was 660 Kb)
I am looking for assistance creating a query on a date field that will go back three business days from the current date. It must not show inclusive days, as in yesterday and the day before. Just the 3rd day past and not counting weekends. Also holidays would need to be considered. Thanks for any assistance RW
