I'm building a simple database for my church but I'm uncertain how to inter-relate/connect the tables in order to pull some data into a report.
In one table I have the primary parish member, his or her spouse, with their birthdays, anniversary date and other information (e.g., address, phone numbers, e-mail, etc.).
In another table I have their children, birthdates and other information.
How do I create one report with the names and birthdays from both tables?
Right at the moment i'm baisically designing and building a software licensing management system fro my boss at the moment but I have had a snag, the face that its 3pm on a friday is not helping with my concentration either.
Ok so my problem is that i've got 3 tables (tblUser_Informantion, tblSoftware_Information & tblHardware_Information) all of which are related via a 4th table (tblReference) which holds the primary keys of each of the other 3 tables mentioned. For all intents and purpose's the primary keys are named according to their tables i.e. (User_ID, Software_ID, Hardware_ID, Reference_ID etc...)
On my main form a User can select via a cmbo-box or search for a Person via Surname or search via Machine Asset number. Upon selection this fills a number of pre determinate box's below that containing you typical standard information. However under this is a subform showing what software the user has installed on the machine and on an alternate tab it shows the software license's purchased and assigned to that machine. So far so good.
However I want to create a button in which the user can add software for that particular person in the fields. The user will select the software from a listbox filtered by combo boxes filtering by Software Vendor etc... My problem is that when the user has selected this software title either via double click or an update button i want it to update the tblReference table. So baisically i need this button to grab the User_ID, Software_ID and Hardware_ID and place it in the tblReference table as a new record.
Hopefully i have explained this well enough for people to understand below is a jpg of my current main screen so you can hopefully get a jist of what im trying to say and do. However im open to suggestions if people feel they know a much better way of achieving my goal.. as i sed its friday afternoon and im feeling particulary slow so you'll have to forgive me!
I am new to MS access 2007 but not new to Access. I am trying to open a form in the design view but that option is disabled along with other menu options. I checked the Ribbon and it appears to be a custom toolbar. I deleted that out of the box but I'm still unable to view the design of any objects. How do I enable the menu items?
I got a form called "Make quote". In this form a user can select a product from a line of fields. This "line" contains: article code, product, price and quantity the customer requests.
Each single quote can contain a very different amount of products. Some quotes just have 1 product, other quotes have 25 products.
I'm looking for a way to let the user add as many "lines" as necessary for the quote. BUT I don't want him to need to go into design view.
I'm thinking of maybe adding a button to the form which does this, but I'm not sure how.
On occasion my access database all of a sudden begins to operate very slowly, I notice the queries take a much longer time to run.
Last week it suddenly began to operate very slowly after idling in excess of 30 mins
Today when I change the screen to design view and then back to form view once again it begins to operate very slowly. (yesterday was working fine on my desktop at home, I have not made any changes to the database with exception to saving and using the file this morning on the shared drive at work) If I shut down and re open it operates well but once again changing to design view causes the same issue. Sometimes compact and repair rectifies sometimes not.
I want to export details from a table in access to a word document or any other document that will keep the current design and let me make changes afterwards.
I am planning my new DB and am contemplating the best design. It will be used for warehouse stock rotation and control of pallets. I want to track each pallet (product/time in/time out etc) to each pallet space within the warehouse. There are a total of 400 pallet spaces or 'locations' as i will refer to them as.
Now, would it be possible for me to have a table set up for each location? Will access object to having 400 tables in my data base?? Is there a limit?
I have a split database made in Access 2007. Each user gets their own copy of the frontend from a script. I wanted to be able to edit the design view of the backend tables even if people were using the database so I made all the forms use snapshot source and only allowed data updates through VBA macro update queries. Having any form open locks the backend source table from being edited. In fact, I've found that just having a normal snapshot query open causes the message "Either an object bound to table 'whatever' is open or another user has the table open. Do you want to open the table as read-only?"
Is there some way to have a table be the source for a form or query, but still have it designable under most circumstances?
Attempted to late-bind a recordset on form load; result was the same:
Code: Set rs = CurrentDb.OpenRecordset("Select redacted as ft from tblRedacted ", dbOpenSnapshot, dbReadOnly) Set Me.Recordset = rs Set rs = Nothing
I'm working on a login form and *not thinking* I removed access to the submenu. I had already set the form up so that you either login correctly or exit the program. I'm working in code for the form and I know it's possible to reactivate the menu in code, I just don't know how. And I can't switch to design mode. The form is set up to not allow anything to happen until the correct login has been entered, and I either had a typo when I set up my test login or the program is bugged and only says it's incorrect....
I now understand that when opening and saving crosstab queries Access (2010) runs that query to ascertain the column names. Unless you hard-code them. Running the query takes at least 20 minutes. I have hard-coded where I can, but one report takes arbitrary dates so I can't hard-code them.
I believe that turning off AutoCorrect might make a difference to whether the query runs - but I don't want to turn it off.
I have a sub form in DataSheet view and I would like to lock the design so that the User should not change the layout
Even when I set the property of the Datasheet “Allow Design Changes: Design View Only” users are able to unhide the hidden columns and they can also change the size of the column by dragging the column end line
Does any one know how to lock the design of datasheet ( I am using this sub form datasheet for data entry but do not want users to change the layout)
I'm going to make up names and values -- I'm interested in the structure.
Table ALPHA:
COLA DAT1 DAT2 DAT3 1 5 7 9 2 4 14 8
Table BETA:
COLA_IND DAT1 DAT2 DATN 1 a b c
Table CHARLIE:
COLA_IND DAT1 DAT2 2 d e
Table DELTA:
COLA_IND DAT1 DAT2 2 f g
Ok, the idea here is that the data in table ALPHA contains data with COLA a key such that selecting 2 would yield the data row "4, 14, 8."
Now, COLA_IND is a "COLA" key for table ALPHA (sorry, I can never remember which side is called the foreign key). So, from tables BETA, CHARLIE and DELTA, I can access any row in ALPHA based on the key "COLA_IND"
Here's the fun part. When I build my query, it wants to use an inner join on the keys from all these tables... In order words:
SELECT blah blah blah INNER JOIN blah ON (ALPHA.COLA=BETA.COLA_IND) AND (ALPHA.COLA=CHARLIE.COLA_IND) AND (ALPHA.COLA=DELTA.COLA_IND)
What I'm looking to do is expand BETA, CHARLIE and DELTA with the information from ALPHA based on the key COLA_IND. I don't think this is doing what I want.
I've got this problem and im 95% sure its going to need a query in order to achieve this answer im looking for.
I'm creating a Software Licensing Management db and its all working lovely. However my only problem remains is the graphical representation (text box within one of the forms) of howmany licenses are/aren't(doesnt matter if this number is a +/- number) available.
In order to achieve this answer I dont think you'l need the table structure of any of my tables other than these two:
However I would like to add a column to either of these tables named 'Availability' or something similar which will show the licenses available.
I've tried a number of Update/Append queries but all have failed. I want this Available running variable held within the table due to it not changing to much of the current db design as i baisically finished the project and they asked for it! any ideas of how to efficiently achieve this will be much appreciated!
I'm looking for a keyboard shortcut to expand the columns in "design view" of a query.
What I mean by this is rather than selecting all of the columns and double clicking to see the entire text, I'd like to be able to a shortcut.
The entire process as I see it involves 3 steps so I will need thesolution to the 3rd step.
(1) [ctrl+spacebar] to select initial column (2) [shift+arrows] to select all of the columns I need (3) [keyboard shortcut] will expand all of the columns "field" names to the size of the column heading
Alternatively, if you know of a shortcut that will expand the columns without having to select them first I'll take it!!
i found myself trying to write this outer join query and i realised that probably i need to improve my design rather than write the query! so here goes
i've created a form which a user can navigate through to look at various jobs which have been done (its a loft insulation database)
now one of the things the users need to do is to arbitarily choose jobs for which they are going to phone up the customer and check the work was completed satisfactorily for QA purposes
the table containing information about the jobs has a yes/no field "selectedForQA" which comes up as a checkbox on the form so they can just check the jobs they decide to check up on
i wrote a query which runs on click of a a command button "send selected to QA" which is just an append query sticking primary keys of the jobs table into another table TBL_QAList which has other fields 'contacted', 'satisfactory' and 'notes' for them to check when they have phoned the customer
of course i can't simply rely on the Append query because the jobs previouly loaded into QAList will still be checked and the append will try and append the same data twice cauzing a primary key violation
i don't doubt that it must be possible to only append those records which aren't already in the table (i believe its an outer join to return the records in neither of two tables? - i could then append the results of that query?) but anyhow the fact that i'd have to do something of that sort suggests to me my design is screwed up in the first place
i know that its bad to have the same data in two tables but all i would have replicated here would be the primary key and when i present the data to the user i use the table relationships to get them all the contact details etc that doesn't get copied across tables
maybe the better solution is to have no QAList table just the jobs table (with sentToQA field) and a QADone table - then the form the user sees can get its data from a query which finds those jobs for which Jobs.selectedForQA = True AND which are not in the QADone table and when they check the done box on their form the record gets added to QADone
have i just answered my own question or is there a yet better way of doing this? or was i on the right track in the first place?
I have 5 plants (when this was orginally created there was 1) I need to develop a report showing defect by suppliers. A user would select a supplier from a combo box, and using a query would show each reject number by supplier. I can get query to work, for only 1 plant as soon as I select another plant no records display. Any ideas
I have some tables that are set up as follows (these are not the actual names of the tables and fields, I'm using generic names so that I don't get a bunch of questions about the setup to begin with; I believe they are set up in the best possible way):
Table1 has a one to many relationship with Table2 Table1 has a one to many relationship with Table3 Table2 has a many to many relationship with Table3
Fields for Table1 Table1_ID Field1Table1
Fields for Table2 Table2_ID Table1_ID Field1Table2
Fields for Table3 Table3_ID Table1_ID Field1Table3 Field2Table3 Field3Table3
Fields for Table4 Table2_ID Table1_ID
Can anyone offer some advice on the best way to set up some forms for data entry with these tables? I think having Table1 be the recordsource for a parent form, having Table2 be the recordsource for one subform, and having Table3 be the recordsource of another subform would work for the one to many relationship between Table1 and Table2 and the one to many relationship between Table 1 and Table3. But I can't figure out how to then include the many to many relationship between Table2 and Table3.
I could not find Menu design in Access...Is it available during design time? When I searched for it I found AddMenuItem to programatically add menu... I vaguely remember seeing menu design in VB 3.0 7 years ago...I am looking for something similiar..
This probably the simplest and stupidest question..please excuse me for that.
Hi There, Im building a database that stores invoices and keeps a running total of all invoices accumulated per customer. Right now I have it so that on the form it Dlookup's a unit price and then divides it by the running total to get the total units accumulated as well as it displays the total money accumulated. The problem is, a few times in a year some unit prices will change. If a user updates this change then it will mess up the total unit count and the units to date will be wrong due to the new unit price. I'm wondering how i could overcome this problem. I was thinking along the lines of saving the total units accumulated in another table, reseting the entire form to zero, and some appending the old unit count to the reseted value... This probably sounds very confusing, but any help to just get me started would be greatly appreciated!!! thanks!
Hello everyone. Im relatively new to access, i've only been using it for a few weeks. There are certain aspects that I cannot get my head around.
Is anyone willing to let me email them what I have done so far and the criteria for the database I am making and advise me where I am going wrong and where I need to be heading to get it right?
Thanks very much. Any help will be very much appreciated.
