Reports and Returns in Forms (Newbie needs help)

[Gunnerman6875]

Hi everyone,
Well I'm a newbie to Access and your site so forgive me if my terminology is a little out. I'm currently trying to create what i thought would be a simple database for work. I'm in the British RAF and I'm currently in charge of the training cell at our Training Depot. Heres my problem, one of the tasks within my job role is to manage 3000+ training publications. These are signed out to instructors in various numbers, however i receive updates for the publications on a regular interval and at present i have to trawl through hundreds of pieces of paper to establish who has the required publication, and how many they have. At present the publications are on an excel spreadsheet, this allows me to work out how many i have in store, and how many are issued.
What i would like to do is get the database i am working on to provide me with is the following:

• The ability to add new contact (Customer) via a form - Achieved!
• A form that allows me to add / remove publications to the publications table - Achieved!
• A form that will allow me to establish who has a particular publication including there contact details
• A form that allows me to search for a publication and update information
• A form that allows me to view what a contact (Customer) has signed out
• And finally a form that will tell me how many of a particular publication i have signed out and how many i have in store.

The tables I have and the fields they contain are as follows:
Customers - Service Number (Key), Name, and contact information
Inventory - Publication Number (Key), Title, Amendment Number, Quantity Held
Publication Issues - Publication Issues, Customer Service Number, Quantity Issued (No Key)
I have a relationship set up between the tables which seems to work.

Could anyone advise me on how to resolve any of problems I'm having. I have been scouring the internet for weeks now but I'm getting lost in it all.

If anyone can help I'm more than willing to send you the database to look at if it will help.

Many thanks Gunnerman6875

[Gunnerman6875]

The attachment thing can be done immediately after posting a reply (within 5 minutes).
1. Edit post.
2. Manage Attachments.
3. Select file etc...
I'll look in again tomorrow.

Hi NeoPa,
Good news, I've now managed to resolve all of my problems apart from one. Now that I've done it it was really simple, It was just a matter of working out what i needed to query, and how to sum them!!! (I know but as i said i am a total novice!!!), However I've now spent the last 2 1/2 hours trying to resolve my final query with no success. I think i know what i need to do but cant work out how to do it. I'm going to attemp to attach the database as per you instructions so you'll be able to see exactly what i have (or haven't) got.
OK I think I need to query a query. What I am trying to do is find out a total of how many of a particular publication i have left in my store, this should be the taken from the queries: [total amount held], minus [total for each pub], (I think!)
Anyway see what you think.

Cheers

Gunnerman6875

Just tried to upload the database but the file is to large at 249kb, what now?

[Gunnerman6875]

Hi NeoPa,
Good news, I've now managed to resolve all of my problems apart from one. Now that I've done it it was really simple, It was just a matter of working out what i needed to query, and how to sum them!!! (I know but as i said i am a total novice!!!), However I've now spent the last 2 1/2 hours trying to resolve my final query with no success. I think i know what i need to do but cant work out how to do it. I'm going to attemp to attach the database as per you instructions so you'll be able to see exactly what i have (or haven't) got.
OK I think I need to query a query. What I am trying to do is find out a total of how many of a particular publication i have left in my store, this should be the taken from the queries: [total amount held], minus [total for each pub], (I think!)
Anyway see what you think.

Cheers

Gunnerman6875

Hi NeoPa,
Tried to upload the database but the file size is to large at 250kb, what now?

[NeoPa]

Try this.
1. Make a copy of your database for backup purposes.
2. Remove all but the basic data you need for testing from all tables.
3. Compact & Repair the database (Tools / Database Utilities / Compact & Repair).
4. Compress with WinZip or similar compression utility.
5. Try the attach process again.

[Gunnerman6875]

Hi NeoPa,
Firstly let me thank you for your patience, your a star!! I've stripped the database down to a minimum and that seems to have got it down to 29kb after being zipped. As mentioned the only thing i'm struggling with is trying to get a total for the amount of pubs i have in store, this should be total amount held for each Publication minus total issued of each publication. I've been trying to work it out again thismorning but still no joy. I'm heading back to camp thisafternoon so I'll check back when i get sorted, Hope this upload works.

