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 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 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'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?
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.
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 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.
Im in the process of building a database for a friends business, and im a bit of a newbie with access. Id like to get some opinions on structure and overall how i should build the Database. My goal is to have two types of clients ... donors and buyers. A client can be both a donor, a buyer or both. When a client is a donor, they get a certain amount of credits added to their account. When a client is a buyer, they will be purchasing those credits from a donor. heres an example of what i want to accomplish; John smith donates 500 credits; I enter John Smiths info and credits into his profile; Jim Doe buys 100 of John Smiths credits; I want the DB to automatically update Mr. Smiths Credits, and then add 100 credits to Jim Does profile. Also, I want John Smith to be able to purchase credits from Jane Johnson, and again, have the credits added to John Smith and deducted from Jane Johnson automatically. Get my meaning here? The tables will also contain the typical client info ...ie; Name, Address, Phone, SSN etc... Can i/Should i do a seperate table just for credits and link it to the client tables? Should i create seperate tables for Buyers and Donors?
Also, I have an excel spreadsheet with formulas to do credits already, but when i tried to import it into a table in access, it didnt work so well. Any opinions on table structure, design etc would be greatly appreciated Thanks all for lookin in
Hello to all, i have a non-windows application and i would like to create a vb program to print invoices. I would like to send to this program a txt file with all the values (qty, vat, customer name etc with vertical & horizontal positions in the form etc..) and then superpose all i need to print with an image (gif or jpg wich is the my customer invoice presentation. In fact i have 2 layers , one with all the value i print and another with the invoice image background. I'm a beginer with VB, so i need advices to create this program, maybe someone did this already. Thx in advance VINCENT
Im fairly new to access and im having trouble constructing a stock control system that can create sales orders and adjust stock levels accordingly, hold customer details linked to sales orders. Ive spent about 20 hours trying to do this and its just pickled my brain, ive searched everywhere but sometimes im uncertain what exactly it is im looking for. Can anyone give me some pointers?
I have 7 tables at the mo but its 4 of the tables i need for the sales order:
tblcustomerdetails customerID,first name, last name (general customer details)
what im trying to do at the minute is contruct a subform for a form that would require entering the products into through a combo box selected by productname and then autofill the product description and listprice. Ive ended up deleting all my forms and queries because nothing seemed to work right. I will then add this sub form to a form containing all the customer information and the total price for the subform this then needs to be output to a report for printing, but i can figure that out later. Ive attached my database if anyone wants a look if you dont understand my jibberish.
I was wondering if this could be done in Access. Let me explain
I work at a candies manufacturer in Puerto Rico. Right now we are not tracking any kind of inventory. Is it possible to efficiently track our kind of inventory ( raw materials, work in process and Finished Goods) in Access?? Maybe using a bar code system??
I would like some advice or opinions from people who have worked with access and mysql.
Currently we run a large database in access which holds around 3500 records. It is actually running quite slow at the moment. What would you suggest to speed it up? ive heard running it on a sql server but i dont have the info to know if this would be correct.
Also i was thinking or changing the access database and getting it fully redone in mysql why would this be more advantageous?
Also i havent any knowledge on MySql is it easy to learn for a beginner? Do you have any information such as websites i could visit to learn or sample databases? Or would it not be worth me learning it? What would you see at the front end and back end?
My client wants me to make fields from different tables on the same form which he wants to use for input. This has made it very difficult for me as my queries have to involve a lot of outer joins and in some cases full joins.
I'm trying to set up a database, which I've done before on different programs, but I'm new to Access. I have a rather elaborate plan but am not sure it's actually possible.
I would like to set up a system that will effectively take input from the user within a record on the database. In simplest terms I'd like to set up a form on which the selection of a value for one field for a record affects the list of options available for a second field. As a basic example, say there are two fields: Input with possible values Red and Blue; Options, with possible values Red1, Red2, Blue1, Blue2. Ideally I would like to set up a form on which if Red is selected in Input, the options Blue1 and Blue2 don't appear in the Options box. Crucially you can also then select Red1 or Red2 as the value for 'Options' for that record (as opposed to just having a text box with the options written in it), as this provides the potential for a string, with the selection of a value for Options affecting another field.
Obviously in reality there will be many potential values for Options, and it won’t be obvious to the user which are compatible with each value for Input.
I wanted to use Program Flow functions with a combo box - say for the Record Source: IIf ( [Input]="red" , "red1;red2" , "blue1;blue2" ), though this would probably need to become a Switch/Case/Break command in the real database - but I don't think you can input equations into the Record Source.
I've also thought about trying to use queries, but can't see how it would work either, (the form for every record is the same, so the combo/list box for Options will always have the same properties. Switching between forms based on the value of Input seems impossible).
Then again perhaps I'm trying to make a database do something it wasn't really designed for, and should go back to basics and just display the possible options in a text box that is dependant on Input (but this way I won't be able to use the value of 'Options' in a further process).
I'd really appreciate any suggestions, especially since I'm pretty clumsy with the system still (first day using it, oh joy) and so could well be missing an obvious solution.
Hi, I'm still an amateur at using Access and have just recently been introduced to normalization.
I'm looking for some advice on how to proceed with a database I'm trying to create. I need the database to store vehicle information (name, make, model, color, license plate), along with parking information (date, time, place, who issued the notice)
My biggest question so far, is finding an efficient way to list a vehicle with what would be an undetermined number of parking slips. and then of course being able to retrieve that information on one form.
I tried using a from for VehicleInfo with a subform for ParkingInfo but I'm not getting the relationships right, the parkinginfo form is not displaying all the information connected to the license plate when the main form shows the vehicle information..
if that makes sense, any help or advice on how to proceed (or begin) would be greatly appreciated.
I have been developing a catering order system at work. A demo version has been in test and initial issues sorted. The users are very happy with the way it works and though far from perfect it does everything they asked for and then some.
Basically, each order for refreshments/food creates a record and order number. Orders feed through to a daily 'jobs' diary sorted by date/time which the catering staff work from.
However, what they are asking for now is to be able to link some records together for collation/charging purposes. Grouping using the customer ID and the order Date doesn't work as customers could have many orders across many dates , and some of the orders by the same customer won't need to be collated together. My initial thoughts are to add a unique code to each order that needs to be linked , has anyone any ideas on this , is there an easy way to generate a code (perhaps CustID, OrderID , Date, other?) which can be added to other records to 'link' them.
I would be grateful for any suggestions.(other than a complete redesign :eek:)
I was hoping someone could offer some advice on how I would design the following project:
Student Table - ID - Name - Unit (each student belongs to one specific unit) - License type (each student could have multiple license types)
Unit Table - Unit Name (string)
License Table - License Type (string)
I have created a report that dynamically updates information according to what unit the student belongs to via a drop down box, i.e. while the report is open, select a unit from a drop down, press a button to apply the filter and the report automatically updates. I want to add the same kind of functionallity to the report based off of licenses as well. My original design had all license types in the Student Table as a yes/no option. I couldn't get the filter to work properly so I moved license types to its own table (which makes more sense anyways...) But, unlike the Units Table, any One student is allowed to have many licenses so this creates a bit of a problem. If anybody has some insight on this I would much appreciate it. If you're not following, please let me know and I will try to be more specific. Thanks.
Hi. I just recently started studying Access independently since my school never taught it to me and I'm trying to design a invoice type of database as a summer project. I'm stumped on queries because the office 2000 guide I have only briefly goes over it.Basically, what i'm trying to do is create an automated value like in excel so that the "Net" column i have will subtract with the "sales" column to automatically enter a value for the "profit" column. I can't find any place for me to enter anything like [profit] = [net] - [sale]. i tried to use the input mask but since my data is in currency, it won't allow me to do it. can anyone please tell me where to start or what i've been doing wrong? thanks.btw, i'm also trying to do the same thing with the y/n feature of access. i'm also trying to find a way so that if i type y/n for a column, it will copy the value from a different colum so say i put yes on "account R" then i want the "AR$" column to copy the value from the "sale" column automatically. if i can solve this problem the same way as the previous problem then please ignore this (i THINK this can all be solved with queries.)
I have an assigment and have to create a database, i'm just starting to learn how to use access properly.
there's a screen of a form I made, if anyone has some advice to make look better it would be welcomed. As you can see it is very basic.
I also would like to know if it possible to create a search bar, for example typing in "sales" and the list of all the candidates working in sales comes up (I know how to do this in a query, but how do you transpose it into a form).
For now I have 20 comboboxes on my form each bound to a field from my sourcetable. Since ya can only choose 1 value in a combobox, the users want to to choose multiple values in each box. How should I implement this? I can't use 20 listboxes because I haven't got any free space left on my form.
I am creating an incident database for students at a high school. At the moment I have one table for the students with a studentID (Autonumber) that links to an incident table in a one to many relationship.
My question is as I have many different types of incidents taking place, e.g. student on report, phone call, Referal from teacher, medical incident, exclusion etc... would it be better to have a table for each type of incident or keep it as at present.
Hi there, Being new to Access and table relationships I need advice on the table design I have so far. A jpeg image of the table relationships can be viewed at www.joyceandstevieb.com/dbasemap.htm Do I need to include foriegn keys to counties and countries in the address tables? Or will the connection from city to county and then country suffice? Also, could I trim down the address and contact tables to just one of each? I don't know how I could differentiate which is customer supplier and haulier though. Any help would be appreciated.
