A Query About A Book Management System Isn't Working

Mar 1, 2008

Hello !:)
I need to make a SQL query in Microsoft Access.
The database is in French but you will easily understand, if not please ask me to translate.
You will find the MDB Access file inside the attached ZIP file.


The query I need to make is ::rolleyes:

We want for the next sent of books to the members of l’EST-SUD (WEST-SOUTH in English), to test the equivalences of the books.

We want to send for each book from the “genre” (theme in English) “Animaux” the most expensive equivalent book, we want to give a list with the original book, the recommended price (PrixCons in the database) and the most expensive equivalent book with his price.

-“NoLiv” is the book number in the database.
-“TitreLiv” is the book name (title) in the database.
-“NoGen” is the theme (genre) number in the database.
-“NomGen” is the theme (genre) name in the database.
-“PrixCons” is the recommended price for the book.




This is the query I tried, but it isn’t working ::confused:

SELECT DISTINCT *
FROM LIVRE AS L, EQUIVALENCE AS E
WHERE L.NoGen IN (SELECT G.NoGen
FROM GENRE AS G
WHERE G.NomGen IN ("ANIMAUX"))
AND E.NoLivEq IN (SELECT LI.NoLiv
FROM LIVRE AS LI
WHERE LI.NoLiv IN (SELECT MAX(LIV.PrixCons) AS [Prix Max Livre Equivalent]
FROM LIVRE AS LIV
WHERE LIV.NoLiv=LI.NoLiv))
ORDER BY 2, 3;





