MS ACCESS :: Relationships

Seeking Advice On Many-to-Many Relationships
Hello, I am working on a database of translators. Each of these translators can have multiple Source Languages, and multiple Target Languages. I understand that I must use a many-to-many relationship, but as the Language fields for both Source and Target Languages comes from one table (tblLanguages), I am having difficulty conceptualizing how I can get the table relationship to produce the desired effect; i.e. having a datasubsheet for each translator with fields for each their Source and Target languages.

My current table structure:

tblTrans
TransID
(name and contact data)

tblLINK_Language_Trans
TransID
LanguageID

tblLanguages
LanguageID

I thought that maybe if I create a separate LINK table for each Source and Target Language, I might get the desired effect, but this was futile, as only one datasubsheet was shown.

Any advice for a humble Access beginner would be GREATLY appreciated!!!

Thanks!

Blair

View Replies !

Payroll Database- Advice Needed On Relationships/normalisation
Hi,
I would like to get your advice on my table setup and relationships for this payroll project. The company is an engineering company with Projects (or construction sites) around the world.

The 'Candidates' are current or potential employees and contractors. There are three main pay categories:

1.Shift-workers
All shift workers doing a particular job on a particular project are paid the same rates e.g. all welders on a particular project or site in England are paid the same as each other. For that reason I want to link the pay rates with the job description for these workers. This avoides creating 50 records for 50 welders on the site in England to say that they make £10 an hour normal time (or whatever it is) etc.

2. Contract
Contract workers usually get paid a flat rate per hour. As these are negociated on an individual basis I would need to have this information linked to each individuals job (M_CandidateJobDetails).

3. Salary
Again this information needs to be input for each individuals job.

For the contract and salary people the pay frequency can vary (weekly, bi-weekly or monthly). So can the currency they are paid in. I haven't got as far as the currency issue yet.

The reason for the one-to-many relationship between M_JobClassifaction and M_CandidateJobDetails is that many candidates can have the same type of job e.g. there can be many employees that in the job classifaction of 'Electrician'. For many of the jobs at managerial level e.g. 'site manager' there will only be one.

I will have a table with the hours worked by each person per week. I can use this for those on shift work or contract to calculate what they will be paid.

One of the main reasons for this database is so that the company can print reports to see what is paid out in payroll for each site and in total (in euros). These will be gross figures and I don't need to take expenses, vacations, bonuses or taxes into account. They other thing we will need to be able to do is assign candidates to vacant positions and change them from one position to another - possibly between different projects.

So basically does anyone have any comments on the relationships, normalisation or anything else. Is this the best way to do it?

I've attached a screenshot of the relationships.

View Replies !

Need Some Advice
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

View Replies !

I Need Advice
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

View Replies !

Need Help, Advice, Anything!
Hi all,

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)

tblorder
orderID, customerID, delilvery address fields..., subtotal, total, delivery

tblorderdetails
orderID, productID, productname, description, listprice, quantity, discount, linetotal

tblproducts
productID, catagoryname, productname, description, costprice, listprice, profitmargin, suppliername, instock, reorderlevel, quantityperunit.

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.

thanks

View Replies !

Please Advice
Hi all
First post.

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??

Is it possible??

Please advice

Thank you

View Replies !

Advice
Hi

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?

Sorry to bombard you with questions

I value your opinions

Thanks

View Replies !

Is It Possible ? Need Advice
Hi,
I would like in Access to create a graphic program to enter hollydays of each employees.
IE: A matrix form with days in the columns and employees in the line, each hollyday period will be a colored block (len=nb of days) that can move (if start/end period changed) on the line and that can be stretched (if period is longer for exemple), and more options double clicking ecah block. I don't know what is the best way to do this.
Do you think it's possible, if yes HOW ?
Thanks a lot for your advices.
VINCENT

View Replies !

Need Advice
Hi,

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.

Any suggestion please?

View Replies !

Some Advice, Please
Hi all,

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.

Thanks for your time.

Kate

View Replies !