Cheers Gunnerman

[NeoPa]

There's not really enough data here to test with and you haven't explained very precisely what you're looking for here BUT :
I've taken a stab at a query that MAY be what you're after.

SELECT [Total Amount Held].PublicationNumber, [Total Amount Held].Title, [Total Amount Held].SumOfQuantityHeld, [Total for each Pub].SumOfQuantityIssued, [SumOfQuantityHeld]-[SumOfQuantityIssued] AS Outstanding
FROM [Total Amount Held] INNER JOIN [Total for each Pub] ON ([Total Amount Held].Title = [Total for each Pub].Title) AND ([Total Amount Held].PublicationNumber = [Total for each Pub].PublicationNumber);

If this isn't right, then attach the updated database (in post #14) with something like 20 records in each table and explain what you're after.

[Gunnerman6875]

There's not really enough data here to test with and you haven't explained very precisely what you're looking for here BUT :
I've taken a stab at a query that MAY be what you're after.

SELECT [Total Amount Held].PublicationNumber, [Total Amount Held].Title, [Total Amount Held].SumOfQuantityHeld, [Total for each Pub].SumOfQuantityIssued, [SumOfQuantityHeld]-[SumOfQuantityIssued] AS Outstanding
FROM [Total Amount Held] INNER JOIN [Total for each Pub] ON ([Total Amount Held].Title = [Total for each Pub].Title) AND ([Total Amount Held].PublicationNumber = [Total for each Pub].PublicationNumber);

If this isn't right, then attach the updated database (in post #14) with something like 20 records in each table and explain what you're after.

Hi NeoPa,

Thanks for looking at the Db, Sorry I didn't have enough data to test with I've put more info into it so maybe it will help. I've had a look at the query that you wrote and to be honest I think you've got it, but as i said I am a total newbie, and still something of a "BIFF"! I understand what i need to add to the query but not sure how to put the rest in. i.e. [SumOfQuantityHe ld]-[SumOfQuantityIs sued] AS Outstanding, etc..... I am learning. I'll reattach the database and I'll try to explain in more detail what I am trying to achieve.
Basically I have a total amount of each publication Say 10. If I have 2 issued to 1 person and 2 issued to someone else. then I'll have 6 remaining in store. I want to add a action button in a form, when I click on it, it will ask me which publication I want? When I type in the Publication (Pub) Number it tells me how many of that Pub I have left in store.

Hopefully that makes a little more sense.

Again sorry for being a bit of a BIFF but your help really is appreciated.

Gunnerman6875

[NeoPa]

Is [Total remaining in store] your attempt to implement what I posted in #15?
If so, what you need to do is :
1. Copy the text from my post (just the SQL).
2. Edit the [Total remaining in store] QueryDef (A QueryDef is Access's term for a saved query.) in Design View.
3. From the View menu select SQL View.
4. Select the existing SQL (to lose it all) and Paste in the SQL copied from my post.
5. From the View menu select Design View.
6. Save if/when you are happy.

This will transfer my SQL directly into your database. This is why we always discuss Queries in their SQL (portable) form in these forums.

[NeoPa]

If/when you are happy that you have what you're after, then we can look at replacing the 'parameters' in your queries with references to controls on a form which should make the database a little more user friendly.

[Gunnerman6875]

If/when you are happy that you have what you're after, then we can look at replacing the 'parameters' in your queries with references to controls on a form which should make the database a little more user friendly.

NeoPa!!
You are one very Clever Guy!
That does exactly what I want, I've just hidden some of the fields in the query so it only shows what i want. One question! the query asks me for the Publication Number twice , is there a way to prevent that from happening?

[NeoPa]

There is more than one way.
Do you want to move on to driving it with a form or do you simply want a query that pops up the question(s) just the once?