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