Looking For Advice...
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.

Thanks

View Replies !

Advice
Sorry - duplicated post.

View Replies !

Advice
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:)

Thanks
RussG

View Replies !

Could Use A Bit Of Advice...
Greetings,

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.

j

View Replies !

In Need Of Advice
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.)

View Replies !

I Need Advice
Hi everybody !

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).

thanks for your help

Ilan

View Replies !

I Need Help And Advice Plz
is there any1 who can give me tips on an exam i have 2moz on databases. its a theory and i'm crap at them. thanx loads, luff me xx

View Replies !

Need Some Advice
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.

Can somebody giove me some good advice on this?

thanx in advance,

Johnny

View Replies !

Some Advice
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.

View Replies !

Need Advice On What I Have So Far
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.

View Replies !

Access Advice
I was hoping someone could offer me some advice.

I have created an MS Access 2000 solution for a company that utilises replication and remote synchronization. The company have about 12 people working out in the field (on laptops) who use replicas of the database (held on the company's server). The solution has become quite unstable and the amount of database conflicts is growing daily.

Could anyone suggest a more robust solution for the senario described? Would MySQL or MSDE be a more stable option? Is there anything I can do to make the MS Access solution work?!!

Any advice or suggestions would be appreciated.

Kind regards.

View Replies !

Suggestions / Advice
I am building a database and need a little push in the right direction.

This database will be used to build estimates for our customers.

Here are the steps.

1. Sales Person turns in a request for a new estimate to one person who will log the request and setup the basic info for the request, Customer, Type of request, Salesperson ect.

2. The logging person assigns the request to one or more Estimators depending on the type of request. And sends the request to the proper estimators.

3. (Here is where my question sits) the estimator will then "login" to the database. Select the request they need to work on and proceed. (I am thinking it would be "nice" to have a "popup" show want requests the Logged in estimator has currently assigned, and let them click to open the one they want to work on.)

So to round this out when the estimator is finished the logging person is notified who then send a notice to the salesperson, which can then access the estimate and build a quote to present to the customer. (The same thing would apply here Selecting from just there own requests.)

Thank you in advance for any comments.

View Replies !

New To Databases: I Need Some Advice
I'm creating a database that keeps track of printing jobs at a printing company... I started my project by drawing out how I want the databases to be configured.

I was going through a book that was made for access 2000, but I need to create this in access 97 because that's what the company has on their computers. One of the features in Access 2000 thats not in 97 is subdatasheets...

Basically, what I want to do is for each printing job, there can be a bunch of different tasks that need to be completed and billed for. For example, on one printing job, they need to design a logo, and then they need to print it out and send samples across the globe, and then they need to create a pdf, etc. This is going to be different for each job.

What I figured I would do is create a separate table to take care of all of the different tasks that are related to each job. This table would have the primary key of the job from the main table for each individual job, and then they would be related in a one (MAIN entry) to many (tasks) relationship.

Is this correct in how I want to do that? How will I do this inside a form, I want them to enter the information in table that expands as they put more tasks in?

This might be a very simple question, I just want to know if I'm going in the right direction.

Thanks much!

View Replies !

Developing A BIG DB And Need Advice
Hi guys, I am developing a database for work and need some advice as the best way to go, i know how i want to do it but want a database that is going to be fast and reliable.
the database will be used by around 120 people ( more or less at the same time, i guess the number of simultaneous users could drop to around 80 or so )

there will be around 2000~3000 records being added on a weekly basis

must be as quick as possible ( the users are very impatient )

the DB must be reliable

will use an access front and back end ( would like to use mysql as backend but we can't )

one think i am going to implement is archiving the completed data after 4 weeks so i hope this will help keep the database running quick.

is there any good articles I should read up on, and all advice welcome

Best regards

Stuart

View Replies !

Asking Advice Regarding Upsize
Hi.
Your help is very appreciated.
I want to upsize large MS ACCESS(2002) app to MS SQL SERVER 2000.
[ By "large", I mean 250 queries, 78 tables, 110 forms].
The app will be used by 25 concurrent users.(therefore the need to upsize).

I have time constraints of 2 weeks to deliver.

My questions:

1. In order to finish it asap, upsizing ONLY the tables - might be a good solution. However, will it work with a workload of 25 concurrent users?(read/write).

2. If upsizing all the tables, would it be possible to upsize SOME of the queries and leave the rest untouched?
If yes, what is the process to do it?
That will save me lots of QA time (there are 250 queries).
Mind you it's not simple, since the forms need to reference Stored Procs as well as ACCESS' SQL queries.

3. In the upsizing documentation, it says that there might be situation that the query will be upsized , no errors will appear in the log BUT it won't work anymore :mad: .
Do you have any methodology for QA the upszied queries in order to ensure the system's robustness?

Thanks a lot
Roy

View Replies !

Advice About Using Dlookup Or Not
I have several tables of test scores(Test1, Test2, etc.). I want the report to contain these scores as well as their percentile ranks and descriptions. I have created a table of all the possible scores and their corresponding percentiles and descriptions(NormsTable). I have been having problems (inconsistently) with the report and I am wondering if I should change the query that it is based on to improve the report's performance.
Right now I have a query for each test with the following fields:
Test1Query:
T1Score1 (from Test1 table)
T1S1Percentile: Dlookup([Percentile],NormsTable,Test1!T1Score1=NormsTable!StandardScor e)
T1S1Description: Dlookup([Description],NormsTable,Test1!T1Score1=NormsTable!StandardScor e)

And so on for all the scores in Test1. Then there is another query of this sort for Test2 and so on. These queries form the control source for my subreports on my ScoreReport.

If I used query joins instead of Dlookup I would have to draw relationships from each score in the Test tables to the the StandardScore field in the NormsTable, which would mean making a new query for each field, essentially. Is this still preferable to Dlookup or is there another alternative I haven't thought of?
Thanks for any advice!

View Replies !

Needing Advice
Hi Im currently building an Access aplication and Im looking for some advice.

What Id like to do is use a list box which has a list of items in it, once an item is selected and a button clicked for this information to be sent into a text box to then be stored by the database. Each new item should be added to the end of the list.

I was wondering if its actually possible and how you would go about doing it.

Thanks for your help.

View Replies !

Picture Advice/help
Hi guys.

I have been told to make my database look 'sexier' but I am a bit worried that the pictures may slow it down or increase it's size unneccesarily.

Speed is an issue because we have some offices that dial in and access it that way and as such are limited to their connection speed.

So what I want to do is created a few images, link to them, and put in code that can 'turn them on'.

The question is how?

I have created a new image object. Changed it's properties to linked.

Now I need some code that reads:

If myvalue = "Yes" then
myImage.visible = true
Else
'code to delete image
End If

I am a bit worried because that means that the images will always be there on load up, so loading the form will be just as slow because the images are there.

Or maybe I can do

If myvalue = "Yes" then
'code to create image
End If

Please help.

Edit - Part two to this question is: How do I use relative links for images? I have a folder of images and I want them to work no matter where the database sits.

Edit again - Just another question: Is it possible to have Images appear and disappear depending where the mouse is?

View Replies !

Advice On Upsizing To SQL
Would anyone be willing to have a discussion about upsizing a large split database to SQL? I've managed the split using the wizard and it went very well. The problem is the performance, the form is really slow on opening and as it scrolls through records.

All my queries are saved. I would imagine it's the way I've built the form and base queries that's causing the problem and wondered if anyone would be willing to have a look at it for me and see if they can make any suggestions.

I'm struggling blind with this one.

Thanks.

View Replies !

Bit Of Advice Needed Please
Hi all,

This is my first post here and was just hoping for a bit of advice, what it is my dad wants me to create a small database for him and was hoping for some advice on how to tackle it. What he wants is listed below. I have some ideas but the most confusing aspect I found was how to update a persons age from the computers date.Heres what he wants and hope you can give me some advice. Thanks in advance John

Fields (free text unless otherwise stated) for people;

First name
Surname
Age
Further Education level - drop down menu for "GCSE/A level ,HNC/HND, Degree, MSc, PhD"
Degree held? - drop down menu "yes" or "no"
Chartered engineer - drop down "yes" or "no"
Professional Quals - free field for these
Skill base 1 - drop down for " Manager - Operations, Production, Maintenance, Engineering; Engineer - Mechanical, Instrument, E&I, Electrical, Rotating Equip, Process, Asset Integrity, Inspection, QA/QC; Superintendant/Supervisor - as above; and any others (think of all KPO Ops parented people)
Skill base 2 - as above
Skill base 3 - as above
Professional ladder job title
Grade - drop down menu BG3,BG4. BG5
Hay points
Date of birth
Age - can this be generated from entry above and current date?

Current Assignment job title
Current Assignment start date
Current Assignment Asset
Current Assignmnet end date
Current Assignment location (eg Cairo, Idku etc)
Currnet Assignment type - drop down menu rotation 14/14, rotation 28/28, expat

Next assignment options
Comments

Then set of data fields for posts

Post job title
Post type - drop down rotation menu as above
Likely Start date
Location
Asset
Sponsor in Asset

People Reports

People - All data
People - Listed by current job end date
People - Listed by skills (if Mechanical in any of three fields above, list him), will be repeats of names in list (as have more than 1 skill but thats OK)
People - List of those with degrees
People - List of those chartered
People - Listed by current job end date
People - Listed by current job end date
People - Listed by current job end date

Job Reports

View Replies !

Advice On Sycronistation
Please can anyone give me some advice on sycronisation. The scenario is this. I have created a database to hold records of patients. The database will be used by 2 people in different locations on seperate machines. Periodically I would like to sycronise the records on both machines so that all the reords are the same on each database. The machines are not networked or connected in any way. I had thought that a copy of one database can be burnt to a disc and imported to the other database by using import and importing everything! Would this work or will it cause problems. I have not split the database or created a master copy as yet. Eventually there will be thousands of records held and I hope to link the 2 machines in some way, over the web some how. Your advice would be much appreciated before I mess up completley. :(

View Replies !

Access FE To SQL Advice
Hi

I am creating and using an access front end with a SQL server back end (which I manage thru access project), and I am wondering if I am losing my way.

I have some heavy tables which hold upwards of 60,000+ records. Before when I held it all on access, the database would swell up to 200 mb+ and used to grind to a near halt, now with the tables on SQL, it is much quicker, and no corruption problems so far. Also using SQL, I am forced to a better table structure.

I have linked the SQL tables thru to the access front end no problem and have queries running of the them, I find I can do more with access queries as they seem easier to construct and fault diagnose. This is especially true when using the ‘choose’ function. However, I am not sure if this is correct or intended way.

Therefore, in a good access front end, SQL server back end set up, should I

1) Where possible, put all updates, append, and delete queries as project stored procedures and pass them thru to the access FE

2)rather than link the tables from SQL to the access FE, and then make access FE queries to feed the forms, as I currently do, make the query views in project, and link them to access FE, and then make queries off these to the forms.

3) i note comments about using a WHERE function, should this be in SQL if possible

