Hi and thanks in advance! I'm working on a rental property database. I have property, unit, tenant, and lease tables (plus some more). But my main problem right now is this: How can I refer to a particular combination of property/unit in the lease table (or anywhere else for that matter)? I thought having separate property and unit tables would be better design. Should I just combine the two into one property/unit table? I figured that, for report/query purposes, it would be easier to have separate tables. Please help!!!
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.
This only shows the flow if the main category is "Floors", sub category "Joist", and then the choices under subcategory "Joist". There will be others for the other categories, subcategories, and choices.
Can someone help me or steer me in the right direction in setting up the database table/tables to accomplish this. Oh and BTW, there will be prices attached to each "Choice" under the subcategories.
I hope that this makes sense... any help would be much appreciated.
I am designing a db from an excel file that a volunteer org uses, i am the treasurer and I believe what they do in the spreadsheet would be easier in Access. I am familiar with tables, queries etc. Where I am getting stuck is the relationships and getting it to work properly. The database would need these tables based on the attached spreadsheet; Students, Tutors, Workshops, Events, Student Payments, Tutor Payments. NOTE: check the comments in each sheet in the spreadsheet for how the sheets work, but it is self explanatory. I think the db would need to be based on the students and from that the workshops and events that they attend, the workshops need to be linked to the tutors, where it gets tricky is that tutors can also attend events. Basically it would be a registration database, I've looked at the template from Microsoft.com called event management and it is sort of what needs to be done but not quite. Any suggestions would be greatly appreciated.
I need some basic help designing an environment where jobs can be assigned to personnel. Typically they'll have the same "job" every day, but if someone is absent then their job must be reassigned, distributed among the others.
The large assigment of job can be done automatically on a daily basis, and the reassignment could be manual. We're dealing with about 120 people, with 5 or so people absent at a time (potentially).
I'd like to track only whether or not the job has been completed. They'll have access to update their record.
I could do this fairly easy except for the fact that they'll have a daily assignment. How to I do this without generating a table without future dates? Or can I?
For some reason I just can't wrap my head around this design working with dates, assignments and completion.
I am trying to create a Preventive Maintenance Program for my facility,
I will create maintenance tasks: [ID],[Dept.],[Machine],[Task],[Frequency],[Completed Date],[Due Date]
Simple tasks,
Up front design help,
I will want the task to stay in the task table and the Due Date to be calculated combining the [completed date] & [Frequency]
Simple not a problem,
Here is my problem: I will need to create a task history table that will contain the Task details and the Completed date each time I complete the task; therefore my thought is that i will need to send the task detail to a history table each time it is complete, so I thought Append Query; but the problem is that it appends all of the records in the task table each time you run it including the tasks that have been appended on previous query runs.
any thoughts on how to capture the task details for a specific record and send them to a history table ?
When creating a database is it true that ideally i should avoid using the lookup wizard at table level and instead do that with combo boxes at form level ?
I am trying to build a db for the office I work at. And I have a few questions about how I should design it.
1. My vendor number should be an autonumber, but it has a relationship to the vendor name on a report. I cant create a relationship btwn text and autonumber.
2. Right now I have 3 main tables. Vendor, builder, and job. All of this is for the marketing aspect of the business. But I want to include other aspects, like collections and payroll. Should I add columns to the jobs table, or create a new table? For example, on the jobs table, I already have job info like details on the job and who referred it to us. Should I add invoiced amount, amount collected, etc., or create a table called collections to store all of this info? Im really confused about this.
I have an existing desktop Access database for a Membership system which is working fine (+- 1000 members). We have an existing website that is also working fine and developed using Frontpage 2003 - due for conversion to Expression Web.
Challenge:
We want to offer a facility for members to update their details online via the website. That's the easy part - my query is how do I manage to synchronise both the desktop and the website DB? Replication seems to have disappeared with Access 2003.
New members would be added locally whereas updates would be handled either locally or remotely.
I'm new to Access 2007 and only have a little knowledge from 1998? version some years ago. So please be gentle :-)
I've put together a database for entering the bookings for my taxi. (One man band) and would like some advice on adding a few bells and whistles.
1. Some of my bookings are daily/weekly/monthly and I would like to be able to add them all in one go. I am currently putting the booking into Excel and copying into the number of cells needed. I then change the dates using Fill and then import the lot into the DB.
2. On the main report page that shows all upcoming bookings I would like to differentiate between dates using different colours. Monday = Yellow Tuesday = Blue Wednesday = Yellow and so on.
3. I would like to show the DAY as well as the DATE. Example MONDAY 4th Feb 2008. The best I can get is 4th February 2008
4. I would also like to be able to choose an address from a dropdown menu but also be able to enter a new address manually if its not listed. This new address would be added to the list. I've tried using LOOKUP COLUMN but without any success.
I would include a copy of the DB but its full of customers personal data so I cant.
The DB is made up of the following Fields in a Table called Bookings. ID, Time, Date, Name, From, To, Notes, Account (Y/N), Paid (Y/N), Cost
I'm asking a lot I know. I can use the DB as is but I would like to add these items if possible.
if i had a table with products and i want to keep a record of what has been chaged or added to this product, whilst keeping the old records how could i set it up in a databse?
the excel spread sheet looks like this
product commments
1 chnged this and that 20/08 1 modifyed component 21/08
1 tblAssistFMWork in which is stored the fields JobNumber, SiteRefNumber, SurveyorNo 2 tbltable1 which has the field SiteRefNumber, Address1, Address2 etc 3 tblAssistFMSubJobNumbers which has the fields JobNumber, SubJobNumber.
JobNumber is the relationship between tables 2 and 3 and SiteRefNumber is the relationship between tables 1 and 2. There are other fields in each table but these are the relevant ones for this question.
Each JobNumber will have a minimum of one SubJobNumber but may have many. Each SubjobNumber starts at 1 for each new job so this may look like this in the table
JobNumber SubjobNumber 0001 1 0002 1 0002 2 etc Each SiteRefNumber may have one or many JobNumbers
I wish to enter the date a SubJobNumber is completed and have been struggling with pulling the record into a form using a combo box because of the need to enter the JobNumber and also the SubJobNumber. So I decided to create a new table tblAssistFMJobDateCompleted which has the fields
JobNumber, SubJobNumber, DateJobCompleted
I need to report on a jobs status ie done or not done, but when I created the query for this it would only pull the records which have a completed date on them. Could someone please give me some advice as to a way forward.
