Multiple Items related to Customer Issue!

[ZKHADI]

Hello Access Community!

i am working on an Mechanical POS here i am struck on a point.
there is a table cascade Main Category and Jobs List related to each other as one to many relation.
so i made a Crosstab query here i put the column heading is Main Category name and the rows are job.
the main problem is for example i am charging a customer from a main category all jobs then i can see the all charged jobs with repeating names of customer and bill number.(See Image Serial No 1)
in Design mode (See Image Serial No 2) the different thing is SID field which allows multiple rows to show data.
but if i remove the SID field then i can set group of Customer as single record but the jobs also showing as 1 (See Image Serial 3 & 4)

So i request to kindly help me solve this biggest issue. where i can see single name of customer with multiple same category jobs.

Thanks in Advance

 

[mike60smart]

Hi
I would suggest that the best method to display the 1 Customer & Many Jobs
would be in a Report where you can Group by the Customer.

 

[ZKHADI]

Hi
I would suggest that the best method to display the 1 Customer & Many Jobs
would be in a Report where you can Group by the Customer.

i already made receipt system but this is the person requirements for who i am making this pos

 

[mike60smart]

Hi
You could probably do this with 2 Listbox's
List 1 displays Custname
List 2 displays related Jobs

 

[ZKHADI]

Hi
You could probably do this with 2 Listbox's
List 1 displays Custname
List 2 displays related Jobs

thanking you for your suggestion but the person requirements that i give him dashboard like this where he can scroll one customer and can see his jobs in main cataogries. exact format of crosstab.

 

[mike60smart]

Well you will not be able to display the data any other way using the Crosstab.

Can you upload a zipped copy of the database with no confidential data?

 

[ZKHADI]

here it is. check the crosstab query. if you fine exact format of crosstab query then try do.

 

[mike60smart]

here it is. check the crosstab query. if you fine exact format of crosstab query then try do.

Check the "frmJobList"
You just select the Customer and related Jobs are displayed

 

[ZKHADI]

Check the "frmJobList"
You just select the Customer and related Jobs are displayed

thanks i will check and let you know .

 

[ZKHADI]

i appreciate your work but if you do what i am requesting it will be more good for me. see image below you have file and you have the file. goto mainmenu form and check `is there any possibilities in cross tab or any other way?```````````````````````````````````````````````````````````````

 

[XPS35]

I think the problem starts with the query test. When you look at the design of the query, you notice that the relations around the joblist, -subcat and -cat are somewhat "confusing". There are several "roads" from joblist to sales and from jobmcat to sales.

Looking at the relationship diagram you see that those three tables are not related to the rest of your model (so referential integrity is not enforced!).

I don't have a solution because I do not know the logic of that part. I would aspect sales to be related to a product, but there is not such a table. Maybe sales should be related to a job????

 

[XPS35]

Maybe this should be (part of) your datamodel:

Notice I removed redundant fields from SalesT.