Ultimately, my thinking is that if I can make the project / SQL do as much as possible, it will be easier (for somebody else with better knowledge) to migrate the front end to another platform such as ‘asp’, if required.

Pardon my lay mans language, I need a course or a good book

View Replies !

More Laptop Advice!!!
I need an ultra portable laptop by want to get as high a spec as possible:

currently looking at sony vaio tx range (http://www.bluefi.co.uk/news/sony_vaio_tx_carbon_fiber_notebook.html) and the ibm thinkpad X90 (http://www.trustedreviews.com/article.aspx?art=511)

any ibm or vaio owners, let me know which you recommend

cheers

View Replies !

Helpscribble Advice
Hi, ive been trying to develop a more advanced help file with helpscribble.

I have created the help files, compiled the files etc, my question is how do you get it to link to your database? I was just under the impression you set your project to call it from the vbaproject propeties, set which contextID you wanted it to generally link too and away you go!!!!

If i press F1 on my forms it just links to the default access help. Can anyone help because this looks like a really good add on to my database.

Thanks

View Replies !

Advice Needed
I built a DB for work (Access2000) which is on the network and is for inputting maintenance request. Nothing complicated about it and at any time there would probably only be 3-4 users logged on at a time, and then probably only for a few minutes each. This DB got corrupted yesterday and I had to recover from teh backup. I compact and repair manually on a weekly basis(usually). My question is would it be less likely to get corrupted if I split the DB? Would it help to set up an automated complact & repair to run overnight?
Thanks
RussG

View Replies !

Help - Advice Needed
Hi

Im currently building a database which holds customer delivery options, quantities, unit and delivery price and so on.

It has all been going fine until now...!

I am trying to setup the billing aspect. My main headache is that each customer could have a delivery every day, of different quantities. Now there will be times when this customer goes on holiday and of course doesnt need to have anything delivered - my only problem is getting the billing to adjust accordingly.

I have my SubForm which contains:

UnitName
UnitPrice
Mon
Tues
Wed
Thursday
Fri
Sat
Sun
DeliveryCost
TotalCost

at the moment in the main form the holiday period is put in and i convert this then into a number of days value - this is then subtracted from the total of units (count of Mon to Sun) x UnitPrice + Delivery.

My problem is that if a customer has 2 days off which are Mon and Tues, how do i get it to only remove the mon and tues values from the unit amount - instead of just removing a figure assuming that they have only 1 per day - wihch in most cases they don't.

Can i somehow assign the day values to each day field in the subform and get the correct days from the holiday or something!

i dont know its just breaking me down!!!:mad:

Any help greatly appreciated

View Replies !

Advice On Performance
Hi all,

I'm hoping someone can offer me some advice on performance for a FE/BE database that will eventually be accessed by up to 60 people during the same day (usually only 3-4 people searching/writing at same time).

The system is to process complaints at a call centre - built from the ground up and my first true Acess DB application. I have noticed extremely slow times in loading forms - which is a pain considering we are a performance oriented workplace.

FE is on the desktop. BE on the server. Currently it is taking up to 10 seconds to open a form (even when the form contains virtually no information looked up from tables).

Could anyone offer any tips on how to improve performance.

Thanks

Robert :)