SECTEUR (N°Sec, NomSec)
ADHERENT (NoAdh, NomAdh, PrenomAdh, Sexe, DateNaissance, #N°AdhResp, #N°Sec)
MEMBRE (NoMemb, PrenomMemb, Sexe, Age, #N°Adh)
GENRE (NoGen, NomGen, Exemples)
LIVRE (NoLiv, TitreLiv, ThemeLiv, PrixCons, #NoGenre)
ENVOI (#NoMem, #NoLiv, DateEnvoi)
ETUDE (#NoMem, #NoLiv, DateEtude, EvalEt, Commentaire)
EQUIVALENCE (#NoLiv, # NoLivEq)
Primary keys are underlined and foreign keys have a # before them

View Replies


ADVERTISEMENT

Order Management System

Jul 29, 2006

I have little experience using Microsoft Access, but am attempting to create an order management system.

I have designed a system to store customer details and allow the entry of orders. I now want to develop this so that the system can handle delivery notes.

There are currently two tables for order information, since each order can consist of multiple items. So, general information, like the order date, order number and salesperson are stored in an orders table. A second table, Order Detail lists the items and item specific information along with the order number of which they are a part.

A whole order (i.e. all items assigned to one order number) may be delivered at once, or in smaller consignments. However, every consignment must have a delivery note with a unique number. So, there may be several delivery note numbers associated with each order, relating to different shipments.

I therefore need to create a system which allows the user to select which items from an order are being dispatched and then get Access to do the following:


Create a new record in the delivery notes table. The delivery note number will therefore be created (it's an autonumber). The user should be prompted to enter the delivery date and this should be stored in the date column of the delivery notes table, alongside the delivery note number.
Enter the delivery note number in the "Delivery Note Number" field of the Order Details table, for every item that the user selected as dispatching.

Once this is in place, I will then be able to pull all the required details together with a query and then print the delivery note.

Bearing in mind that I'm very new to Access and so don't have any knowledge of scripts/macros etc, can anyone tell me how I can achieve this?

Many thanks in advance,

Gary

View 11 Replies View Related

Search Management System

Jun 7, 2007

I am trying to create a database where I can search for a specific vacation spot based on the things to do there and qualities about those things.

Lets say I have the following

Arizona --> Grand Canyon --> Hiking, Rafting, Dining, fun, costly
Arizona --> Family --> Dining, Scary, free, scary
Utah --> Zion Ntl Park --> Hiking, Photography, inexpensive, fun
Florida --> Disney --> Family Fun, Parks, Dining, fun, costly

So now I want to run a search to show me places that have "hiking" which would return Grand Canyon and Zion Park and it would return Zion under the Utah Category and Grand Canyon under Arizona.

Also, would it be possible to run a search of places that meet, say, 80%, of the results. So if i result for places that are costly fun, and hiking it would return results that matched it about 80% or so?

Thanks!

View 5 Replies View Related

Keeping Historic Data - An HR Management System

Nov 16, 2006

I am trying to design a database for an HR management system.
I am not very clear on how to handle historic employee data in this database and will need some help and advice please. Any comment you can provide, i'd really appreciate it.

We will like to have record of historic data and the date of change:
Employee address change
Employee name change
Salary change
Position/Title change
Office location change
Number of dependents change
Bonus received year-to-year

E.g. if an employee name changes, we should still have to have a record of what the old name was.
Scenario:
Jacqueline Peters got married 2years after her employment date and now goes by Jackie Harrison (the changed was made on the system), she leaves the firm 10years later. Many years later, someone calls about a Jacqueline Peters that worked here, we have no way of knowing who that is.
Now, we would like to the salary she was earning 5years after she started here though her endsalary has changed 10years later she got a big promotion (salary change) and changed department, we have no way of knowing what her salary at a particular time was and what her old title was.

What's the best way to design this database to handle historic data? Have you worked on something similar? How did you go about it?
Any ideas and comments will be greatly appreciated.

View 7 Replies View Related

Queries :: Phone Book Database - Query For Fields Containing A Value

Sep 5, 2013

I'm currently trying to make a simple phone book database that use queries to search through the records. I need to set the search parameters to pick up all records containing a value rather that just search for that specific value.

For instance if I type in "mic" and search I want all records containing mic to appear in results like mick and Michael.

Is there any way this can be done?

So far I have only tried the [please enter first name] method and this has been in successful.

View 3 Replies View Related

More Than 255 Fields Management In A Select Query Of Access 2000

Apr 26, 2007

Dear sir/madam

I have two tables linked together with one-one relationship with primary key.
The form has been created using a select query using these two tables. total fields at present is 253(table1=25 fields, Table2=228 fields Total = 253 fields)

Now my problem is I have to add 20 fields more to the above. so the total fields now is 273 fields but it is not accepting and giving the error as "Too many keys defined 3190".

how to solve these problem

Thanks in anticipation
L.Ranganathan

View 14 Replies View Related

Tables :: Primary Web Based Inventory System - Update Raw Data From Another System

Mar 13, 2014

I have our primary web based inventory system that I am exporting to Excel and using this as an import to Access for the main raw data for my database. This being inventory it changes daily so I am updating this table every day. When I try to append the table it ads all the records. I am wanting an easy way to add only the new records/take out the ones that are no longer there. Basically update the table with what is currently there.The only have I have found to do this is by running non-matching queries and update queries.

View 2 Replies View Related

Hire System - Append Query

Apr 6, 2005

Hi,

I am trying to creat a booking system for cars which has a specification of being able to be hired by the 'week'. I am thinking of creating a table with every week date (every monday) from Feb 28 2005. Just wondering how I would create a table do to this with an append query?

I was thinking something like looping from Feb 28 2005 (monday) and dateadd + 7 a few hundred times then having an HireID field in the same table and once a car is booked for a one week period its start date hireID (or CarID) are entered into the database so nothing can be double booked.. I am just not so sure how I would reuse each date for all the different cars?

Any ideas on this are much welcomed.

Thanks in advance,
Scott.

View 5 Replies View Related

Best Book On VBA

Jun 16, 2007

I want to know which is the best book on VBA out there.. for Access 2003

View 3 Replies View Related

~Please Help~ Cattery Booking System 'Free Pen' Query

Dec 3, 2005

Hi, here is the general schema:

tblCat, tblCustomer, tblPen, tblVisit, tblTreatment, tblCarePackage, tblEmployee

When a customer makes a booking, all I need to do is check that there is less than 20 cats in the cattery for every day of the customer's intended visit range. This isn't the same as a car-hire system where if a car is out for one day during the intended hire, then the booking can't be made - it doesn't matter which pen a cat is put into, as long as there is a pen free for each day of their visit.

i.e. a customer wants to hire a ford escort from 1st-10th january but it is already booked for the 9th onwards - the booking can't be made.

a customer wants to put their cat in ANY pen from the 1st - 10th so it just depends on ONE pen being free for every day of this visit.

Any suggestions? My teachers are hopeless and I need to have implemented my system by 20th January!!! :eek: Thanks, Jayce

View 5 Replies View Related

Best Book For Access 97

Oct 4, 2005

I allready looked at what was recommended in this forum - but I noticed there are only two books listed.

Are both of these books good for beginners or is the Access 97 Bible better? I have seen about a dozen of books and would like to have a few on hand to learn from and to reference as my skills increase.

I truly am after something that can give me a breakdown of the code in Access and what each line really means and the action it produces.

Thanks in advance,
John

View 5 Replies View Related

Access Book

Feb 22, 2007

Hi All,

i'm looking for a really good access book that will help me progress my access knowledge i have an okay knowledge of access and wish to expand my knowledge futher any good books out there that can help i know their is hundreds of books but does any one have first hands experience of a gr8 book they used

Thanks

View 3 Replies View Related

Book Recommendations?

Aug 1, 2007

Hi Everyone,

I was wondering if anyone can recommend a good book for learning more about Access?

I have basic experience with the programme, but am hoping to tackle the old 'questionnaire/survey' issue. A lot of books I can find on amazon seem to be set at such a level that I can already do (multiple tables, simple queries and reports), I need something a little more in depth. Others don't seem to tackle the specific issues I'm going to face (although I could be wrong about that!). Also, as I usually don't bother with computer manuals (I generally pick up and play with new programmes) I don't know which ones are best (e.g. Microsoft own, 'Dummies' etc)?

EDIT: I'm using Access 2002.

Thanks a lot!

Bek

View 8 Replies View Related

Appointment Book

Feb 8, 2008

DOes anyone have any examples that they are willing to post or links about how to build an appointment book in Access? I have tried google but I cant seem to find useful links.

Thanks.

View 12 Replies View Related

Address Book

Oct 12, 2006

I have a form "frmsend mail" whic have fields "To" and "Cc" i want to have a address book in the access itself having same functionality as outlook i.e it should add new email id to the address book, prompt the email id,etc.
How to do tht.
thnxx

View 7 Replies View Related

Address Book

Oct 12, 2006

I have a form "frmsend mail" whic have fields "To" and "Cc" i want to have a address book in the access itself having same functionality as outlook i.e it should add new email id to the address book, prompt the email id,etc.
How to do tht.
thnxx

View 1 Replies View Related

Good Access Book

Apr 3, 2006

I am looking for a good Access Book. At work, I have "Access for Dummies" and "Access2000 Step by Step". Are either of these good, bad, etc. What would you recommend. I am a begginner, but know some things, so I don't want something for real beginners.

View 2 Replies View Related

Phone Book Problem

Nov 23, 2004

Here is my setup:

In Acess 97 I have a single data base with 5 tables, Attorneys, Employers, Health Care Professionals and two others. All tables have exactly the same fields.

I am trying to create a “phone book” made up of all the records in the data base. The form for this phone book has only a few fields such as fname, lname, phone, address. It also has the autonumber field. The record source for the form is a Union Query. When I open the form it works well. I get lawyers, employers, doctors , etc. all in alphabetical order.


Here is my problem:


I have a command button labeled “View Record.” I want to be able to click on this and have it find the record in its original table and open the form for that table so the record can be modified (on exiting the record I have it requery so the phone book is updated).

I can only get this to work with one table at a time, that is, the table that is named in the event procedure code. So if I am in, say, an Attorney record and tblAttoney is named in the event procedure code, all works well. But if I am in, say, an Employer record I get a blank Attorney form opened.


How can I get it to trace the autonumber to the proper table and open the record . I assume that the autonumbers are unique ACROSS all five tables since they are in the same database.


Any help would be greatly appreciated.

View 1 Replies View Related

Good VBA Book, Any Idea?

May 9, 2007

I really need to learn how to code in VBA for access. Can someone direct me to a good VBA Book?

View 1 Replies View Related

Form For Comic Book Confusion

Apr 6, 2005

hello, this is frazzling my brain i cannot comprhend the answer :confused:
sorry werent sure were 2 post this hope this is the right place

I have an array of comics 300 they come out weekly, the first is Number: 344 this came out 26th September 1983, i have a form with a combo box were i select the comic number (344 - 700+) in this case i am selecting 344 and i press a button "command 11", i would like it to display in three text fields, date, month and year. This has well an truly killed off my brain. Any help would dearly be appreciated.
Need any further information just ask.
Paul

View 6 Replies View Related

Any Good Book That Have Examples Of Code, Function, Etc

Feb 15, 2008

Just wondering if anyone new of any books that contain examples of code, functions, syntax, etc. I would like to have a good reference that I could go to instead of searching the internet everytime I need to try something new.

Thanks in advance.

View 6 Replies View Related

[HELP] Adding Book Copies To Book_copy Table

Mar 21, 2008

Ruralguy suggested that i made a new thread - so I'm taking his advice (H)

I've got another problem, well not problem..i want to make it more user-friendly.

Database= 21238 (Look for yourself see what i mean)
(rename to .RAR if it doesn't open)

Basically, when the user adds a book, they can add it easily..
the only problem is, when ..just say i want to add a book, and I have 10 copies of this book, I have to add the book first and the go into the ADD_BOOK_COPY form and then select the book which i want to add more copies of and click add - i repeat this for the total amount of copies that i have.
So if i had 10 copies of 1 book, i add, and then go into a seperate form and then click this button 9 more extra times.
Which, can cause headahces!


So basically, i'm thinking of implementing a new way to do this,
i was thinking about..
When i got to add a book, there's a List/Combo box with 1-20 into
So i can add the book, and then select how many copies, click 'add' and this will add 20 copies of that bookm firstly by adding it to the 'book' table and then adding 20 copies to the book_copy table..


Is there anyone who would like to help with this? :' )

Cheers btw! :)

View 7 Replies View Related

Check Book Registry Table Design

May 2, 2005

Hi all, Im updating my current checkbook registry database. Just want to be sure that table look ok to everyone before starting the coding process. This will be a multi user - multi account program. I have attached the relations diagram.
Please give me some suggestions as to what fields i might need to add.

Also check to relations.

Thanks a bunch.

View 4 Replies View Related

Print A Stock Book Type Report

May 16, 2007

I have two tables.
The ITEM RECD table containing the fields -- recd_invoice_ no, invoice_date,part_no,qty.
The ITEM ISSUE table containing fields- issue_no, date, part_no,qty.
The two tables are related by the part_no field.

How do I print a stock book type report for each item individually with all the recd item details on left side and the issued item details on the right side? All the recd & issue details for all dates need to be there in the report.

Someone please help!!!!!

View 3 Replies View Related

Printing A Stock Book Type Page

May 26, 2007

I have created a report in MsAccess for a typical stock ledger page as is shown below:

ITEM MODEL: XYZ

TRANS <------- RECEIPT -----> <----- SALES ----> BAL
DATE INV No INV Dt QTY INV NO INV Dt QTY QTY
25/05/2007 0278 27/03/2007 10 ------ ------ ---- 10
25/05/2007 ------ ----- ---- 2007/0001 25/05/2007 4 6
26/05/2007 ------ ------ ---- 2007/0002 25/05/2007 6 0
26/05/2007 0291 12/05/2007 27 ------ ------ ----- 27

I wish to print the stock ledger from one transaction date to another with the last balance qty appearing as the first row with the “brought forward” message. The report would look like this:

TRANS <------- RECEIPT -----> <----- SALES ----> BAL
DATE INV No INV Dt QTY INV NO INV Dt QTY QTY
Brought forward 6
26/05/2007 ------ ------ ---- 2007/0002 25/05/2007 6 0
26/05/2007 0291 12/05/2007 27 ------ ------ ----- 27

Require expert advice & help.

View 1 Replies View Related

List Management

Nov 4, 2006

Hello - I have a list of approx. 500,000 numbers and want to:

1) Highlight any duplicates
2) Test one list against another for common numbers

Can anyone suggest how this can be done in Access?

Thanks,

Cameron

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved