Queries based on same table

[jakoBAlmqvist]

Hi everyone!

I've got a table that I, with help of a query and many criterias on different fields, can filter so that I get the records I desire to have. I then done a chart out of these records. So far everything works..

but now to the problem...
The table is among other things based on years and quaters. I want to be able to compare different quaters. So what I've done is to make a new query that has the same criteria as the first query except the criteria for years and qutarers. That criteria is taken from other comboboxes. (All the criteria are taken from a form with different combos).

So finaly what I've tried to do is to do a third query based on the two first queries so that I there would be able to take the fields I want and calculate the difference between them. That doesn't work!?...

It says: "You have chosen fields from record sources which the wizard can't connect. You may have chosen fields drom a table and from a query based on that table. If so, try choosing fields from only the table or only the query"

What can I do!??!
Any help would be appreciated

Sorry for my poor english, hope you understand...

Thanks in advance

-Jake

 

[Mile-O]

The table is among other things based on years and quaters.

Can you explain this further please?

 

[jakoBAlmqvist]

What I mean is that the table has many fields. Two of them are years and quaters. Criteria for these fields filters the table. There are also criteria for 5 other fields. But these criterias should be the same in the two queries.

Do you understand what I'm trying to say?

 

[Mile-O]

I think my problem with understanding is because I would never have a table "based on years and quarters" - instead I'd treat each table as a separate object as per the rules of database normalisation.

In my opinion having fields in a table holding years and/or quarters (or any other derived part of a date) is wrong since you usually have an original date from which these details are derived and which can then be calculated in a query. If I have in a field today's date (30/06/05) then I can use the Format or DatePart functions in a query to create a calculated field to get what I want. i.e. Format([DateField], "q")

Unless you can post a sample of this problem then I can't see how I can visualise what it is your problem is. I also never use information on a form to filter itself.

 

[jakoBAlmqvist]

How do I post a sample!? I can send you the whole thing!? But how!? Thrue mail?!

 

[Mile-O]

Zip it, and use the Attachments part of a Post Reply screen. Either that or show screensheets by the same means.

 

[jakoBAlmqvist]

Thanks for all help!... I've zipped it down and attached it.

When you open it, op "Form1" under forms. From there you be able to see how the forms are. "Form 1" is a form that is used to put in values. The other forms are to look at the values that have been put in.

There are quite many forms, queries and tables that I think are just there but aren't beeing used....parts of earlier experiments=)

Once again, thanks!

-Jake

 

[Mile-O]

I know you are not going to like this but your table structure is severely lacking. There are obvious relationships which need to be defined but you have not yet defined them. For some reason, you have a number of tables and then the information is duplicated in other tables. Forms are bound to these tables; tables that don't appear to represent any specific object. There's a repeating group with this white, blue, silver, gold thing - whatever it is, it needs a new table.

Also, none of these tables have any primary keys defined meaning there could be anomalies creeping into your data.

I recommend you read up on database normalization as this is the first thing you should be considering when beginning a database. If you don't normalize properly (before queries, before forms, etc.) then you are just going to end up with a multitude of problems...like you appear to have done.

 

[jakoBAlmqvist]

Thanks

oh.. that did not sound so good... well I'm going to sit down and read thrue the things you told me to and see if I maybe can save what I've done in some kind of way, even though by what you wrote it doesn't look like something I should hope for..

This is the first thing I've ever done in Access, so every step has been a little problem to solve. Feels boring to fail on what I thought was going to be the last step to finish it all.

Well however, thanks for all help, really apriciate everything though the answers weren't the ones I wanted to hear...

I'm sure I'll be back with new questions!

-Jake