I dont know if this is possible in MS Access (2003) but this is what I want to do...
I want to create a database, each record has about 8 fields.
Usual basic stuff for a typical DB so far...
BUT! One of the fields is the category info, say "Item 1"
Usage:
Now another 5 records are made and are childen of "Item 1" and called..say "1a" (..b,c,d,e)
Item 1d record has 300 sub nodes to it.. say "Item 1d1" (2 to 300). And so on to eventualy make many records but linked by a huge tree like structure (like Treepad DB).
Finding Records.
Now, to find say "Item 5c22f5" I just want to see 1 drop down list, I then choose "Item 5" from it.
Then a second drop down list appears with..say 8 child nodes of Item 5, I choose "Item 5c", 3rd drop down appears, I choose "Item...22"
etc, etc. till I get to my "Item 5c22f5"
Now, while sellecting each node, some nodes in other brances contained hundreds of items but I never want to see them unless I go through their brances.
Adding new records:
eg: While working on "Item 5c22f5" I decide to add a record which will be "Item 5c22f6", I want to simply add it within the same branch but not be expose to the hundreds of other nodes/records.
Then I also would like to now add a sub of the current and call it "Item 5c22f6a". and also be be confided to the current branch. Or if I just go in 3 brances deep & see 5 levels each etc.
Finally, each record will have a field called "Notes" but I want rich text formating (like in word pad would be fine).
Maybe the ID tagging fields could be 3x on each record to show forward, current & back nodes???
What ever it takes.
I hope I've explain this OK.
I am new to MS Access so if it cant do it, I dont want to spend time learning it for nothing (at least not now).
This (if sugcessful) will be my universal, mind mapping info base. A baby I've been dreaming of for years now. Like Infohander meets TreePad meets Advance Data Managment (ADM) meets FileMaker.
Not availble anywhere in anyform as I know.
Thanks in advance. (sorry about the spelling)
I am building a database for a real estate agent, and as such have tables of client details/requirements and properties available for sale.
I intend building a query that will match up a number of fields (number of bedromms, price etc).
How can I best build my query so as not to continually match the same properties to the same clients? I imagine I will need some SIMPLE method of identifying/tagging the properties for sale when the client has either viewed the property or has deemed it unsuitable. This would then not bring up these records when I ran the query a second time.
A SIMPLE method of remveing the 'tag' would also be useful.
Any suggestions appreciated. The simpler the better (I am only a novice) !
I have some requirements whereby there are many reports that are needed to be given to the employer. I had an idea the other day and I wanted to run it by you guys. The reports can be categorized in a hierarchy (like everything else in this world of course):
=> Type => SubType => Name
Here are some examples of what I have currently:
Types => Finance, Customer, Services SubTypes => Invoicing (Finance), Proposals (Finance), Customer List (Customer), Recent Customers (Customer), Service Types (Services) Name => Print Invoice (Invoicing), Print Proposal (Proposals), etc, etc...
What I was thinking of doing is creating a few tables with relationships based on this, then one form from a query (that joins all the tables together) that serves as the report generating form. I would drive the cascading combo boxes by a bunch of hidden fields on the form itself.I think this would be much better than just writing a bunch of SQL statements behind combos on an unbound form. Wouldn't it?
First I will give background on the table and fields. Then I will explain the overall goal for creating of the TEST and KEYWORD 2 fields.
BACKGROUND PROPNUM: UNIQUE ID SECTION: Set of data responsible for a certain function EX: section 4 - expenses, section 5 - interest, etc. SEQUENCE: The order of the sytanx in that propnum's section QUALIFIER: Qualifies multiples set of syntax per section to differentiate other work (NOT REALLY IMPORTANT for the query) KEYWORD: A specific word that the program recognizes and treates the expression according to the key word * the quotes keyword is a continuation line and represent the keyword above it* EXPRESSION: are the variables that are treated by program according to the keyword
OVERALL GOAL: The main goal is to have a spreadsheet of variables used by the program to calculate it's end result. Which means KEYWORD & EXPRESSION by PROPNUM. THE PROBLEM is that the only way to tell that a quote keyword belongs is by having the sequence and section lined up. So my solution is to rename the quote keyword with the primary keyword and a number.
I am tasked with creating a scientific database of fish catches at various different sampling stations. I envisage the entry form having a hierarchy, ie year, season, date, sample station, species caught, and then the specific associated data with each species (weight, abundance, length etc) will be a distinct record.
As fas as data entry is concerned, I would like the user to not have to re-enter the same information over and over when entering multiple species entries at the same sampling station, on the same date and so on up the hierarchy.
:mad: I have created a database to hold information on non conformances we have with our suppliers, I am really stuck, what I am looking for is to put a field into a table which counts issues with the different suppliers, if anyone can help it would be much appreciated! Thanks
I know this is very simple but I am unable to get it work.
I have a Form (based on Table "Trades") and on the Form is a Command button. The Command Button has a VBA Code which selects the items from the Table and coverts to a text file - this works fine but the problem is that I am unable to repeat the process for all records in the table. I have tried using Dim as Intergers and upto a number of records, but it does all records as the first record.
I also tried this:
Dim db as Database set db as currentDb
- but it does not like it!
I know I have to set it as Dim rs as Recordset
but when I set rs as CurrentDb.Recordsets it does not like it!!
I have a little problem and do require help in fixing it. I am only 3% away from finishing the database (due today) but have a little bit of a problem on my hands. here it is:
I have a counter on my form that tracks which record is currently being viewed. the code for the counter is:
------counter code--------- 'Provide a record counter for using with ' custom navigation buttons (when not using ' Access built in navigation)
Dim rst1 As DAO.Recordset Dim lngCount As Long
Set rst1 = Me.RecordsetClone
With rst1 .MoveFirst .MoveLast lngCount = .RecordCount
End With
'Show the result of the record count in the text box (txtRecordNo) If Me.CurrentRecord = lngCount Then Command126.Enabled = False Command129.Enabled = False Else Command126.Enabled = True Command129.Enabled = True End If Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngCount & " record(s) for " & [CLIENT PREFIX] Me.Text292 = "Record " & Me.CurrentRecord & " of " & lngCount & " records" ----------counter code---------
On the same form, I have a delete button. The button deletes the current record that is being viewed (user prompted before it is deleted). The code for that button is as follows: ----delete code----- Private Sub command271_Click() On Error GoTo Err_cmdDelete_Click
DoCmd.SetWarnings False If MsgBox("Are you sure you want to delete this Audit and its associated measures?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdDeleteRecord
CLIENT_NAME.SetFocus End If Exit_command271_Click: DoCmd.SetWarnings True Exit Sub Err_cmdDelete_Click: MsgBox Err.Description Resume Exit_command271_Click End Sub -------delete code--------
This button also works fine with the exception of the following. THE MAIN PROBLEM: example I have a client that has 5 different entries in my database (5 diff. facilities) and I want to delete one of their facilities. I would simply use my 'search' form and then navigate to the facility that I would want to delete. If that facility is facility 1, 2, 3, or 4 then I have no problems. The system deletes the selected record and then simply displays the one after. The PROBLEM is when you are trying to delete the 5th record. When the fifth (in this example, it could be the 9th record for any other client, as long as it is the last one) is the one to be deleted, an error occurs. When you hit the debug button the following line from the counter is highlighted: .MoveLast
I have solved this problem and I no longer get an error message BUT when the last record is deleted, the database simply loads a new and empty form that will accept data. I DO NOT WANT THIS TO HAPPEN. I want it to be set up as follows: If the last record (not the ONLY record, but the last record on file) is the one that is to be deleted THEN delete that record and load the one prior If there is ONLY ONE record, then delete it and load the main form.
Hi... I searched quick, didn't find anythign, but you guys are so responsive... :D
Question: Part 1. On TimeSheet form, I want to click to "create new job", and the Jobs Form opens, at a new record. Currently it opens at record 1.
Part 2. I foresee that when I return from the modal job form, the old form (timesheet) would not have updated the combobox to show the jobname I just entered.
I guess this is what subforms are, because I keep reading about them, but dunno what they are.
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?
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.
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???
Not sure what the issue is here but i am trying to append a Job number from a main database into two connected databases. The number is a sequential number i have created and the primary key. It is an autonumber field(obviously). The format is J0000 and the numbers start from J0001
I am using two different append queries to append them into the external databases tables. However (and here i think lies the problem) the fields i am inserting them into are text fields with the same format J0000. When i view the numbers in these tables, they are displayed as J1 or J9. The 0's are missing. Any ideas.
Hey guys, I'm just wondering how I could limit a field to using from 2 - 20 letters. If I placed the letter "a", I'd get an error saying how at least 2 letters must be there, and a maximum of 20. How would I do this?
Lastly, how can I make it so when I enter data into a form, I can click a button which adds it all into the table? When I do it normally, it's automatically transferred into the table as I type it into the form.
This is incredibly simple I know, but I am a n00b at Access. ^_^
OK, I have a strange problem... This doesn't seem to work..... It did... then I made some changes.... Can someone clue me in on what I am doing wrong? A query, simply summing some values in fields... no biggy...... BUT... The field TotalHours: ([SundayHours]+[MondayHours]+[TuesdayHours]+[WednesdayHours]+[ThursdayHours]+[FridayHours]+[SaturdayHours])
So why isn't this working? Can I use a simple VBA statement to sum these fields?
I am trying to create an IN query (ACCESS) which will firstly show the customers table (specific details) then the orders details should be in the IN query, I formulated this but it won't work:
SELECT s.CustomerID, Address, City, PostalCode FROM Customers s WHERE s.OrderID IN (SELECT s.OrderID FROM Orders p WHERE s.OrderDate = '1996') AND s.OrderID IN (SELECT s.OrderID FROM Orders p WHERE s.OrderDate = '1997');
Here is the one I did to show the customers and orders who made orders in 1996 and 97:
SELECT Customers.CustomerID AS Customers_CustomerID, Orders.CustomerID AS Orders_CustomerID, Customers.Address, Customers.City, Customers.PostalCode, Orders.OrderID, Orders.OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE (((Orders.OrderDate) Between #1/1/1996# And #12/31/1997#));
it is probably something simple I am missing here! Sorry to keep asking :(
but I can't seem to figure it out. All I get is Null values when I run the query.
I have a form with a start text box and a end text box. I use the form name sucessfully in the criteria row so I know there is no problem with the form name. I also know there is no problem with the qry name. Does anyone see anything I am doing wrong?!?!
The query I am working in is labeled [qry_old_summary_date_range]. Just wanted to clarify that is not a problem.
I have a table with dates of deadlines and another with dates of submissions. For each deadline I want to know who submitted first. I figured that all I had to do was compute the difference between the dates and the select the minimum. However, Access rejects the Min() function in the WHERE clause. How do I avoid this?
I have a table (imported from excel) that is a pricing grid for blinds. The header row at the top is widths(mm) and the header row down the left is heights(mm). The data is the price. The import process makes the widths the column names.
height 600 800 1200 ... 300 $60 $80 $100 600 $80 $100 $120 900 $120 $140 $160 . . all I want to do is to take that table and make it flattened, like this:
I can't think how to do this in a query - I could take the flat version and create a crosstab to go the other way. Any clues - I don't want to change too much about the way the imported excel file looks becasue I have no control over that (I already discard miscellaneous heading and junk before I import it)
My Brain has completely lost all of it's gray matter.
I have two tables tbl_Employers and tbl_Services that are linked via a one to many relationship. Each Employer can have Many Services. The field i am useing in the relationship is EmployerID
If I build a simple select query using these tables I will see all of the employers that have a related record in the services table. Any employer could be listed there many times as per the relationship.
What i want is a simple count of how many employers actually have recieved a service.
Why is this so difficult for me to get my head around.
