Form based on multiple queries

[TomBP]

Hi,

I want to construct a form based on multiple queries. In this case the queries are:

1. Automation Ratio - PGr B36
2. Automation Ratio - PGr B37

When creating the desired form by using a wizard, I receive an error (see attached jpg) that the chosen fields from the record sources can't be connected.

Is there a way to solve this error?

 

[vbaInet]

From the screenshot your field names indicate that you have a normalisation problem.

You will need to read up on that subject:

http://support.microsoft.com/kb/283878

 

[TomBP]

I've read up on the subject. I can conclude that a DB needs to be normalized by placing relationships between tables.

To support this conclusion I adapted the DB so it will only make use of one table. Hence there are no relationships to create.

When consulting the wizard to create a form using two seperate queries I still receive the same error message though.

 

[vbaInet]

Happy to know that you've read the link.

To support this conclusion I adapted the DB so it will only make use of one table. Hence there are no relationships to create.

This is what a relational database isn't about and what normalisation helps to iron out. What you've just done is denormalise. I would suggest you try and re-read it and perhaps these links might also help:

http://en.wikipedia.org/wiki/Database_normalization
http://www.databasedev.co.uk/data_models.html

Now to your problem, your DCount() functions are calling themselves which is wrong. If your query is called A, it is wrong to use an aggregate function in A using A as the domain of the DCount() function. Plus, you've got too many of those in your query. You can use a totals query instead which will be based on 01 Bestselling 2009 query, i.e. utilising Count() instead of DCount(). Take for example the B37 Count field:

B37Count: Count(IIF([PGr] = 'b37', 1, Null))

If you want to have the b36 results above the b37 results, look into a UNION between those two result sets.

 

[TomBP]

The problem is that my main table (REP_01_2009) is filled with a txt-file every month. This txt-file is a download from SAP. I don't see how normalisation is even possible as there is only one table.

From what I can make up out of your reply the problem of the wizard error probably lies in the incorrect use of the DCount functions and the lack of a UNION between queries.

I've tried adapting the sample database to make use of the suggested Count function but I keep running into errors.

Could you be so kind to adapt one query in the DB attached in the startpost? This would help me to understand the issue.

 

[vbaInet]

I've tried adapting the sample database to make use of the suggested Count function but I keep running into errors.

Could you be so kind to adapt one query in the DB attached in the startpost? This would help me to understand the issue.

If you had a query with a few fields I may have recreated the query for you. I just don't have the time unfortunately. Simply create a new query, open the b36 query so you have something to refer to, set a criteria to show only b36 and b37 records (I think those were the two values), right-click and click the Totals button, replace the DCounts with the Count example I showed in my last post.