View Replies !

I'mmm Puzzeled: Could Use Some Advice ...Thanks
I have built a tested database using access 2003 and have designed a ASP, VBScript dynamic website as the frontend. I have been impressed thus far, until I got to the magic number of storing 60,000 records using one table inside one database file. After I compacted the database, now I'm puzzeled becuase I'm getting slow performance when I'm searching all the 60,000 records at one time.

How could I perhaps design either the database structure or the asp code so that when searhing thru all 60,000 records will be less than 5 seconds to view my results via the website?

Right now I am viewing all 60,000 records at an estimated time of 25 seconds; which seem to bit slow to me, is this good or not so good?

I thought access could allow descent speeds of searching much more than just 60,000 records.

BASICALLY! I Need to store Lots of records and perform fast website search results.

I wonder who's out there in the world using access and searching more than 60,000 records at one time and is getting their search results in less than 5 seconds...please response I would like to know what you think.

Thanks

View Replies !

Need Advice: Explorer
By searching the forum I found a link to the attached sample database where you can explore the drive of the pc from access.

I was wanting to use this (or some form of this) for a customer form I have for our archived files where PDFs are stored.

What I was wanting is a text box on my form that you could put the address of the directory ie-> \serverarchivesactiveACustomer A

The listbox on the left would list the contents of that folder, which is years.

