Help Creating a Query for Multiple Tables + count afterwards

[eaamaya]

I am fairly new to access and I would really appreciate your help since i ran into a brick wall with my database. I am a university student and im working really hard to get this database for my work (potential permanent position).

I have several tables for different file types. Each table has a city field and dates of submission. I would like to query by city first, then count how many applications were made in the first, second, third and fourth quarter of the year for each type of file for that city.

example:
Tables of File types( each with city and date as fields): Agreement A, Agreement B and Agreement C ... each agreement table has various applications

I want to show in a table in the following format (numbers being amount of applications submitted):

"Agreement Tracking For City 1:

___________|_Jan _Feb_ Mar ....
Agreement A |_20 __3 ___5 ...
Agreement B |_15 __20 __5 ... "


Thank you very much in advance!!

[June7]

First, why do you have separate tables for different file types? If the fields are identical this should be 1 table with another field for the file type code.

Then use a CROSSTAB query.

Your example output shows months, not quarters.

[ssanfu]

I have several tables for different file types. Each table has a city field and dates of submission

This tells me that you do not have a normalized table structure. With the current structure, what happens if you get a new agreement (file) type?

You would have to:
add a new table
add new queries
modify the forms
modify the reports


From what you have posted,
One city can have many filetypes and One file type can have many cities

That is a many to many relationship.
Consider:

tblCities
CityID_PK (Autonumber)
CityName (Text)

tblFileType
FileID_PK (Autonumber)
FileType (Text) the name??

tblCityFile
CityFileID_PK (Autonumber)
CityID_FK (Number - Long) foreign key to tblCities CityID_PK
FileID_FK (Number - Long) foreign key to tblFileType FileID_PK
SubmitDate (Date/Time)


I don't know all of your requirements, but I would start with the above.

[eaamaya]

fields are not all identical, but I think I can make it work the way you said. The reason why i puit it in months as the output format, is because i can then do a simple add function to show the quarters after... my main issue is counting the applications.

Thank you for your previous comment, I will reorganize the tables to make it work.

[eaamaya]

I am working with files for 3 cities. Each city has a several file types applicable to the cities (ex. there are type A files for each of the 3 cities) and each file type has many applications. For each application, most fields are identical so Im gonna try to work it out as June7 advised... having 3 tables for each city and then having all the applications in them with a field called "application type" to make the queries work.

Knowing that, if there is a better way to organize this so the queries work better, please let me know. As I said Im fairly new to access and I dont have the experience to know what works best

[June7]

Where did I say you should have 3 tables for each city?

Again, if fields in tables are identical (or nearly so), why have multiple tables?

[eaamaya]

You said one table for each file type, so my reasoning was to have the cities separate and the field "file type". The people here are assigned to a certain city and they will input applications and so on for it. Im thinking of having a split form for each city,for them to input data and see the previous applications for that city (3 tables/ 3 forms one for each city).

My apologies for misinterpreting your previous comment, would one table with fields "city" and "application type" be better then? and then filter by city the spreadsheet for each split form... but how can i get to the application summary.. is there a best-way to do it?

[ssanfu]

We (I) don't know your project, data requirements or what you have done. Bear with us (me)....
So, I'm getting confused on terminology.
Cities I understand

What are these?
Filetypes ???
Agreements ???
Applications ???

How do they relate to each other?

Can you provide sample data?


You appear to be worried about the output before you even have the structure/relationships set up correctly. (been there, done that )
If you get the structure correct, you will save yourself hours of frustration and headaches.


You might want to go to Roger Carlson's site and go through his tutorials
http://www.rogersaccesslibrary.com/f...s_forum46.html

[eaamaya]

ssanfu:

Agreements was just an example.

Some File types are :
subdivision applications. those applications have many file numbers SD-##-####.
site plan applications. those applications have many file numbers SP-##-####.
and there are types more of course.

