Query relationship structure

[Juicejam]

I need my query output to have blank rows in certain columns/fields as I am displaying the query results using a listbox.

I know this can be done easily but for some reason I am losing my hair over it. I have attached an excel file showing the actual query output on the left and the desired output on the right. I have also included a snapshot of the query design.



Please someone help!! Thanks very much...

[orange]

Can you describe to us what exactly you are trying to achieve?

[Juicejam]

I am trying to show the query results in a listbox on a form.

E.g. for in each order, there is a menuitem, e.g a pizza but a pizza can have many different toppings. So I want the query to show the single pizza item in one column(and one row) and the many different toppings in another column (with many rows).

Please have a look at the file I attached to my original post. Thanks so much.

[orange]

Suggest you look at Listbox Rowsource

There may be ideas here:
http://office.microsoft.com/en-us/ac...010113052.aspx

You may also want to look at Cascading comboboxes, or some combination of Listbox and combobox.

[Juicejam]

Thanks but I dont really have a problem with getting the info into the listbox. My issue is getting the query to return blank fields for some columns while have information in another column but same row.

Please, please have a look at the file I attached which shows the actual query results that I get and then the kind of output I am looking for. My issue is not using the listbox but getting the data in the format I want.

Thanks again for your assistance.

[Juicejam]

Here are samples of the actual output and my desired output.

[orange]

No, I understand what your file shows.
It seems to me that you need to identify
first -- the major item( Pizza) and
secondly, a list of toppings.

The concept is to select something and once that selection is made, you have a second list from which to select relevant options.

Example:

Regions:
North
South
East
West

And if you select South, then only the Southern states are available for selection

States:
Alabama
Florida
Georgia

But if you had selected NorthRegion)
then only the northern states would be available for selection

New York
Michigan
North Dakota
Montana
Pennsylvania
etc.

Conceptually anyway, but may be that isn't what you're trying to accomplish..


OOps: You posted while I was typing..

[orange]

Your qrylfmod_name should be a separate table.
You need a junction table between

Item and qrylfMod_name
An Order can can contain an Item and 1 or more qrylfMod_name thingies

It is a table structure and relationship issue that can be solved with Normalization.

This seems to be a typical Order and LineItem type issue.

There is a series of video tutorials here that normalizes this general problem:

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

You can go back and watch these as often as you want.

[Juicejam]

Thanks I will re-read your messages but the data is already in the tables so my issue isn't getting the options captured etc. The information is already captured through a POS front end application all I am building is another application to display this information but in a sensible way.

It is actually a kitchen display screen for a restaurant I am building. I want the cook/bakers to see the pizza item once and then all the relevant toppings. But the way the query displays he may think there are five pizzas instead of 1 pizza with five toppings.

[orange]

Yes, I understand the presentation issue. It is a common problem if tables and relationships are not normalized. You have duplicate data storage and when you try to query it you get multiple records where only 1 was expected. There are other "wierd issues" as well, and they stem from the structure of the tables.

Can you post a copy of your mdb? You can "dumb it down" - remove any confidential or personal info. Just a handful of records would suffice.

I'm just interested in the structures you have.

Good luck with your project.

[Juicejam]

Ok I have read your message over again. Perhaps its me not being very clear. My tables are well normalised. What I need to do is extract the data in the format I desire. It is not a data capture or entry issue whatsoever. The data is already there.

I want one column to show only menu items and the other column to show the related modifiers. The problem is that the column showing the menu items (e.g. large pizza), repeats the item in each row where there is a corresponding modifier, e.g. pepperoni, mushrooms, etc.

All I want is the column to show pizza once, and then have blank fields beneath with the corresponding modifiers in the next column list.

Hope I am not confusing you again.

[Juicejam]

ok here is the mdb. Please also open the forms to see what i am trying to achieve.

The problem i have is that the back end database with the data is shared over a network and in addition to being slow, I keep getting an error message saying that too many databases are open. This is if I deploy the project as I have included in the mdb.

This is why I want to change my strategy and instead of using subforms to display the data which would be reloading a query each time, I would use unbound listboxes instead and simply requery these without increasing network traffic or invoking the too many databases error message.

[orange]

Please see this for conceptual data model for a restaurant. It is only an example and may not suit your purposes, but may give you some ideas.
http://www.databaseanswers.org/data_...ings/index.htm

I have looked quickly at you database. I find it quite disjointed. I looked at your relationships that don't seem to be related in any way to your Tables. You have many more tables in your database than in the relationships window.
I think you have serious structure issues, but you know your environment better than anyone.
I will try to see which query you have been using to get your "5 pizza" modifier issue and see if I can adjust it... but I'm not convinced it is a trivial adjustment.

I don't know what your form is doing -- it is calculating something ????

[Juicejam]

Please remember the database I sent you is the backend for an application so the data relationships etc may be adjusted by code. It's a shelf application so I do not know what is done with the data structures.

It's best if you look at the queries indeed as those are what I have put together to pull the data from the tables. The forms calculate time to fulfill order and requery the data in the forms to get the most current records.

[orange]

Does the following mean that this is a purchased application

It's a shelf application so I do not know what is done with the data structures.

and is this meant to indicate you are adding a piece to the current system

The information is already captured through a POS front end application all I am building is another application to display this information but in a sensible way.

Perhaps someone else can look at this and give you some guidance.