I am stumped though as how to make the module code default to a value of a text box.

Any help would be appreciated.

View Replies !

Advice Needed
I have a database that has one aspect that is highly dependent on certain payroll information. For example:

When a pilot begins entering his flight for the day at the bottom of the entry form it tells him the payroll dates are from x to x and you have worked X number of days in this pay period.

This part I have gotten down quite nicely using a payperiod table. Problem is that I must enter that information manually and it is time consuming to do that for the whole year. Point of fact I only put the first day of the pay period in this table. Our pay periods are every two weeks.

I also generate a report for payroll based on these pay periods.

My question/advice is a new way of going about this pay period deal without having to enter it in manually every year.

We have these wonderful computers but I feel I am doing too much work but just don't see a different method around it.

A bit wordy but I wanted to be clear. Any advice in a new direction?

View Replies !

Advice On Setup
I have a database that contains information on football games. There are many
different games from many different seasons, teams, opponents.

I will eventually have a fairly large number of different reports and forms
that will want to be viewed but filtered by the above three catagories.
Usually all of the reports at any given period of time while running the
reports will want to be viewed off the same criteria as the one before. (i
don't think you would want to have to fill out the parameter manually for
each report or you will be entering redundent data as the parameter report
after report.)

I was thinking of having a form with 3 multi select list boxes, [seasonlst]
, [teamlst] , [opponentlst] and having each form filter information based of
the values entered in each box.

