Hello everyone. Hope your holidays are going well. I have been given the task of developing a DB that will be used to generate reports for scheduling services for clients. These are medically related services and are provided around the clock. I have divided the Services into two broad categories A&B for ease of discussion and I think it helps in the DB design. The reports will display client information, services, service detail, appointment frequency and times. The reports will be printed in duplicate, with one copy for technician providing service and the other for book keeping purposes. I have it in 5 tables:
1.
tblClient
Auto#-pk
Client ID
Client Last name
Client First name
Client Location
(pretty straight foward, single client, main table)
2.
tblServices
ServiceA1-5&B1-5
(Table with a complete list of all the services offered. Clients can have up to abut 6 different services at once of several different types. The services aren't really related to one another - for DB purposes...I think :D )
3.
tblServicesA
Auto#-pk
ServiceA1-5
Frequency
(The services provided here are really more of a maintenence type of service which occurs about 4times/day around the clock (known as the frequency). The technician observes the client, if service is being utilized,then the time listed on the report will be crossed out, if the service is not used the time will be circled on the report. Services in the A category are very straight foward. Each service has a frequency at which they are checked (as stated above 4 times/day for Service A), technician checks client, if service used, service is charged. Again, pretty straight foward, but wait till we get to ServiceB. The patient can have up to 3 services from this category on their report)
4.
tblFrequency
Frequency
Times
(This table is for the different frequencies at which all the
services are checked +/or performed. Each service in ServiceA category has only one frequency (4 times/day) we'll call fID. In ServiceB category, each service is associated with one (actually two, but we'll get to that later) frequency. In this category, there are about 13 different frequencies to choose from. All frequencies are associated with one set of times. For example: fID is associated with 0600, 1200, 1800 & 0000. Again, these times will be displayed on the reports used by the technicians (the frequency will also be displayed on the report). The frequencies of ServiceB are subject to change. When this happens, the technician makes appropriate changes on current report, then will update the frequency for future reports. Wasn't sure if a PK was necessary here.)
5.
tblServiceB
Auto# - PK
ServiceB1-5
Frequency
Medication
Dosage
(ServicesB3-5 are similar to ServicesA in that they are only associated with a single frequency that will not change. So on the report the service and frequency will be displayed.
ServiceB1 is not too difficult, but instead of having a timed freq/day, it only occurs on a 72hr basis. What needs to be displayed on the report will be the next due date of the service. I was hoping that the technican could just refer to order summary to select a date that the service will need to be 'observed', and enter this data as text in a field on a form. When the service is observed, it then needs to be observed again another 72 hours later, so the date will need to be changed. The problem is, that there are times when the observation of this service occurs before the 72 hrs is up, and times when it is done later than 72hrs, so I can't have the date automatically updated.
ServiceB2, a little tricky. ServiceB, like all the other services will have a single frequency which when selected will display the times the service needs to be performed on a report for technicians to refer to. It also has a secondary frequency that will also need to be displayed on the report, but only as a free text. There are not specific times that are associated with this secondary frequency, and like the date in ServiceB1, I was hoping it could be entered as text in a field on a form. ServiceB2 is also associated with a medication (in some
cases up to 3 medications). Each medication has a single dosage.
I'll end it here, hope the explanation doesn't make it seem any more complex than it really is.
Although I have been doing alot of research in MSA recently, I'm still very much a noob. Thanks for any help.
i am a basic basic user in access, however i have the chore of creating a database for our products and sales reps. i have created a table and sepated the tables out using the analyze table function. it has now given me several tables. thats all i have done.
what i want to do is this: create a form that my sales reps can use to create a quote request and print it out for the customer. i want each quote to be saved as well, per customer, per date.
the sales rep may only have 1 of the 2 items needed to preform the quote. when he types in 1 of the 2 items, i want the other 10 fields pertaining to that item to come up (the descriptions{many fields for that} and price).
i then want to be able to either print, email, or both this quote to the customer out of the dbase.
what do i need to do this? if there is someone out there that can help me with this, i am willing to pay to help set up this dbase for us.
I have a Dbase set up and ready to roll, but I haven't actually created the Dbase yet with Access. I would very much like your opinion on my table layout and relationships. You can view the design here (http://www.joyceandstevieb.com/Dbase.htm) I am hoping to track shipments in and out, plus current stocks of items using Queries and Reports etc. I have read a lot on Normalisation and realise that there a lot of fields in the "movement" table, although a number of them are "yes/no" fields to help with the queries. Any advice is most welcome. and as always, your time is much appreciated.
I'm currently using Allen Browns pop up calander in an access database. http://allenbrowne.com/ser-51.html
The calender allows the user to choose and add a date into a field.
When I run the access project i can see the pop up calander on my form, and use and interact with it to add in a date. However when someone else on the network acceses the project, and they open the form with the calander on it they get an error and are not able to use the calander. Is there something that i need to change to allow multiple users access to use the calander?
How do i create in the validation rule of the table, a way of checking that an email address has an @ sign in the middle of it. and the general format etc?
I have two database tables: -table one contains c.100 decimal lat/longs and a temperature. -table two contains lat/longs for the whole Earth (lots!) and a temperature for each coordinate.
I want to query table one against table two and return a report which:
-returns a figure which is an average of the temperature four coordinates .5 of a degree N,S,E+W for each test site. -compares the test site temp and the new averaged temp against each other
I'm told this should be quite straightforward but I can't figure out how to do this. I'd love some guidance.
Thanks!
P.S. I'm not worried about points near the poles being closer than those elsewhere at the moment (unless there is an easy way of dealing with this?)
I'm running Access 2000 through Citrix, 20 users internationally from 1 db. I'm working through Citrix network issues to split db with separate FE's for each user. I have sporadic corruption due to this. But this morning I met a new breed of corruption. I preface this with the fact that I run a bat file nightly to do clean up and compact and repair the db. It verifies the db is not in use prior to running. This morning we were presented with a hosed db. The log file said updates ran fine. In my main table the first record was garbage, including the Auto-num field. I found that my Primary key has lost it's index and references had become unchecked. Correcting these issues got it running but does anyone have any clue what might have triggered this or even where I should start looking? Please help as I am at a loss. Thanks.
I have some basic VBA and ADO recordset exposure, and would like to create a form, that:
- allows a user to search 4-5 fields (client & business name based information) - return options of records to view that match that data - allow user to make a selection, which then provides another form with full details of that client's record(about 20 fields)
Can anyone provide an example or perhaps guidance on a good tutorial, that will take me through this process gently (and help me understand the process)!
I have created a formated field called DateToCallBack, which is the calculated field. For example when I enter say today's date 03/11/07 in the Date field and enter 10 as the number of days to call back in, the calculation field works just fine and gives me the correct call back date which in this example would be 13/11/07. The problem I am having is, I am trying to set a criteria on the calculated field for a Between [Start Date] And [End Date], but when I run the query it gives me all the day dates in the range I specified properly, but it gives me that in every month, when I only wanted one specific month. For example, I select between 1 Nov 07 and 8 Nov 07 It gives me all the 1st to the 8th of every month, when really I only wanted Nov.
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.