all these applications are for certain cities. what I am doing now thanks to all your feedback is creating order validation macros, so when the user inputs the file (SP-##-####) it automatically chooses the type of application and city. this will make everything simpler. These will all be saved in a single table and then i will worry about the output and look up the crosstab query that june7 said. If this all makes sense please let me know.. if not ill go back so the begining and start over the structure of the database.

The original question was how to make a query of the data showing the application types on the y-axis and then the counts of applications per month/quarters on the x-axis. Seems that having all data in one table will be much easier to manage now for that output.

I did some courses that my work offered of access intermediate and advanced and that didnt help much as you can see. I will go through thoe tutorials for sure.

[Dal Jeanis]

eaamaya - we'll be able to help a lot more after you've done an entity-relationship diagram like the tutorials show. Once you've done that, we can help you make your database simplere and easier to use.

[eaamaya]

eaamaya - we'll be able to help a lot more after you've done an entity-relationship diagram like the tutorials show. Once you've done that, we can help you make your database simplere and easier to use.

I followed the tutorial and I came up with this:

Applicant	Application	Status	Property
First Name	File # *	current status*	Address*
Last Name	Date Submitted		city
Company	controversial (yes/no)
Phone #	political (yes/no)
email	comments
Autonumber? *	type of App

For the applicant there isnt any unique info that can work as a primary key (as email and phone# are not available or necessary for some applications). For the diagram i pretty much made everything go 1 to many relationships with "Application".

An applicant can have many applications submitted
Application status can have many applications for each
Properties can have many applications for the address

I cant see how to connect applicant, status and property with eachother.

Im sorry that this thread became off topic, if a mod would like to move it that would be fine. If you have any more advice,questions or changes I can make to this, please let me know.

Thank you very much once again for all your help and patience!

[June7]

A field in Application to save the ApplicantID (the autonumber).

Can a property be associated with more than one Applicant or more than one Application?

Autonumber ID field in Property and a field in Application to save the PropertyID.

A field in Application for status. If status is a short, simple descriptor, I would save the descriptor and not an ID. However, if you want to save a record for each change in status (application ID, date, descriptor, who), need another table.

[eaamaya]

Can a property be associated with more than one Applicant or more than one Application?

A property can have more than one application and the applicant can have more than one application as well.

Also the statuses are: approved, withdrawn, outstanding materials, rejected, in process. But yes I would like to save a record to see when the status change was made and so on.

Thank you very much for your help June7

[ssanfu]

I cant see how to connect applicant, status and property with eachother.

Consider:
tblApplicant
ApplicantID_PK (Autonumber)
FirstName (Text)
LastName (Text)
Company (Text)
PhoneNum (Text)
email (Text)


tblStatus
StatusID_PK (Autonumber)
CurrentStatus (Text)


tblProperty
PropertyID_PK (Autonumber)
Address (Text)
City (Text)
ZipCode (Text)


tblApplication
ApplicationID_PK (Autonumber)
PropertyID_FK (Long)
ApplicantID_FK (Long)
StatusID_FK (Long)
FileNumber (Text)
DateSubmitted (Date/time)
Controversial (yes/no)
Political (yes/no)
Comments (Text or Memo)
TypeOfApp (Text)



Or you could move the status to the application table as June suggested.
Then the table structure could be:

tblApplicant
ApplicantID_PK (Autonumber)
FirstName (Text)
LastName (Text)
Company (Text)
PhoneNum (Text)
email (Text)


tblProperty
PropertyID_PK (Autonumber)
Address (Text)
City (Text)
ZipCode (Text)


tblApplication
ApplicationID_PK (Autonumber)
PropertyID_FK (Long)
ApplicantID_FK (Long)
FileNumber (Text)
DateSubmitted (Date/time)
Controversial (yes/no)
Political (yes/no)
Comments (Text or Memo)
TypeOfApp (Text)
CurrentStatus (Text)



** Notice there are no special characters or spaces used in the names.

[eaamaya]

perfect! thank you so much! with this format, how could I get the application summary to work? (back to the original question now that you helped me structure the database- thanks again!)

typeOfApp on y-axis, counts of applications per month on x-axis