Is this the best way to do it or is there a better way?

View Replies !

Advice On A Project Please
Hi,

Have been asked to "update" an archaic database for a consultancy firm. The existing database is approx 20+ years old and appears to use .DBF files for storing the records.

Now, the DB itself I can do, but I've normally only written DB's for one or two users. So before even considering doing this work, I would be greatful for any advice on the following.

They use a central server linked to 8 user pc's. I assume I will therefore need a FE/BE database. Would it be useful to determine what version of MS Server Client they're using?

Using Standard Access 2000 or above, (i've still to confirm what version they require it written in) can a FE/BE database be created, that will be useable (speed / reliability etc)?, I'm told that they will have ~4 users using the system at any one time. Do I need to consider using SQL Server (and if so what level / version)?

From initial emails, the 4 main .dbf files are 50Mb each. I seem to recall that without using SQL Server, once a standard Access Database starts to exceed 200Mb it starts to cause operational issues. Is this correct?

Lastly, if anyone can point me to a good book / website that discusses the standard coding changes that need to be made when establishing connections etc in VBA for multiple user databases I would be extremely grateful.

Thanks in Advance

View Replies !

Seeking Advice
I read about your different topics/discussions but still have issues.
The group I work for needs to track Customs entries, compile them for payment and make reports. They used to track the entries in Excel but they wish to do this in a Form = INVOICE TRACKING, be able to query and make reports. My Access knowledge is limited but I have created two tables and the above mentioned Form

The main table= Invoice Tracking (as per the group request) as 37 fields. Starting at the 8th field, data is updated by the users on a daily basis.
The source table = XLS 1127 as 7 fields and is refreshed and updated 2-3 times weekly by a live report on the Internet.
Up to now, when I need to update the main table, I run an APPEND QUERY. Once this is done, I run a FIND DUPLICATE QUERY and I delete the duplicates manually because some of the first 7 fields will get updated this time or some other time and therefore it will create duplicates Entry Nbr in the Main table. Meanwhile the other 30 fields are populated by users with the information supplied by the invoices.
Is there a macro or an expression that could be created to avoid the Query from selecting the duplicates randomly? I have a very basic knowledge of macros and VB but would it be possible to write something that would be like In record #1, IF the field 2 or 3 is empty than look at field 9 and others and if Not Null then get the data in the duplicate record #2, merge the
information from the two records??
When the Main table will grow bigger, it will be too much time consuming to delete XXX duplicates and can cause errors.

Thanks

View Replies !

Security Advice
Some advice would be appreciated. There seems to be a minefiled of papers, some conflicting, on Access security. I have a standalone access database which I need to protect. I do not want users getting access to anything other than whats on the switchboard. I have disabled the shift key bypass and all menus that can be disabled in the start up options. It seems secure to me now. Can it still be bypassed? Should I be setting passwords etc?

View Replies !

Advice Appreciated
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.

View Replies !

Need Development Advice
Hello,

I am looking for some advice on how to go about solving a problem.

I created a database 1 primary table with 1 other table, linked in a form with a 1 to many relationship.

The PK is auto assigned for both tables.

I want to have say three different companies use the same database but with some kind of unique ID for each company so when I bring all of the data together into one database it will all merge but be sortable by the company that created the data.

As some of you will note I thought that defining the PK for each record was the way to go but it seems that this is not going to work.

Any suggestions?

Thanks!

Fen How

View Replies !

Advice Needed
Hi all,:)

