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

[NeoPa]

Gunnerman,
That's a pretty good stab at a first post.
Could I just ask that you repost your table details in this format :
Posting Table/Dataset MetaData

Table Name=tblStudent
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
MaxMark; Numeric
MinMark; Numeric

It is often very important to know the data types of fields for working within a database.
BTW PK - Primary Key
FK - Foreign Key (a link into another table).
Lastly, when (which hours of the day) are you generally available to work on this?

[AricC]

Just a question. Are you part of the Air Wing? I used to do a job that sounds exactly like this (with a C-130 unit). The US Navy actually has a program that does all of this. I don't want to deter you from building your own DB though.

[Gunnerman6875]

Gunnerman,
That's a pretty good stab at a first post.
Could I just ask that you repost your table details in this format :
Posting Table/Dataset MetaData

Table Name=tblStudent
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
MaxMark; Numeric
MinMark; Numeric

It is often very important to know the data types of fields for working within a database.
BTW PK - Primary Key
FK - Foreign Key (a link into another table).
Lastly, when (which hours of the day) are you generally available to work on this?

Hi AricC,
Thanks for your reply, Firstly in answer to your question about what I do, I'm a member of the RAF Regt, we are basically the infantry arm of the British RAF. We deal mainly with the off base defence of airfields, so my publications are on tactics, doctrine and weapon systems.
With regards the database, since posting the thread yesterday I have managed (not quite sure how!!) to resolve a lot of the problems, I wrote a query that had a criteria written into it. By using the same query and re-writing / repositioning the criteria each time it has allowed me to get what i need.
I now have the problem of being able to find out how many of a particular publication are issued. I've written another query that will find all of a particular publication by putting in a criteria, and it brings back all the issues of that pub, but i cant work out how to total them all up!
I'm not sure how to get the table information to you in the format you have requested but here goes with a first attempt:

TableName=Publication Issues
Publication Issues; Text
CustomerSerNo; Text
QuantityIssed; Number

As you see there is no PK on this table, is this correct?
I hope this helps.

You asked me when I was available to'work on this, Im around all day at present as I'm on leave, back to work on Sunday, as of then I'm about in the evenings Mon-Fri from 6 to late if required.

[NeoPa]

TableName=Publication Issues
Publication Issues; Text
CustomerSerNo; Text
QuantityIssed; Number

Hi,
This is fine.
This tells me that you have only the one table available in the database and that you have a text field called [Publication Issues] within the table called [Publication Issues]. Is that all correct?
Next, you speak of queries that you've developed already that give you the data that you want but not in exdactly the right format or not returning all the data you need. Can you post the SQL of these please. That will help to convey what's going on.
If you can attach your database to a post in here that would be even better.
Assuming you're living and working in Britain (given your job that's reasonable) then I'm in the same time-zone (South London). This gives us a better chance of working together.

[Gunnerman6875]

TableName=Publication Issues
Publication Issues; Text
CustomerSerNo; Text
QuantityIssed; Number

Hi,
This is fine.
This tells me that you have only the one table available in the database and that you have a text field called [Publication Issues] within the table called [Publication Issues]. Is that all correct?
Next, you speak of queries that you've developed already that give you the data that you want but not in exdactly the right format or not returning all the data you need. Can you post the SQL of these please. That will help to convey what's going on.
If you can attach your database to a post in here that would be even better.
Assuming you're living and working in Britain (given your job that's reasonable) then I'm in the same time-zone (South London). This gives us a better chance of working together.

Hi NeoPa,
Thanks for the reply,
I have no problems attaching the database to a post but I'm unsure how to do this so a little nudge in the right direction would be great. As mentioned the database is now pretty much getting there, however I cant work out how to get a query to calculate the total amount of a particular publication I have out. I would Idealy like it to tell me how many I have left in my store.
As I originally said I'm a total newbie to Access and this is my first database, so posting it would be good as it would be great to let an expert look it over and provide me with some feedback and ways i might be able to improve it, either in the way it works or in the appearence.
I'll look forward to hearing back from you.
PS. I'm currently in Gloucester but posted in Suffolk,so the time-zone is good for me.

[NeoPa]

I will try to find out about the posting of attachments.
The easiest way is to upload it to some sort of private webspace type site then post a link in here. I'm afraid I don't use that myself though so can't help much. I know other members have though - as well as uploading attachments I believe. Maybe someone helpful will post an answer :).
In the meantime, and while I'm getting home, can you confirm the question from my previous post that I'd understood correctly the situation. Cheers.

[NeoPa]

Nothing else here for the moment.
I'm asking the question about posting attachments in the admins/mods area but no response as yet. Will pass back the info when found.
Please don't forget to respond to my request for confirmation in post #5.
Probably catch you tomorrow.

[NeoPa]

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.

[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

[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. [SumOfQuantityHeld]-[SumOfQuantityIssued] 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?

[Gunnerman6875]

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,
Question? Where do you learn all this? Do you go to collage or something? I need to learn more on acces big style!! I've just found another problem! I've just tried doing a return ie Removing the publications from someone, as though they had returned them to me, then run the query and it gives me with the headings but no info!! The Inventory table still tells me I have a set amount though! How do I learn about all this? I've got books and CD Roms, but they are'nt much help on the specifics.

[Gunnerman6875]

NeoPa,
Question? Where do you learn all this? Do you go to collage or something? I need to learn more on acces big style!! I've just found another problem! I've just tried doing a return ie Removing the publications from someone, as though they had returned them to me, then run the query and it gives me with the headings but no info!! The Inventory table still tells me I have a set amount though! How do I learn about all this? I've got books and CD Roms, but they are'nt much help on the specifics.

P.S. Do you work on Databases for a living, or do you do this for fun? I'm intrigued ?

[NeoPa]

NeoPa,
Question? Where do you learn all this? Do you go to college or something? I need to learn more on acces big style!! I've just found another problem! I've just tried doing a return ie Removing the publications from someone, as though they had returned them to me, then run the query and it gives me with the headings but no info!! The Inventory table still tells me I have a set amount though! How do I learn about all this? I've got books and CD Roms, but they are'nt much help on the specifics.
P.S. Do you work on Databases for a living, or do you do this for fun? I'm intrigued ?

I'm a little past college age I'm afraid.
I've done various work in IT since the late seventies (all my working life) and done a couple of tasks in Access in previous (work) posts. My current job has me working in Access a lot of the time, as well as in Excel and Networking and O/S and Server builds and maintenance. I seem to enjoy the Access stuff most though (with Excel).
So, yes. I do it for a living.

As far as extra learning help goes, we are trying to build up the section giving tips for starters but I basically learnt it by reading the help topics, on top of the understanding I'd built up over the years of various related topics. Not forgetting Practice; Trial-&-Error; Experience; etc.

[NeoPa]

I've just found another problem! I've just tried doing a return ie Removing the publications from someone, as though they had returned them to me, then run the query and it gives me with the headings but no info!! The Inventory table still tells me I have a set amount though!

Sorry, I forgot this bit.
An empty query dataset means there were no matching records found that fit all the criteria.
This is probably correct, but I would have to understand exactly what you did to be able to interpret the results (or lack thereof) correctly.