Need to run a query/report and I'm stumped!!!

[qbc]

Hello everyone,
I have spent hours and I'm stumped. I need to run a report and can't seem to get the results I need. It could be the way my tables are setup but I can't change them now.

There are two tables in my database linked by the client code.

Table 1
Client Code
Client Name
etc...



Table 2
Location 1
Zone 1
Amount 1
Location 2
Zone 2
Amount 2
Location 3
Zone 3
Amount 3

Basically, I need to run a report for a particular Zone and if that value appears in any of the Zone fields, I need the report to return the client name and add up all the amounts for those locations that hold that Zone value.

Any thoughts? Any help would be much appreciated.

Thanks everyone.

[aytee111]

If you can't change your tables then you have to make a temporary table that will give you what you want. The table to contain client, zone, location, amount.

Query 1 - delete all entries from this table
query 2 - append client, zone1 (if not null), location1, amount1
query 3 - same for zone2
query 3 - same for zone 3

Now you have a table with one record per zone. Your know the zone so now you have the client, location and amounts.

[orange]

As aytee111 has said, your tables are not normalized which is the root of your problem.

[qbc]

What do you mean by normalized? I am relatively new and need to be almost walked through.

[aytee111]

The most important thing you must learn about using Access is database design, i.e. the table structure. With a solid foundation the rest comes easy, but with a bad structure, such as the one you have, everything will be made difficult for the life of the database and for everyone who has to maintain it and work in it.

Learn everything you can about database design - whether Access is your tool or SQL Server or Oracle or whatever - there are ways to design and ways not to design. Normalization is just one aspect. For example, your table 2 above would not have fields going "horizontally" across as if it was an Excel spreadsheet, but would look more like the temporary table that I described.

[qbc]

Thanks so much for the advice. Yes, I gathered that my database design was the problem.
Will you be able to give me more concise instructions in order to employ your solution for the above problem?

Thanks

[aytee111]

Take it step by step - do as much as you can:

1 - create a table with the 4 fields I suggested above (table design), define the fields the same data type and format and length as is it defined on table 2 (you can copy and paste too)
2 - create the delete query - use the wizard if you don't know how. The SQL will be "DELETE * FROM tablename;"
3 - create an append query, add the data for the fields marked "1" - put in your criteria under Zone "is not null" - this will only add records to the table if there is a value in the zone1 field
4 - do the exact same thing but change the "1" to "2"
5 - same again, this time for "3"

At the end you should have a table that contains all the zones with their locations and amounts and client. You can now more easily search using only a zone, etc.

[qbc]

ok, before i start, how does the data from my current main table get fed into the new table with 4 fields?

also, someone told me there might be a way of accomplishing this by creating if statements in the query. What do you think?

[aytee111]

Yes, you can use IIf statements - there are always many ways to do things. It is your choice, what you feel most comfortable with. If zone1 is not null and zone1 = selected zone or zone3 is not null and is equal to selected, etc. For me that is too complicated, hard to get right with all the correct bracketting, etc, but feel free to learn and experiment.

The queries that I mentioned are append queries - that is how your data gets into the new table.