I'm looking for some advice: the problem is that I've got 13 sections in which people are working, and administrative assistants that are taking care of 1 or more of these same sections.

In addition to the usual security, there is also a requirement to make the AA's only output their own sections reports.

The way I see it there are two ways to ensure this:

1) put in a field in the users table that would indicate which sections the AA was responsible for ie

userID 1
respFor 56,34,45

userID 2
respFor 41,3

userID 3
respFor 42

... etc respFor would have to be a text field with a delimiter between each sectionID

sectID 41
sectName AAA

sectID 42
sectName BBB

2) have a separate table that will form a "union" between user and section tables ie User&Section

userID 1
sectID 56

userID 1
sectID 34

userID 1
sectID 45

userID 2
sectID 41

userID 2
sectID 3

userID 3
sectID 42

Now for my question: Can you advise me on which way is best for this circumstance? or perhaps you've got another way to go? Have you got an example ?

Your advice please...

TIA

View Replies !

Advice On Tables...
Hi there.

I just wanted to ask peoples opinion on something.

In the database I'm working on I have to create two new tables that will be joined to the Main table. each of the new tables has only one field (as well as the FK) with a one to many relationship with the Main table as there will up to 4 records per main record. Note, the fields will only be set at 15 text characters.

I'm trying to decide if it's better to only make one table and have the two fields together even though one might have 3 records, another might have 2 records and so on (of course with the same FK), making some blank space in the opposite.

Normally I wouldn't do this, however, it's a table with only two fields.

Or would it be better just to keep the two fields within seperate tables?

Also, I'll be developing a form with the two fields together, linked to the main form (This is also because there isn't enough room within the Main form).
Each will be in datasheet view.

Thanks in advance for any advice.

View Replies !

Plz Give An Advice
Hi,

A Database like this,

SCRIP_CODE SCRIP_NAME CLIENT_CODE QTY B_S
---------- ---------- ----------- ---------- ----------
500100 TISCO 47198 1000 BUY
500100 TISCO 47198 1000 SELL
500101 RELCAPITAL 47198 1000 BUY
500101 RELCAPITAL 47198 1000 SELL
500102 RELIANCE 47198 1000 BUY
500103 SATYAM 47198 2000 BUY
500101 RELCAPITAL 47198 1000 BUY
500101 RELCAPITAL 47198 1000 BUY

SELECT [scrip_code], [scrip_name], [client_code], max(IIf([b_s]='BUY',[qty],0))-max(IIf([b_s]='SELL',[qty],0)) AS net
FROM daily_trans
GROUP BY [scrip_code], [scrip_name], [client_code]
HAVING max(IIf(b_s='BUY',qty,0)) - max(IIf(b_s='SELL',qty,0)) <> 0;

Giving output as,

SCRIP_CODE SCRIP_NAME CLIENT_CODE NET
---------- ---------- ----------- ----------
500102 RELIANCE 47198 1000
500103 SATYAM 47198 2000

But i want the o/p as,

SCRIP_CODE SCRIP_NAME CLIENT_CODE NET
---------- ---------- ----------- ----------
500102 RELIANCE 47198 1000
500103 SATYAM 47198 2000
500101 RELCAPITAL 47198 2000

How can i solve this problem?

Thanks Rathish

View Replies !

Relationships - Advice?